How to Connect Power BI with Microsoft Dynamics 365 Business Central and Build Live Dashboards - Microsoft Business Central Blog

How to Build Custom API Pages in Microsoft Dynamics 365 Business Central

In this complete guide, you will learn how to connect Power BI Desktop to Microsoft Dynamics 365 Business Central, clean and transform your data, build a Star Schema data model, write DAX measures, and create two fully interactive dashboards — a Sales Dashboard and an Inventory Dashboard — from scratch.

This tutorial is based on our complete video course on YouTube. Whether you are a Business Central user, a finance manager, or an ERP consultant, this guide will help you visualize your BC data in Power BI without writing a single line of code.

Prerequisites — What You Need Before Starting

Before you begin, make sure you have the following ready:

Power BI Desktop installed (free download from powerbi.microsoft.com or Microsoft Store)
A Microsoft Dynamics 365 Business Central licence with a valid Microsoft 365 login
Your BC Environment name (e.g., Sandbox or Production) — find it in the top-right corner when you log into BC
Your BC Company name — also visible in the top-right corner of BC
An active internet connection — BC is cloud-based and Power BI connects over the internet

💡 Note: Always start with your Sandbox environment. Sandbox is a safe copy of your live BC data. Mistakes in Sandbox do NOT affect your real company data. Switch to Production only after everything is tested and working.

In this course, we load three tables from Business Central: Customer, Customer Ledger Entry, and Item Ledger Entry. A Date Table is created inside Power BI using a DAX formula.

Step 1 — Connect Power BI to Business Central

Follow these steps to establish a live connection between Power BI Desktop and your BC environment:

Open Power BI Desktop on your computer. Close the Welcome pop-up if it appears.
On the Home ribbon, click Get Data.
In the left panel, choose Online Services. Find Microsoft Dynamics 365 Business Central in the list and click Connect.
A sign-in window appears. Enter your Microsoft 365 email and password — the same credentials you use to log into Business Central.
The Navigator window opens. Expand your environment name (e.g., SANDBOX). Expand your Company name. You will see all available BC tables.
Inside Standard APIs v2.0, find and tick customers and itemLedgerEntries. For Customer Ledger Entry, go to Web Services (legacy) and tick Cust_LedgerEntries.
Click Transform Data at the bottom — NOT Load. This opens Power Query for data cleaning.

💡 Note: Always click Transform Data instead of Load. Transform Data opens Power Query where you can remove unwanted columns, rename fields, and clean data before it enters your report.

Step 2 — Clean Data in Power Query

Business Central tables have 80 to 150 columns. For our dashboards, we only need 6 to 8 columns per table. Follow these steps for each table:

Customer Table — Keep These Columns

Select these columns using Ctrl+Click, then right-click → Remove Other Columns:

number → rename to Customer No
displayName → rename to Customer Name
city → rename to City
country → rename to Country
salespersonCode → rename to Sales Person
creditLimit → rename to Credit Limit
blocked → rename to Blocked

Customer Ledger Entry — Keep These Columns

customerNumber → rename to Customer No
postingDate → rename to Posting Date
documentType → rename to Document Type
documentNumber → rename to Document No
salesLCY → rename to Sales Amount
open → rename to Open
dueDate → rename to Due Date

💡 Note: After removing columns, add a Row Filter on Document Type. Click the dropdown arrow on Document Type → Text Filters → Equals → type Invoice. This keeps only invoice rows, making your Total Sales measure accurate.

Item Ledger Entry — Keep These Columns

itemNumber → rename to Item No
entryType → rename to Entry Type
quantity → rename to Quantity
postingDate → rename to Posting Date
locationCode → rename to Location

After cleaning all three tables, click Close & Apply on the Home ribbon in Power Query. Power BI loads the data into the report.

Step 3 — Create a Date Table Inside Power BI

The Date Table is not loaded from Business Central. We create it inside Power BI using a DAX formula. This gives us Year, Month, Quarter, and Month Number columns for time filtering.

Click the Modeling ribbon at the top.
Click New Table. A formula bar appears.
Paste this formula:

DateTable = CALENDAR(DATE(2021,1,1), DATE(2023,12,31))

11. Press Enter. The DateTable appears in the Data pane with a single Date column.

12. Right-click DateTable → New Column. Add these four calculated columns one by one:

Year         = YEAR([Date])
Month Number = MONTH([Date])
Month Name   = FORMAT([Date], "MMMM")
Quarter      = "Q" & QUARTER([Date])

Fix Month Name sort order: click Month Name column → Column Tools ribbon → Sort by Column → select Month Number. This ensures January appears before April, not alphabetically.
Mark as Date Table: right-click DateTable in the Fields panel → Mark as Date Table → select the Date column → click OK.

💡 Note: Marking the DateTable as a Date Table is essential. Without this step, Power BI time-intelligence DAX functions like TOTALYTD() and SAMEPERIODLASTYEAR() will not work correctly.

Step 4 — Build the Data Model (Star Schema)

The Data Model tells Power BI how your tables are connected. Without relationships, Power BI cannot combine data from two tables in one chart.

Click the Model icon on the left sidebar (three connected boxes). Your tables appear as boxes on the canvas. Create these relationships by dragging from one column to the matching column in the other table:

customers[Customer No]          →  Cust_LedgerEntries[Customer No]    (1 to Many)
DateTable[Date]                 →  Cust_LedgerEntries[Posting Date]   (1 to Many)
DateTable[Date]                 →  itemLedgerEntries[Posting Date]    (1 to Many)

A gold line appears between the tables when a relationship is created. Verify that the ‘1’ side is on Customer and DateTable, and the ‘*’ side is on Customer Ledger Entry and Item Ledger Entry.

Arrange the tables neatly: put Customer Ledger Entry and Item Ledger Entry in the centre. Place Customer and DateTable on the sides. This layout represents the Star Schema — fact tables in the centre, dimension tables around them.

💡 Note: You do NOT need to create a relationship between Customer and Item Ledger Entry — they share no common column. Power BI connects them indirectly through the Date Table.

Step 5 — Write DAX Measures

DAX (Data Analysis Expressions) measures calculate dynamically based on whatever filter is applied in the report. We create 7 measures in total — 4 for the Sales Dashboard and 3 for the Inventory Dashboard.

To create a measure: right-click the table in the Fields panel → New Measure → type the formula in the formula bar → press Enter.

Measure 1 — Total Sales

Total Sales =
CALCULATE(
    SUM( 'Cust_LedgerEntries'[Sales Amount] ),
    'Cust_LedgerEntries'[Document Type] = "Invoice"
)

Format as Currency. This measure sums only Invoice rows, excluding payments and credit memos.

Measure 2 — Total Customers

Total Customers = DISTINCTCOUNT( 'customers'[Customer No] )

Format as Whole Number. Counts unique customers regardless of how many invoices they have.

Measure 3 — Outstanding Balance

Outstanding Balance =
CALCULATE(
    SUM( 'Cust_LedgerEntries'[Sales Amount] ),
    'Cust_LedgerEntries'[Open] = TRUE()
)

Format as Currency. Sums all invoices where Open = TRUE (not yet fully paid).

Measure 4 — Overdue Amount

Overdue Amount =
CALCULATE(
    SUM( 'Cust_LedgerEntries'[Sales Amount] ),
    'Cust_LedgerEntries'[Open] = TRUE(),
    'Cust_LedgerEntries'[Due Date] < TODAY()
)

Format as Currency. Same as Outstanding Balance but adds a second filter: Due Date is before today. This measure updates automatically every single day.

Measure 5 — Total Items Sold

Total Items Sold =
CALCULATE(
    ABS( SUM( 'itemLedgerEntries'[Quantity] ) ),
    'itemLedgerEntries'[Entry Type] = "Sale"
)

Format as Whole Number. In BC, Sale entries have negative Quantity (stock going out). ABS() converts -150 to 150 to get a positive unit count.

Measure 6 — Total COGS

Total COGS =
CALCULATE(
    SUM( 'itemLedgerEntries'[Quantity] ),
    'itemLedgerEntries'[Entry Type] = "Sale"
)

Format as Currency. Represents your Cost of Goods Sold — the cost value of inventory that was sold.

Measure 7 — Total Purchased

Total Purchased =
CALCULATE(
    SUM( 'itemLedgerEntries'[Quantity] ),
    'itemLedgerEntries'[Entry Type] = "Purchase"
)

Format as Whole Number. Purchase entries have POSITIVE Quantity in BC — no ABS() needed. Shows total units received into stock.

Step 6 — Build the Sales Dashboard

Add a new page at the bottom of Power BI Desktop. Double-click the tab and rename it Sales Dashboard.

Set the canvas size: Format → Page Size → Custom → Width: 1920, Height: 1080.

Add 4 KPI Cards at the Top

Click the Card icon in the Visualizations panel. Drag the Total Sales measure into the Fields well. Resize to approximately 2.5 inches wide and 1.2 inches tall. Format with navy background and white text.

Copy the card three times (Ctrl+C, Ctrl+V). Change the measure and colour for each:

Card 1 — Total Sales: Gold background (#F39C12)
Card 2 — Total Customers: Steel Blue background (#4A6FA5)
Card 3 — Outstanding Balance: Orange background (#E67E22)
Card 4 — Overdue Amount: Red background (#C0392B)

Add Bar Chart — Top 5 Customers by Sales

Click the Clustered Bar Chart icon. Set Y Axis = Customer Name (from customers table). Set X Axis = Total Sales measure. Apply a Top N filter: Filters pane → drag Customer Name → Top N → Show Top 5 → By value: Total Sales → Apply Filter. Sort descending by Total Sales.

Add Line Chart — Monthly Sales Trend

Click the Line Chart icon. Set X Axis = Month Name (from DateTable). Set Y Axis = Total Sales. Drag Year from DateTable into Legend — this creates one line per year, colour-coded. Fix sort order by setting Month Name to Sort by Column = Month Number.

Add Pie Chart — Sales by Salesperson

Click the Pie Chart icon. Set Legend = Sales Person (from customers table). Set Values = Total Sales measure. Turn on Detail Labels with Category and Percent. The pie chart uses the relationship between customers and Customer Ledger Entry to correctly split sales by salesperson.

Add Slicers for Interactivity

Add three Slicer visuals: Year (Tile style), Country (Dropdown style), and Sales Person (List style). Place them on the right side of the dashboard. When a user clicks 2022 in the Year slicer, all charts and cards instantly update to show 2022 data only.

💡 Note: Use Format → Edit Interactions to control which visuals respond to which slicers. Right-click a visual → Edit Interactions → click the None icon on any visual you want to exclude from filtering.

Step 7 — Build the Inventory Dashboard

Add a second page and rename it Inventory Dashboard. Keep the same background colour as the Sales Dashboard for a consistent look.

Add 2 KPI Cards

Card 1 — Total Items Sold: Navy background (#1F3864), format Whole Number
Card 2 — Total COGS: Amber background (#E67E22), format Currency

Add Bar Chart — Top Items by Units Sold

Click Clustered Bar Chart. Set Y Axis = Item No (from itemLedgerEntries). Set X Axis = Total Items Sold measure. Apply Top N filter: Top 10 by Total Items Sold. Sort descending. This immediately shows your fastest-moving inventory items.

Add Column Chart — Monthly Sold vs Purchased

Click Clustered Column Chart. Set X Axis = Month Name (from DateTable). Drag both Total Items Sold AND Total Purchased into Y Axis. Power BI creates a grouped column chart with two bars per month — showing units sold versus units received side by side.

This chart answers the most important inventory question: are we selling faster than we are buying? If sold bars consistently exceed purchased bars, you are running down stock and risk a stockout.

Add Slicers

Add Year slicer (Tile style) and Entry Type slicer (List style — shows Sale, Purchase, Transfer). Add an optional Location slicer if your BC uses multiple warehouses.

Step 8 — Publish to Power BI Service

When both dashboards are complete and tested in Sandbox, publish the report to the Power BI cloud service:

In Power BI Desktop, click the Home ribbon → Publish button.
Choose your workspace (e.g., BizCentralOrbit Reports) and click Select.
Go to app.powerbi.com in your browser and sign in.
Navigate to your workspace. You will see your published report (BCODemo).
To switch to Production data: click the three-dot menu on the Dataset → Settings → Data Source Credentials → Edit Credentials → change the environment from Sandbox to Production → Sign In.
Click Refresh Now to load live Production data into your report.

💡 Note: Never switch to Production until you have fully tested all measures, relationships, and chart formats in Sandbox. Production data is your real company data — always verify first.

Step 9 — Schedule Automatic Refresh

Once published, set up automatic daily refresh so your dashboards always show the latest BC data without any manual action:

In Power BI Service, go to your workspace → find the Dataset (BCODemo).
Click the three-dot menu on the Dataset → Settings.
Scroll to Scheduled Refresh → toggle it ON.
Set the frequency (Daily) and time (e.g., 6:00 AM).
Add your email under Send refresh failure notifications to — Power BI will alert you if a refresh fails.
Optionally, subscribe to the report: open the report → Subscribe → add yourself → choose Daily at 8:00 AM. You receive a screenshot of your dashboard in email every morning.

Step 10 — Embed Power BI in Business Central Role Centre

As the final step, embed your Power BI dashboards directly inside the Business Central interface so users can see live data without switching applications:

Open Business Central → go to your Role Centre home page.
Scroll down — you will see a Power BI section with a Get Started link.
Click Get Started → follow the setup wizard (2-3 steps).
When the Power BI Reports Selection dialog appears, find BCODemo in the list → tick the Enabled checkbox → click Close.
Your Sales Dashboard or Inventory Dashboard now appears embedded directly on the BC home page — fully interactive!

💡 Note: Any BC user who also has a Power BI Pro licence can see the embedded report on their Role Centre. Users without a Power BI licence will see a prompt to subscribe.

Summary — What You Have Built

In this guide you completed the following:

Connected Power BI Desktop to BC Sandbox using Get Data → Online Services → Dynamics 365 Business Central
Cleaned three BC tables in Power Query — removed 80+ unnecessary columns, renamed fields, applied Invoice filter
Created a Date Table inside Power BI using the CALENDAR DAX formula with Year, Month, Quarter columns
Built a Star Schema data model with three relationships — Customer → CLE, DateTable → CLE, DateTable → ILE
Wrote 7 DAX measures — Total Sales, Total Customers, Outstanding Balance, Overdue Amount, Total Items Sold, Total COGS, Total Purchased
Built a complete Sales Dashboard with 4 KPI cards, bar chart, line chart, pie chart, and 3 slicers
Built a complete Inventory Dashboard with 2 KPI cards, bar chart, column chart, and 2 slicers
Published to Power BI Service, switched to Production data, and set up daily automatic refresh
Embedded the dashboard in the Business Central Role Centre for seamless access

If you found this guide helpful, watch the complete step-by-step video course on our YouTube channel where we build both dashboards live from scratch using real Business Central data.

To get more such useful information, please follow our LinkedIn page and you can also subscribe our you tube page.

YouTube Link: https://www.youtube.com/@bizcentralorbit

LinkedIn Link: https://www.linkedin.com/company/bizcentralorbit/posts/?feedView=all

If you want to book a 1-to-1 live session with any of our expert consultants then click the link: https://bizcentralorbit.com/#One-to

If you enjoyed this blog, you may also like: How to Call Claude AI API from Microsoft Dynamics 365 Business Central

If you want a Tutorial videos of “Add custom Cue Group on Roll Center Page by AL Language” then click the link: https://www.youtube.com/watch?v=pfa7VobkurQ

Raise a support ticket instantly by clicking the link: https://bizcentralorbit.com/contact-us/