Power BI Report Business Central: Why Your Team Keeps Rebuilding the Same Report in Excel

Key Takeaways:

• A Power BI Report Business Central setup can come from two places: the built-in embedded reports Microsoft ships with the product, or a custom report you build yourself in Power BI Desktop.
• The out-of-the-box Power BI Report Business Central apps cover finance, sales, purchasing, inventory, and manufacturing, but the data model behind them can't be changed.
• Canadian companies need to confirm where their Power BI tenant stores data before connecting a Power BI Report Business Central setup, especially under Québec's Law 25 or federal PIPEDA rules.
• The reports that save the most time are rarely the flashiest ones. Cash flow, sales performance, and inventory valuation come up again and again.

Every finance team we talk to in Canada has a version of the same Friday afternoon ritual: export data out of Business Central, paste it into Excel, rebuild the same pivot table built last month, and email it around before anyone notices it's out of date. A proper Power BI Report Business Central setup is supposed to end that ritual. For a lot of companies, it doesn't, not because Power BI is weak, but because nobody set the connection up to match how the business really reports.

This article walks through how a Power BI Report Business Central connection works, which built-in reports hold up and which ones you'll want to replace, and what a Canadian company needs to think about before wiring Power BI to live ERP data. We'll also point out where the standard setup runs into limits, since that's usually the part vendors leave out.

Grab a coffee. We're covering the full picture here, because the shallow version of this topic is exactly why so many Power BI rollouts stall right after the demo.

What a Power BI Report Business Central Setup Involves

A Power BI Report Business Central setup pulls data out of your environment and turns it into something you can filter, drill into, and share, without manually exporting a CSV first. Microsoft Power BI and Business Central are built by the same company, so the connection is native. You're not stitching together a third-party integration; you're using a pipe Microsoft already built.

There are two distinct ways this shows up inside Business Central, and mixing them up is where a lot of confusion starts.

The first is the embedded report. Business Central includes a Power BI FactBox on many pages, the Role Center included, where a report you've published in the Power BI service shows up directly inside the ERP screen. You never leave Business Central to see it.

The second is a fully custom build, built in Power BI Desktop, connected to OData or API endpoints, and published to your organization's own Power BI workspace. This is where you get real flexibility: your own visuals, your own filters, your own combination of tables with other data sources like Excel, SQL Server, or a CRM dashboard built from your Dynamics 365 data.

The Built-In Power BI Report Business Central Apps: What's Really There

Starting with Business Central v25.2, Microsoft ships seven ready-made Power BI apps from AppSource: 

1. Finance
2. Sales
3. Purchasing
4. Inventory
5. Inventory Valuation
6. Projects,
7. Manufacturing. 

Each app is a small collection of reports bundled together, so installing the Finance app gives you several pages of pre-built visuals, not just one dashboard.

Setup follows the same pattern every time. You search for Extension Management inside Business Central, open the AppSource Gallery, install the app you want, connect it to your environment and company, and let it pull in the data model behind the scenes. The first time, Business Central loads demo data so you can see what it looks like before pointing it at your own numbers.

Here's what most vendor articles on this topic leave out: once installed from AppSource, you can resize visuals, add slicers, and remove elements you don't need, but you cannot touch the underlying data model of a built-in Power BI Report Business Central app. 

If your chart of accounts, dimensions, or item categories don't map cleanly onto how Microsoft structured the app, you're stuck working around it rather than fixing it. For a Canadian distributor running multiple companies with different tax setups, or a manufacturer with custom dimension structures, that limitation shows up fast.

Where the Out-of-the-Box Power BI Report Business Central Setup Falls Short

The built-in apps are a solid fit for a company that wants standard financial and operational visuals without paying for custom development. But a few limitations come up often enough with our clients that we'd rather name them here than let you discover them mid-project.

The first is data model rigidity. You can't add a custom field into the built-in report without rebuilding it yourself in Power BI Desktop, which defeats the purpose of using the ready-made app in the first place.

The second is multi-company reporting. A single Power BI Report Business Central connection built from the AppSource apps connects to one company at a time. Running separate legal entities for Canadian and U.S. operations, or separate companies per province, means OData V4 connections and manual work for financial consolidation across multiple entities, since Business Central 2021 release wave 2 dropped support for pulling multiple companies through a single legacy connection.

The third, and the one nobody in the U.S.-based vendor content mentions, is data residency. If your Power BI tenant isn't anchored to a Canadian Azure region, you may be storing financial and customer data outside the country without realizing it. For companies subject to Québec's Law 25 or federal PIPEDA obligations, that's not a small detail. Confirming your tenant sits in Canada Central or Canada East before connecting a live report to production data is a five-minute check that avoids a longer conversation with compliance later.

Building a Custom Power BI Report Business Central Setup

When the built-in apps don't fit, the next step is a custom Power BI Report Business Central build in Power BI Desktop. The process looks like this in practice:

You open Power BI Desktop and select Get Data, then choose Business Central as your data source. Sign in with your work credentials, pick the environment and company, and select which list pages or APIs you need. Sales order data, Item Ledger Entries, G/L Entries, and Projects in Business Central records are common starting points. Teams focused on AP often pull data that feeds automated invoice management, since a report on top catches processing bottlenecks a static list never would.

From there, you build your visuals, set up relationships between tables if combining more than one data source, and add a filter tied to a primary key if the report should update based on which record is selected. Once built, you publish it to your Power BI workspace, and if you want it inside a Business Central FactBox, set its size to 325 by 310 pixels so it scales properly.

This is also where a Power BI Report Business Central setup pays off in time savings. A finance team that used to spend two days at month-end pulling numbers into a spreadsheet can get the same view refreshed automatically, on a schedule, without touching Excel at all.

Getting Your Data for Power BI Report Business Central Ready First

Before anyone opens Power BI Desktop, it helps to spend a day looking at the Business Central data itself. A Power BI Report Business Central setup is only as clean as the tables feeding it, and a few things trip up new projects.

Dimensions are the biggest one. If your finance team hasn't been consistent about tagging transactions with department, location, or project dimensions, no amount of clever Power BI modeling will let you slice the report by those fields later. It's cheaper to fix dimension discipline in Business Central now than to patch around missing data in Power Query afterward.

Naming conventions count almost as much. Item categories, customer groups, and vendor classifications set up quickly during the original implementation tend to have gaps or duplicates that only surface once you try to group them in a report. A short data audit before the Power BI work starts usually saves more time than it costs.

Finally, decide who owns refresh scheduling early. Someone on your team, or your implementation partner, needs to own the gateway connection and refresh cadence for any report pulling live Business Central data. Without an owner, refresh schedules quietly break and nobody notices until the numbers stop matching. Getting the team comfortable with the data model in the first place, through proper Business Central Training, tends to prevent most of this before it starts.

The Three Power BI Report Business Central Types Canadian Teams Keep Coming Back To

Past the demo apps, a handful of Power BI Report Business Central types come up over and over with our clients across Québec, Ontario, and the rest of Canada.

# 1. Cash Flow Forecasting is the Most Requested. 

Business Central's native cash flow forecast tool already blends historical data, open transactions, and budget figures, and pairing it with a Power BI report on top gives finance leaders a rolling view of liquidity instead of a static snapshot. We've written a longer breakdown of how the cash flow forecast in Business Central works if you want the full setup.

#2. Sales Performance is a Close Second. 

A well-built sales performance dashboard in Power BI pulls order history, pipeline data, and rep-level activity into one view that a sales manager can filter by territory or product line without asking IT for a new export every week. Companies running Dynamics 365 alongside Business Central often extend the same idea into a full Dynamics 365 sales dashboard, so the VP reads pipeline and ERP numbers on one screen instead of two systems and a spreadsheet stitched between them. A simpler sales dashboard view works fine too if your team just needs the basics without the full CRM layer.

#3. Inventory Valuation and Turnover Rank Third. 

Especially for distributors and manufacturers managing multiple warehouses. A report here catches slow-moving stock and cost variances well before they show up as a surprise on the P&L.

If your team is still deciding whether Power BI or Business Central's native pages are the better home for daily monitoring, our piece on Analysis Mode in Business Central covers the built-in option that doesn't require a Power BI Report Business Central at all. 

If you're still weighing the broader case for business intelligence ERP work, or why integrate Power BI into your ERP software at all, those two pieces cover it from a step back. Some teams are now pairing reports with Copilot in Power BI for a plain-language summary before a meeting, a nice bonus once the core reports are stable.

What a Working Power BI Report Business Central Looks Like

AmNor Industries, a Quebec-based industrial company, went through this exact progression with Gestisoft, stabilizing their Business Central core before turning to reporting.

"Now that we have good data, we need to get them talking," said Sonia Préville, Corporate Finance Director at AmNor Industries, describing their next phase of work with Power BI once their accounting data was clean and centralized.

That order counts. A Power BI Report Business Central built on messy data just makes the mess easier to look at. AmNor fixed the data first, then turned to Power BI for the analysis layer on top.

If your team ends up leaning on the cash flow forecast as one of your go-to reports, here's a quick look at how that forecast comes together in Business Central before it ever reaches Power BI.

Common Mistakes Companies Make With a Power BI Report Business Central Setup

A few patterns show up again and again once companies build their own reports.

Connecting a report directly to production data with no refresh schedule is the most common one. Reports look live but quietly go stale, and nobody notices until a decision gets made on last week's numbers.

Skipping the primary key filter on embedded FactBox reports is another. Without it, the report shows the same data regardless of which record you're viewing, defeating the point of embedding it in the first place.

Treating the built-in AppSource apps as a permanent fix, rather than a starting point, also tends to catch up with growing companies. What works fine for a ten-person finance team often can't keep up once you're running multiple entities or need dimension-level detail the demo app was never built to show.

One more pattern shows up almost as often: nobody assigning report ownership. A Power BI Report Business Central that nobody owns is a report that slowly drifts from what the business needs. New product lines get added to Business Central, new dimensions get introduced, and the report just doesn't reflect them until someone finally complains loudly. Assigning a single owner, even part time, keeps it useful well past its first three months. If nobody internally can take that on, it's usually cheaper to bring in one of the ERP consultants in Canada who already do this for a living than to let the report quietly rot.

Final Thoughts on Power BI Report Business Central

A Power BI Report Business Central can either be a five-minute AppSource install or a properly engineered piece of your reporting stack, and most companies need both at different points. The built-in apps work fine for standard finance and sales visuals. The moment your reporting needs get specific, whether that's multi-company consolidation, custom dimensions, or a Canadian data residency requirement, the kind of custom ERP work a proper report requires stops being optional.

If your team is already eyeing Copilot on top of your reports, a Copilot readiness assessment makes sense before that project starts, not after. If your team is still exporting to Excel every month, that's usually the clearest sign it's time for a real conversation.

If you’re ready to explore Power BI Report Business Central, book a free call with a Gestisoft representative today and see what it can do for you!

Explore More:

• What are the benefits of Power BI? A shorter primer if you're still building the internal case for budget.
• How to choose an ERP software using 10 criteria For teams evaluating Business Central before any reporting work starts.
• Copilot ROI What to expect once Copilot sits on top of your Power BI reports.
• Business Central Financial Management The finance module most of these reports pull from.
• How Dandurand Transformed Its Operations with Business Central and Gestisoft A Canadian wine agency's Power BI dashboards, built on Business Central, in production today.