Power BI with Business Central retail data: setup and what a store dashboard can show
Power BI does not read Business Central by magic. It reads what Business Central has published — as an API or as a web service — through a built-in connector in Power BI Desktop. Get that publishing decision right first, and the connection itself is a short, documented procedure. Get it wrong, and the dashboard will be built on the legacy route Microsoft already discourages.
Data-accuracy note: a Power BI report is only as current and complete as the published data behind it and its last refresh. Check the source and the refresh before acting on any number in it.
Before connecting: make the retail data available first
Microsoft Learn puts the preparation before the connection: sign up for the Power BI service, download Power BI Desktop — described as a free application installed on the local computer — and make sure the data wanted in the report is available as an API, which is the recommended route, or published as a web service. That last step is the one stores skip. If the item, customer, or ledger data needed for a view has not been exposed that way, it will not appear as a usable data source after connecting, no matter how the report is designed.
An optional extra at this stage is the Business Central report theme. The theme file can be downloaded and later imported in Power BI Desktop via View > Themes > Browse for themes, so reports use the same colour styling as the Business Central apps without defining custom colours for each visual. It changes presentation only — it does not change what data is available.
Choose the data route: APIs first, legacy web services last
After connecting, the Navigator groups what Business Central has published into folders. That folder list is the clearest documented guide to which route a report is actually built on:
| Navigator folder | What it contains | Use it for |
|---|---|---|
| Standard APIs v2.0 | Data APIs used to read master data such as customer, vendor and item, transactions such as general ledger entries and customer entries, and setup data such as dimensions, payment methods and unit of measure | The default starting point for store-operations reporting |
| Advanced APIs | Advanced APIs published by Microsoft, and Custom APIs published by other organizations, grouped by the APIPublisher and APIGroup properties | When the needed data is exposed through an advanced or custom API rather than the standard set |
| Web services (legacy) | Pages, codeunits and queries published as web services in Business Central | Only when the data exists no other way — Microsoft describes this legacy method as discouraged and suggests using APIs instead |
The decision framework is therefore simple. First, look in Standard APIs v2.0: most retail reporting needs — items, customers, vendors, ledger and customer entries, dimensions — are described in those terms. Second, check Advanced APIs if a custom or advanced API already exposes the exact data. Third, fall back to a published web service only for data that is available no other way, and record that choice as legacy debt rather than treating it as the normal pattern.
Connect Power BI Desktop to Business Central, step by step
- Start Power BI Desktop and select Get Data. If Get Data is not visible, select the File menu, then Get Data.
- Select Online Services. On the Get Data page, choose Online Services, then in that pane choose the connector for the deployment.
- For Business Central online, select Dynamics 365 Business Central, then Connect. Sign in when prompted, using the same account used to sign into Business Central. Microsoft describes sign-in here as one-time only — after connecting, a prompt is not shown again.
- For Business Central on-premises, select Dynamics 365 Business Central (on-premises), then Connect. Enter the OData URL for Business Central, then the user name and password for the connecting account, entering the web service access key in the Password box. Note that the on-premises structure is different because it does not support API pages.
- Use the Navigator to choose data. Power BI contacts the Business Central service and the Navigator displays the available data sources, grouped by environments and companies. Expand a folder, select the data source or sources to add to the data model, then select Load.
- Build, then check the loaded data. Once data is loaded it appears in the navigation on the page, and report building can begin. Adding more Business Central data later means repeating the same Get Data steps.
What success looks like: the Navigator shows the expected environment and company, the chosen API folders expand, and after Load the selected tables appear in the report view with rows present. An empty Navigator, or a folder missing the needed data, points back to the publishing step — not to report design.
For larger datasets, Microsoft's FAQ points to filtering before the data is pulled in by editing the Power Query formula directly: most filters set that way are passed to Business Central through query folding. There is currently no way to set a filter for the web service data from within Business Central itself without a custom app, so the filter belongs in the query, not in a hope that the source page was filtered first.
What a store-operations dashboard can honestly show
Built only from the documented data types above, a store-operations view can honestly cover three layers. The first is master data: items, customers and vendors as published through the standard APIs — the lists a manager checks for completeness and consistency. The second is transactions: general ledger entries and customer entries are the documented examples, which support totals and movement over time rather than invented measures. The third is setup data used as the slicing layer: dimensions, payment methods and units of measure are documented setup data, so a view can be broken down by the dimensions the business already maintains instead of by labels typed into the report.
What it cannot honestly do is just as specific. It cannot show data that was never published as an API or web service. It cannot promise live data without a refresh story — publishing and sharing choices affect how refresh happens, as the next section covers. And it should not present a single-company feed as a chain-wide total: starting with Business Central 2021 release wave 2, only ODataV4 is supported, and ODataV4 does not export data from multiple companies. Microsoft also notes that the $expand function does not work around this — it creates a column with the company name but does not populate it with company data after a refresh.
Multi-company reporting is still documented, but it is a Power Query build, not a connector tick-box: take the query that loads data for a single company, convert it to a custom Power Query function that takes the company ID or environment name as parameters, map that function over a list of companies, and merge the datasets using Table.Combine. The API endpoints behind this use the documented prefix pattern with the environment name in the address. Treat that as a deliberate second phase, with its own testing, not as something the first dashboard quietly includes.
Publish the report and view it back in Business Central
After building or modifying a report, publish it from Power BI Desktop by selecting Publish on the Home tab of the ribbon or from the File menu. If signed into the Power BI service, the report publishes there; otherwise a sign-in prompt appears. After publishing, the report is available in Business Central and becomes available for selection there.
Inside Business Central, several key list pages include a Power BI FactBox. On a list page the report is filtered for the selected entry and updates as the user moves between rows. The list pages documented with that filtered part include the Customer List, Vendor List, Item List, Sales Order List and Purchase Invoices — for a store, the Item List case is the practical one: select an item in the list and the report part follows that selection. If the FactBox is not visible, it may be hidden by personalization, or on older versions it is shown via Actions > Display > Show/Hide Power BI reports. To switch reports, choose Select Report in the part, then on the Power BI Reports Selection page select the Enable box for each report to display and choose OK. That selection page lists reports from the user's own workspaces or workspaces shared with them in the Power BI service. Reports can also be created specifically to display in a FactBox on a list page and change when different rows are selected.
Sharing needs a deliberate choice too. Microsoft documents that sharing a report directly from the Power BI service requires a Power BI PRO license or Power BI Premium Capacity. The alternative — distributing reports as .pbix files for each person to upload — means refreshing is done individually by each user, which might impact Business Central performance. For a store dashboard several people rely on, the service route is the documented sharing path; the file route trades that for per-user refresh load.
Limits and checks before trusting the numbers
- Page size and timeout are bounded. The connector supports advanced parameters including ODataMaxPageSize, with a default of 5000 and a Business Central maximum of 20000, and a Timeout with a default of 00:08:00 that cannot exceed the service maximum of 10 minutes. A report that times out or truncates is a parameter and dataset-size problem to fix explicitly, not a reason to quietly accept partial totals.
- Reads can use the replica. The UseReadOnlyReplica parameter defaults to true, determining whether requests target the primary database or a read-only replica; Microsoft describes offloading reads this way as improving performance and stability, especially at peak times.
- Check the company scope on every visual. Given the ODataV4 multi-company limit, a total that claims to cover several companies must come from the documented Power Query combine approach — otherwise assume it covers the one company it was loaded from.
- Check the source folder. A report built on the Web services (legacy) folder carries a documented discouraged route. Note it, and plan the move to an API source when one becomes available. For field guidance on related reporting data, see Microsoft Learn: Building reports in Power BI Desktop to display Business Central data.
FAQ
Is Power BI Desktop a paid product? Microsoft describes Power BI Desktop as a free application installed on the local computer; a separate sign-up for the Power BI service is part of the documented preparation.
Why is the needed table missing from the Navigator? The documented preparation step is to make sure the data is available as an API or published as a web service first. A missing source points to that publishing step.
Can one report cover multiple companies? Not from a single ODataV4 feed — the documented route is a Power Query custom function mapped over a list of companies and merged with Table.Combine.
IndexBack to all guides →