Power BI for Business Central: Multiple Companies

A group running several legal entities in Business Central eventually asks for one Power BI view across all of them: group sales, group receivables, a P&L that adds up.

The free Power BI apps do not do this, and Microsoft says so. This post covers what the apps do with multiple companies, why the obvious workaround fails, and the routes Microsoft documents for a combined view. Every Microsoft claim links to its Microsoft Learn source.

What the free apps do with multiple companies

Asked whether the same app can report across multiple companies or environments, Microsoft's FAQ answers:

"No. The Power BI apps currently work per company in Business Central. If you have multiple companies in your Business Central environment, you must install and set up a Power BI template app for each company. Each app is then available in its own Power BI workspace." (Power BI apps FAQ)

To install an app again, the FAQ says to choose "Install another copy of the app into a new workspace", and suggests putting the company name in the workspace name. Each semantic model points at its company through Environment and Company parameters, and the company parameter is case sensitive (Install Power BI apps). Four companies means four installs of each app, in four workspaces, and nothing that adds them together.

Why the obvious workaround fails

The next idea is usually a report on the OData feed that pulls in every company. Microsoft closes that door too:

"ODataV4 doesn't export data from multiple companies. The $expand function in Power BI that you might think would be an alternative way to create a multi-company report can't be used either. It creates a column with the company name but doesn't populate it with the company data after a refresh." (Building reports in Power BI Desktop to display Business Central data)

That last part is the trap: the column appears, so the report looks as if it worked.

Route 1: consolidate in Business Central, then report

Business Central has its own consolidation feature. Each company involved is a business unit, and general ledger entries are transferred from the business units into a separate consolidated company (Consolidate data from multiple companies). Microsoft lists what it can handle, including:

• companies with different charts of accounts
• different fiscal years and different currencies
• the full amount or a percentage of a company's financial information
• companies in different Business Central environments

You then get a Consolidated Trial Balance report, and a G/L Consolidation Eliminations report to preview eliminations before posting. Microsoft notes that "processing consolidation eliminations is a manual process."

When it fits: group financial statements. It keeps eliminations and currency translation where the finance team already manages them.

What it will not give you: anything below the general ledger. Microsoft describes the consolidated company as "just a container for the consolidated data" that "doesn't have any live business data", and what it receives is general ledger entries. A report built on it can show a group P&L; it cannot show group sales by customer, group inventory or group receivables by invoice.

Route 2: build your own model on the API

For operational reporting, Microsoft documents a way to load several companies into one model. API endpoints share a prefix containing the environment name and then the company ID, and Microsoft says you can use that structure "to create PowerQuery queries that load data for multiple companies or multiple environments if the user who is reading data can access them" (Building reports in Power BI Desktop to display Business Central data).

The documented steps are short:

1. Take the Power Query query that loads data for one company and turn it into a custom function that takes the company ID (or the environment name) as a parameter.
2. Map that function over a list of companies, then merge the results with Table.Combine.

That gets the rows into one table. It does not make them comparable. What decides whether a group report is right sits in the model, and none of it is in Microsoft's steps:

• A company key on every fact table. Every row needs to say which company it came from, and every relationship has to respect it. The same customer number in two companies may be two different customers.
• A mapping to one chart of accounts. Business Central consolidation allows different charts of accounts per company; a combined report needs them mapped.
• One currency for group totals. Companies posting in different currencies need an agreed translation rate and date. Adding raw amounts gives a number that looks like a total and is not one.
• Intercompany activity. Sales between group companies inflate group revenue unless the model knows which customers and vendors are group companies.
• Permissions. Microsoft's condition is that the reading user can access every company in the list. Plan the refresh identity with that in mind.

When it fits: group operational reporting, where a finance-only consolidated company is not enough.

Route 3: separate installs, side by side

Sometimes nobody needs a single total. If each company has its own manager and the group only wants to see them next to each other, per-company installs with clearly named workspaces may be enough. It is the cheapest route and needs no custom model. It stops being enough the first time someone adds two workspaces together by hand.

A trap in the documentation

One Learn page says data from additional companies "can be added to the reports by adding new queries that use different companies as the data source" (Use the Business Central apps in Power BI). It describes a different set of apps (Business Central CRM, Finance and Sales) built on published web services, whose datasets are "filtered to only return the previous 365 days of data." It is not about the nine apps above.

Choosing a route

The first two can run side by side: consolidation for the statutory numbers, a custom model for the operational view, and a reconciliation so the two never disagree. The other limits Microsoft documents are quoted in the Power BI for Business Central guide.

For Microsoft partners

If a client needs a multi-company model and you do not keep a reporting bench, Palavir builds that layer for Microsoft partners: the model, the measures and the pages, in the client's tenant, delivered under your brand.