Business Central + Power BI: When the Standard Connector Isn’t Enough

Most people start analysing in PowerBI Business Central using the supplied connector. It’s quick, it’s easy, and for simple reporting it does the job.

But sooner or later, you’ll run into limits:

• You can’t get at the data you actually need.

• Refreshes take forever.

• You need to combine BC data with something else (CRM, payroll, spreadsheets).

That’s when it’s time to look beyond the standard connector and think about hybrid approaches.

Option 1: Stick with OData (but use it wisely)

The OData endpoints are built into Business Central and are dead simple to connect to from Power BI. You point Power Query at the OData feed, and the data just flows in.

The upside:

• Easy to configure, no extra setup.

• Good for lightweight tables like Customers, Vendors, Items, Dimensions.

• Works fine for reports that don’t need deep history or millions of rows.

The downside:

• Performance drops off quickly with large transactional tables (think G/L Entries, Sales Invoices, or Item Ledger Entries).

• Query folding isn’t always reliable — filters in Power BI don’t always push back to BC.

Example OData query in Power Query M

let
    Source = OData.Feed("https://api.businesscentral.dynamics.com/v2.0/{tenant}/Production/ODataV4/Company('CRONUS USA, Inc.')/Customers")
in
Source

Option 2: Business Central APIs

The Business Central APIs are more structured and consistent than OData. They expose data in a cleaner way, and sometimes surface fields that the OData feeds don’t.

The upside:

• Better performance on transactional tables.

• Easier to do multi-company reporting, just call the API for each company and append results.

• More control over which fields you pull (with $select, $filter, etc.).

The downside:

• A bit more technical than OData. You’ll need to be comfortable writing API calls or wrapping them in Power Query functions.

Example API call (sales invoices)

GET https://api.businesscentral.dynamics.com/v2.0/{tenant}/Production/api/v2.0/companies('CRONUS USA, Inc.')/salesInvoices

Calling it from Power Query (M)

let
    Source = OData.Feed(
        "https://api.businesscentral.dynamics.com/v2.0/{tenant}/Production/api/v2.0/companies('CRONUS USA, Inc.')/salesInvoices?$select=id,invoiceNumber,postingDate,amount"
    )
in
    Source

Best practice with APIs

• Use $select to only fetch the fields you need.

• Use $filter to limit rows at the source (e.g. ?$filter=postingDate ge 2024-01-01).

• Wrap the query in a function so you can call it for multiple companies and tag each one.

Wrap up

The Microsoft-supplied connector is fine for a quick start, but it won’t carry you far.

• OData is simple, and works well for small or reference tables.

• APIs are more structured, perform better on transactional tables, and support multi-company reporting.

If you’re serious about reporting in Power BI with Business Central, you’ll end up using both. The trick is knowing when to use OData and when to switch to the APIs.

If your business is looking at reporting in Power BI from Business Central - our team is more than happy to help!