Business Central Data in Power BI — Beyond the Default Model

When you first connect Business Central to Power BI, it feels great. A couple of clicks, and you’ve got data, dashboards, and some nice-looking sample reports.

However, if you’ve got more than one company in Business Central (which most people do), the default setup doesn’t really help you. The out-of-the-box model only looks at one company at a time. That’s fine if you just want to see how a single entity is doing, but the moment you need a group view, finance roll-ups, sales comparisons, even something simple like head-to-head KPIs — it falls apart.

Why this is a problem

Multi-company reporting isn’t some edge case. Most businesses run multiple legal entities, whether because of geography, tax, or just how they’re structured. And yet, if you try to use Microsoft’s supplied dataset, you’ll quickly find yourself:

• Copying the same dataset for each company

• Manually stitching together reports

• Wasting hours maintaining something that should be simple

It gets messy fast.

What you can do instead

The good news is you have options:

1. Use the APIs

Business Central’s APIs let you specify the company as part of the call. That means you can pull data from multiple companies, add a “Company” column in Power Query, and report on them side by side.

Example API call (sales invoices from a company):

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

All you change is the company name or ID in the URL.

2. Stage your data with Dataflows

Instead of pulling straight into a dataset, you can set up a Power BI Dataflow per company, standardise the structure, and then just append them. One clean source of truth, no duplicate models.

Quick steps for dataflows:

1. Create a dataflow per company in Power BI Service.

2. Connect each one to the right BC company.

3. Standardise table names and columns so they match.

4. Append them in a final dataflow.

5. Point your dataset at this single unified table.

3. Consolidate inside BC

If you only care about high-level rollups, Business Central’s consolidation functionality might be enough. Push that into Power BI, and you’ll save yourself some grief (though you lose detail).

A pattern that works well

One approach I’ve seen a lot is:

1. Query invoices (or other key tables) from each company through the API.

2. Append them together in Power Query.

3. Add a column for “Company Name”.

4. Build your reports once, not three times.

Now you can switch between companies, or see everything consolidated, without breaking your model.

Step 1: Create a function

In Power Query, create a blank query and paste this in:

// fxGetCompanySalesInvoices
(companyName as text) =>
let
    Source = OData.Feed(
        "https://api.businesscentral.dynamics.com/v2.0/{tenant}/Production/api/v2.0/companies('" & companyName & "')/salesInvoices"
    ),
    AddCompany = Table.AddColumn(Source, "Company", each companyName)
in
    AddCompany

This function takes a company name and returns that company’s sales invoices with the “Company” column already tagged.

Step 2: Call the function for each company

Now you can call it as many times as you need, without copy-pasting the full OData query:

let
    	CompanyA = fxGetCompanySalesInvoices("CompanyA"),
	CompanyB = fxGetCompanySalesInvoices("CompanyB"),
	CompanyC = fxGetCompanySalesInvoices("CompanyC"),

	Combined = Table.Combine({CompanyA, CompanyB, CompanyC})
in
	Combined 

Why this is better

• Less duplication → only one place to maintain the API call logic.

• More readable → each company is just one line.

• Easier to scale → you can even store the company list in a table and iterate through it if you want to go full dynamic.

Watch out for

• Refresh times blowing out.

• Security, sometimes you don’t want people seeing all companies.

• Who owns consolidation logic (is it BC, or Power BI transformations?).

Multi-company is usually the first place people hit the limits of the Microsoft-supplied model. If you only run a single company, the out-of-the-box stuff is fine. But if you need a bigger picture, you’ll want to go a step further: APIs, dataflows, or even BC consolidation.

Think of it as the moment you move from the “starter kit” to building something that specifically fits your business.

Link to PBI sample from Microsoft: Here