How to Integrate Dynamics 365 with Power BI (Beginner Tutorial)
In many mid-size firms, the monthly board pack still starts the same way. Someone exports a trial balance to Excel, pastes it into last month's workbook, repairs the links that broke, rebuilds two charts and emails a PDF on day eight. Meanwhile, every number in that pack has been sitting in Dynamics 365 since the books closed.
Several of us at Harbor & Hale ran finance teams before we became consultants, and replacing that workbook with a live Power BI dashboard is one of the most satisfying changes we help clients make. You don't need a developer to get started, but you do need to make a few decisions in the right order.
This tutorial covers prerequisites and licensing, connecting Business Central, connecting Dataverse-based apps such as Dynamics 365 Sales and Project Operations, building a first finance dashboard, scheduling refresh, embedding reports inside Dynamics 365, and the mistakes we see most often. It's written for controllers, finance managers and analysts. Microsoft adjusts menu names from time to time, so treat the click paths as a guide.
What Do You Need Before You Start?
Get these in place first and the rest of the tutorial goes smoothly:
- Power BI Desktop, the free Windows application where you build reports.
- A Power BI license plan. To publish and share reports, publishers and viewers generally need Power BI Pro, unless your organization hosts content in a Premium or Fabric capacity that allows free viewers. Some Microsoft 365 plans include Pro. Check Microsoft's current licensing for your situation.
- Access to the data. Power BI connects as you, so you'll only see what your Dynamics 365 permissions allow. In Business Central that means your permission sets; in Dataverse apps, your security roles.
- Connection details. For Business Central, your environment name (for example, Production) and company names. For Dataverse apps, the environment you want to connect to.
- A short list of questions. Agree on the five to seven numbers leadership actually wants before you open Power BI. That keeps the first dashboard focused.
Different Dynamics 365 apps connect in different ways. Here's the map:
| Data source | How to connect | Gateway needed? | Good first report |
|---|---|---|---|
| Business Central online | Microsoft's Power BI apps, or the Business Central connector | No | Monthly P&L trend, aged receivables |
| Business Central on-premises | On-premises connector or published web services | Yes, on-premises data gateway | Same as above |
| Dataverse apps (Sales, Customer Service, Project Operations) | Dataverse connector | No | Pipeline, case volumes, project hours |
| Finance and Supply Chain Management | Built-in analytics, or data exported to a lake or Fabric | Depends on the approach | Beyond a beginner tutorial |
If you're still deciding between Business Central and Dynamics 365 Finance, this comparison of Finance and Business Central explains why their reporting setups differ.
Part 1: How Do You Connect Business Central to Power BI?
Step 1: Decide between a ready-made app and your own report
Microsoft publishes ready-made Power BI apps for Business Central covering areas such as finance, sales, purchasing and inventory. You install them from the Apps area of the Power BI service, enter your environment and company when prompted, and sign in. Within minutes you have working reports.
Our advice: install the finance app first, even if you plan to build your own. It shows how the data is modeled and gives you something to show leadership on day one.
Step 2: Connect with the Business Central connector
In Power BI Desktop, choose Get data, search for Business Central and select the Dynamics 365 Business Central connector. Sign in with your work account. The navigator then lists your environments, the companies in each, and the available data: Microsoft's standard APIs plus any pages or queries your administrator has published as web services.
For a first finance dashboard, select general ledger entries, the chart of accounts, dimension values and customer ledger entries. Choose Transform Data rather than Load, so you can shape the data first.
Step 3: Shape the data in Power Query
Power Query is where you tidy the data before it reaches your model. For each table:
- Remove columns you won't use. Fewer columns mean faster refreshes.
- Filter general ledger entries to the last two or three fiscal years.
- Check data types, especially dates and amounts.
- Rename columns to finance-friendly names: "Posting Date," "Account No.," "Debit," "Credit."
Then create a separate date table covering your full date range, with fiscal year and fiscal period columns that match your accounting calendar.
Part 2: How Do You Connect Dataverse-Based Apps?
Step 4: Connect Sales, Customer Service or Project Operations
Dynamics 365 Sales, Customer Service and Project Operations store their data in Dataverse. In Power BI Desktop, choose Get data, then Dataverse, sign in, pick your environment and select tables such as accounts, opportunities, cases or project tasks.
Two practical tips. Choice fields (status, stage, priority) arrive as numeric codes with a separate label column, so use the label in visuals. And although Dataverse supports DirectQuery, start with Import unless you clearly need live data; it's faster and simpler to manage.
Step 5: Relate CRM data to ERP data
If your Sales app is integrated with Business Central, accounts and customers are already coupled. Use the customer number as the key between the two, so you can show pipeline next to receivables for the same customer. Keep relationships one-to-many, from lookup table to transaction table.
Part 3: How Do You Build Your First Finance Dashboard?
Step 6: Build a simple star schema
Put general ledger entries at the center as your fact table. Around it, add the chart of accounts (with account category), the date table and dimension values such as department or project. Mark the date table as a date table and relate it to the posting date.
Step 7: Write a handful of measures
A small set of measures covers most of a first dashboard. Adjust table and column names to match your model:
Net Amount = SUM('GL Entries'[Debit]) - SUM('GL Entries'[Credit])
Revenue = -CALCULATE([Net Amount], 'GL Accounts'[Category] = "Income")
Expenses = CALCULATE([Net Amount], 'GL Accounts'[Category] IN {"Cost of Goods Sold", "Expense"})
Net Income = [Revenue] - [Expenses]
Revenue YTD = TOTALYTD([Revenue], 'Date'[Date], "6/30")
The minus sign on revenue is deliberate. Income accounts carry credit balances, so without it your revenue shows as a negative number. The "6/30" tells the year-to-date calculation that the fiscal year ends on June 30; change it to your own year end, or drop it for a calendar year. Check the exact category text your data returns, because it can differ slightly by source.
Step 8: Lay out the page
Keep the first version to one page:
- Top row: cards for revenue, gross margin percentage, operating expenses and cash.
- Middle: a 12-month trend of revenue and expenses, with budget alongside if you keep budgets in Business Central.
- Bottom: aged receivables by bucket and the ten customers with the largest overdue balances.
- Slicers: company, department and period.
Before anyone else sees it, do what any controller would: tie revenue and net income for two closed months back to Business Central's own financial reports. If the numbers don't match to the dollar, fix that first.
Part 4: How Do You Publish and Schedule Refresh?
Step 9: Publish to a shared workspace
Publish from Power BI Desktop to a shared workspace, not your personal "My workspace." Give colleagues access through workspace roles or, better for a wider audience, publish a Power BI app from the workspace so viewers get a clean, read-only experience.
Step 10: Set credentials and a refresh schedule
In the Power BI service, open the settings for your semantic model (the dataset behind the report). Under data source credentials, sign in with OAuth using your work account. Then turn on scheduled refresh and pick times that suit your close calendar, such as early morning.
Pro workspaces allow a limited number of scheduled refreshes per day (eight at the time of writing); capacity-based workspaces allow more. Business Central online and Dataverse need no gateway, while Business Central on-premises requires an on-premises data gateway. Use credentials your team controls centrally, not those of an analyst who might move on, and turn on failure notifications.
Part 5: How Do You Embed Power BI in Dynamics 365?
Step 11: Embed reports in Business Central
Business Central can display Power BI reports in role centers and as FactBoxes on list pages. On the role center, use the Power BI part to select which reports appear. On a list such as Customers, the FactBox can show a report filtered to the selected record, so a credit controller sees that customer's payment trend without leaving the page.
Step 12: Embed reports in Dataverse-based apps
In model-driven apps such as Dynamics 365 Sales, an administrator first enables Power BI visualization embedding in system settings. Users can then add Power BI tiles or dashboards to their personal dashboards, and app makers can add reports to system dashboards. Viewers still need the right Power BI license and access to the underlying report.
What Are the Most Common Mistakes?
Most first attempts stumble on the same few things:
- Loading every general ledger entry since go-live, which makes refreshes slow and fragile.
- Skipping the date table, which breaks year-to-date and prior-period comparisons.
- Forgetting sign conventions, so revenue and liabilities appear negative.
- Combining several Business Central companies without a company column, and without handling intercompany eliminations.
- Building in "My workspace" or refreshing under one person's account.
- Sharing sensitive figures, such as payroll accounts, without row-level security.
- Publishing before the numbers tie back to the ledger.
Key takeaway: Start with Microsoft's ready-made app, then build one focused finance page on a clean star schema, reconcile it to the ledger, and only then publish and embed it.
Conclusion
Connecting Dynamics 365 to Power BI is less about technical skill than sensible order: confirm licensing and access, connect the right source, shape the data, build a small model, reconcile it to the ledger, then publish, schedule and embed. Follow those steps and the monthly export-and-paste routine gives way to a dashboard that refreshes itself and lives where people already work. If you're new to the wider product family, this beginner's overview of Microsoft Dynamics 365 is a helpful companion.
If you'd like a second pair of eyes on your first finance dashboard, the team at Harbor & Hale Consulting is always happy to talk.
Frequently Asked Questions
Do I need Power BI Pro to use Power BI with Dynamics 365?
You can build reports in Power BI Desktop for free. To publish and share them, publishers and viewers generally need Pro licenses, unless your organization uses a Premium or Fabric capacity that allows free viewers. Check Microsoft's current licensing terms for your plan.
Does Business Central include Power BI reports?
Business Central can embed Power BI reports in role centers and list pages, and Microsoft publishes ready-made Power BI apps for areas such as finance, sales and inventory. Power BI licensing is separate, so confirm that your users have the licenses they need to view shared content.
How often can Power BI refresh Dynamics 365 data?
Pro workspaces allow a set number of scheduled refreshes per day, eight at the time of writing, and capacity-based workspaces allow more. For most finance dashboards, one or two refreshes a day is plenty.
Can Power BI show real-time Dynamics 365 data?
Dataverse supports DirectQuery, which queries data live when a report is opened. For Business Central, imported data on a refresh schedule is the usual approach. Most finance reporting is based on posted, period-end data, so near-real-time is rarely necessary.
Can I combine data from several Business Central companies?
Yes. Pull the same tables from each company, append them in Power Query with a column identifying the company, and handle intercompany transactions so they don't double-count. For formal group reporting, align charts of accounts and dimensions across companies first.
