Your first Power BI report off Business Central data, end to end

If you've been exporting Business Central to Excel and rebuilding the same report every week, this is how you replace that with a Power BI report that refreshes itself. We'll build a simple but real one, sales by customer and month, from a blank canvas to a published, shareable report.

Power BI is Microsoft's reporting tool. Business Central is its ERP (enterprise resource planning) system.

Before you start

• Power BI Desktop, the free Windows app where you build reports. (It's Windows-only. On a Mac, you'll need a Windows virtual machine or an Azure Virtual Desktop.)
• A Power BI account on your work email, and a Pro license when you're ready to publish and share.
• Access to Business Central with permission to read the data.

Step 1: Connect to Business Central

1. Open Power BI Desktop, select Get Data, and search for the Dynamics 365 Business Central connector.
2. Sign in with your work account and pick your environment and company.
3. You'll see a list of available data. Start small: choose a sales table, plus Customers and Items. Resist selecting everything; a lean model is a fast model.
4. Select Transform Data to open the query editor rather than loading immediately.

Step 2: Shape the data in Power Query

In the query editor, for each table: remove columns you won't use, confirm each column's data type (dates as dates, amounts as decimals), and rename anything cryptic. Then select Close & Apply.

Step 3: Build a simple model

1. Add a date table so time-based reporting works, and mark it as your date table.
2. In Model view, draw relationships: Customers to your sales table, Items to your sales table, and your date table to the sales table's posting date.
3. Aim for a "star": your sales table in the middle (the facts), the others around it (the dimensions). This one habit prevents most beginner headaches.

Step 4: Write your first measures

In the fields pane, create a measure (not a calculated column): Total Sales = SUM(SalesLine[Amount]). Add one or two more, like a line count or margin. Measures are reusable and fast; columns bloat your model.

Step 5: Build the visuals

1. Drop in a Matrix: customers down the rows, your date table's month across the columns, Total Sales in the values. That's your sales-by-customer-by-month grid.
2. Add a column chart of Total Sales by month for the trend.
3. Add a card for total sales, and a slicer for date or customer so readers can filter.
4. Format for humans: clear titles, comma and currency number formatting, a sensible color.

Step 6: Publish

Select Home, then Publish, and choose a workspace (a shared workspace, not "My workspace," if others will see it).

Step 7: Share, and set up refresh

1. In the Power BI service, publish an App from the workspace or share the workspace with viewers. Viewers generally need a Pro license too, which is worth knowing before you promise access to twenty people.
2. Set up scheduled refresh on the dataset and enter your Business Central credentials so the report updates itself. Pick a daily time. (If you ever point Power BI at an on-premises source, that's when you need the on-premises data gateway, a separate setup.)

Where to take it next

You now have a living report instead of a weekly chore. The highest-value next skill is DAX time intelligence, the measures that give you year-to-date and this-year-versus-last-year, which is the exact thing finance always asks for. If you want a hand getting started, let's talk it through.