# Analyse your BCL sales in Power BI

> Export your paid transactions from BCL and build a Power BI report of sales by form, payment channel and day, or pull them from the BCL API.
>
> Source: https://docs.bcl.my/power-bi/

BCL has no built-in Power BI connector, but your sales data moves across easily. Export your paid transactions from BCL, tidy the file in Excel, and build a report in Power BI Desktop (free) that shows revenue by form, by payment channel and by day. If you are comfortable with APIs, Power BI can also read your transactions straight from the BCL API.

## Export your paid transactions

Start with an Excel file of your successful payments:

1. In the sidebar, click **Transactions**.
2. Click the **Successful** tab, then click **Export All**.

   *(Screenshot: The Transactions page with Transactions in the sidebar, the Successful tab and the Export All button highlighted)*

3. Keep the default columns and click **Submit**. The Excel file downloads.

   *(Screenshot: The Export All panel with the Submit button highlighted)*

For all the export options, see [Export your transactions](/export-transactions/).

## Keep one row per order

The export has one row for each item in an order, and every row repeats the order's **Total Amount (RM)**. Remove the extra rows first, or Power BI counts those orders more than once:

1. Open the file in Excel, or in Excel for the web after you upload it to OneDrive.
2. Click **Data** → **Remove Duplicates**.
3. Untick **Select all columns**, tick only **Order Number**, keep **My data has headers** ticked and click **OK**.

   *(Screenshot: The Remove Duplicates dialog with only Order Number ticked and the OK button highlighted)*

4. Save the file.

Excel tells you how many duplicate rows it removed. The rows left are one per paid order.

## Build the report in Power BI Desktop

Power BI Desktop is a free Windows app from Microsoft. Build the report there, then publish it to the Power BI service:

1. Open Power BI Desktop and click **Excel workbook**.

   *(Screenshot: The Power BI Desktop start page with the Excel workbook tile highlighted)*

2. Choose your saved export. In the **Navigator**, tick **Worksheet** and click **Load**.
3. Add these three visuals from the **Visualizations** pane, ticking the fields in the **Data** pane:

   | Visual | Fields | What it shows |
   | --- | --- | --- |
   | Clustered bar chart | **Form Name**, **Total Amount (RM)** | Revenue by form |
   | Donut chart | **Payment Channel**, **Total Amount (RM)** | Revenue by payment channel, such as FPX or DuitNow QR |
   | Clustered column chart | **Transaction Date** (by day), **Total Amount (RM)** | Revenue by day |

4. Save the report, then click **Publish** and choose **My workspace**. Open the report at [app.powerbi.com](https://app.powerbi.com) to view it in your browser or share it inside your organisation.

To update the report, export again, replace the file, and click **Refresh** in Power BI Desktop before you publish again.

## Advanced: read your sales from the BCL API

Power BI Desktop can call the BCL API instead of reading an export. You need a BCL API token with the **API Read** permission, from **Platform Setup** → **Integrations** → **API Token**. See [API tokens](/api-tokens/).

> **Caution**
> The API lists live transactions only. Test Mode payments do not appear in `/transactions`.

1. In Power BI Desktop, click **Get data from other sources** on the start page (or **Get data** on the **Home** tab), search for **Web**, select it and click **Connect**.

   *(Screenshot: The Get Data dialog with the Web connector and the Connect button highlighted)*

2. Choose **Advanced**. Enter `https://api.bcl.my/v1/transactions` and `?per_page=100` as **URL parts**. Under **HTTP request header parameters**, add **Authorization** with `Bearer ` followed by your token, click **Add header**, and add **User-Agent** with a name for your app, for example `KopiKampung-PowerBI/1.0`. BCL refuses requests without a proper User-Agent. Click **OK**.

   *(Screenshot: The From Web dialog in Advanced mode with the URL parts and the Authorization and User-Agent headers highlighted)*

3. If Power BI asks how to connect, choose **Anonymous** and click **Connect**. Your token already travels in the header. Power Query opens and turns the JSON into a table.

That first query reads one page of up to 100 transactions. To read every page and keep only the columns you need, click **Advanced Editor** in Power Query and replace the query with this one. Put your token in place of `YOUR_API_TOKEN`:

```text
let
    Token = "YOUR_API_TOKEN",
    GetPage = (page as number) =>
        Json.Document(Web.Contents("https://api.bcl.my/v1", [
            RelativePath = "transactions",
            Query = [per_page = "100", page = Number.ToText(page)],
            Headers = [
                Authorization = "Bearer " & Token,
                #"User-Agent" = "KopiKampung-PowerBI/1.0",
                Accept = "application/json"
            ]
        ])),
    FirstPage = GetPage(1),
    AllRows = List.Combine(
        List.Transform({1..FirstPage[meta][last_page]}, each GetPage(_)[data])
    ),
    Orders = Table.FromRecords(AllRows,
        {"order_number", "created_at", "payer_name", "amount", "status",
         "payment_channel", "form_type", "remarks"}, MissingField.UseNull),
    Typed = Table.TransformColumnTypes(Orders,
        {{"created_at", type datetime}, {"amount", type number}})
in
    Typed
```

*(Screenshot: The Power Query Advanced Editor with the paging query)*

Click **Done**, name the query (for example **BCL Orders**) under **Query Settings**, and click **Close & Apply**. The API returns one row per transaction, so there are no item rows to remove. It includes every status, so filter the **status** column to successful payments before you add up revenue.

*(Screenshot: Power Query with the BCL Orders query, one row per transaction, and the Name field highlighted)*

## Tips

A few habits keep your report accurate and safe:

- **Export the Successful tab only.** Pending, failed and cancelled payments in the export would inflate your revenue.
- **Use the same file name each time.** Replace the old export with the new one under the same name, and a **Refresh** in Power BI Desktop picks up the new data without rebuilding the visuals.
- **Keep your API token out of shared files.** The token sits inside the report's query. Do not send the .pbix file to people who should not read your BCL data, and give the token only the **API Read** permission.
- **Want new orders in Excel as they happen?** See [Send orders to Excel with Power Automate](/excel-power-automate/).

## Common issues

### Why is my revenue in Power BI higher than in BCL?

The BCL export has one row per item, and each row repeats the order's Total Amount (RM). Remove duplicate rows on Order Number before you add up the totals.

### Can Power BI read my BCL data directly?

Yes, with Power BI Desktop and the BCL API. Use Get data → Web, send your API token in an Authorization header and set your own User-Agent. The API lists live transactions only.

### I imported the Excel file into the Power BI service and only see the spreadsheet. Why?

The Power BI service opens an imported Excel file as a workbook to view, not as data for a report. Build the report in Power BI Desktop, then publish it to your workspace.