Use Business Central in Power BI Dashboard

What if you could replace spreadsheets with the #1 analytics tool by this time next month?

‍

Getting started with showing Business Central data in Power BI can be daunting, but at the end of this guide you will be ready to visualize any KPI your team needs. The guide walks you through these critical steps:

‍

Getting set up for the first time
Connecting to the data
Publishing your reports
Important tips and tricks

‍

Setting up Power BI with Business Central for the first time

‍

Before we jump into it, let's outline an important distinction.

‍

What is the difference between Power BI Dashboard and a Report?

‍

Report and dashboard are two words that are often intermixed within Power BI but they are two distinctly different things. A Power BI Dashboard is flexible grouping of various reports. It allows you to mix and match individual reports into a single page view.

‍

Power BI Reports are multi-page visualizations of metrics and data. Easiest way to go is to begin designing reports, and then compile dashboards as needed.

‍

‍

Getting started with Power BI is a straight forward process. Once you have successfully connected to your Microsoft Dynamics 365 Business Central to Power BI, the connection details will be saved in the report designer making it easy to reuse going forward.

‍

You open a finance dashboard, and can clearly see how costs are trending. The operations report shows your bottlenecks and areas of opportunity. Sales trends are crystal clear.

‍

The steps to getting started with Power BI:

‍

Purchase a Power BI Pro License
Download and install Power BI Report Designer
Connect Power BI to Microsoft Dynamics 365 Business Central using the data source connector
Select appropriate Business Central environment
Import relevant tables

‍

Purchase a Power BI Pro License

‍

Purchasing a Power BI Pro license is the easiest first step in the process. It is just $9.99 per month and can be purchased through a self-service on Microsoft's Power BI page.

‍

You are also able to get a free 60 day trial to test before purchasing.

‍

Download and install Power BI Report Designer

‍

The Power BI Report Designer is the desktop tool for connecting to your data sources, configuring your data, and designing the report components.

‍

These capabilities are also available through the Power BI online portal, however we typically find it easier to use the desktop designer.

‍

‍The Power BI report designer can be downloaded here.

‍

Download Power BI Desktop Report Builder for D365 Business Central — Option #1 is the correct one: Microsoft Power BI Desktop

‍

Connect Power BI to Microsoft Dynamics 365 Business Central using the data source connector

‍

Once you've installed Power BI Desktop, we recommend signing into your Microsoft account that is associated with your license.

‍

‍

To connect Power BI to your Microsoft Business Central account, first click on the Get Data button in the toolbar. Second, search for Business Central in the data source pop-up.

‍

Finally, select Dynamics 365 Business Central and click connect.

‍

Connect Power BI to business central cloud and on premise

‍

Select appropriate Business Central environment

‍

Now that you are connected to your Business Central instance, you will need to select the environment you want to draw the data from. You can see in the below example that we have two environments, a sandbox and production.

‍

The choice for which to use depends on your circumstances, but you will be able to switch them at any time.

‍

Connect Microsoft Dynamics 365 Business Central to Power BI

‍

Selecting the environment is as simple as clicking on the expand drop down from the list above.

‍

Import relevant Business Central tables

‍

Once the environment has been expanded, you will see a list of companies available within that environment.

‍

Again, you will need to select the one that is appropriate for the report you are planning on designing. You can then expand that company and see the full list of tables available to import.

‍

Select the tables you want and click the Load or Transform Data buttons.

‍

Connect Power BI to Business Central Tables

‍

Now your data will be loaded into your Power BI report, and ready for you to get started!

Picture it: Your weekly stand-up meeting has detailed charts, figures, and calculations, and no one had to spend any time preparing them.

‍

Here are a few tips that we believe will be helpful as you get started with using Power BI with Business Central:

‍

Find a Power BI template to use
Transform your data
Pay attention to automatic relationships created on import
Create a date table and relationship

‍

Find a Power BI template to get started with

‍

When you first open Power BI Desktop you are greeted with a blank slate. Creating a great report helps when you are have a template to serve as a starting point.

‍

We recommend spending some time searching for a free Power BI template to use.

‍

Transform your data on import

‍

When you've selected the tables to import, we recommend hitting the Transform Data button instead of load. This will open the Power BI Query Editor and allows you to add transformation steps to the query.

‍

Some important ones transformation steps can include:

‍

Split columns
Changing data types and formats
Expanding columns to view more associated data
Rename columns for easier reference
Remove unimportant columns to eliminate clutter
Rename query for better reference on source of data

‍

Pay attention to automatic relationships created on import

‍

There is a standard setting that is enabled when you install Power BI Desktop that handles relationships between tables. This can be very helpful for associating primary keys and related columns between tables.

‍

However, this feature can sometimes create relationships which you are not interested in using and will create problems if you do not know it exists.

‍

Review your relationships tab and verify that only the relationships you're interested in are enabled.

‍

Power BI automatic autodetect new relationships after data is loaded

‍

Create a date table and relationship

‍

A great habit to get into is to create date tables and relationships to it from your primary date field. By doing this you can properly display time-series data without missing dates, months, and other issues.

‍

You can create the initial date field by using: Date = CALENDAR (DATE(2012,1,1),DATE(2025,1,1)) and then mark that table as a date table.

‍

Other important date table columns include:

‍

Month Year = FORMAT('Date'[Date], "MMMM YY")
Month Number = MONTH('Date'[Date])
Week = WEEKNUM('Date'[Date],1)

‍

Creating the relationship from your primary date field to the date table is done in the relationships tab. An example of this would be the posting date of a document.

‍

Publishing your Power BI Report

‍

Once your report is ready to be used, you are able to publish it to your account workspace.

‍

Workspaces allow you to segment dashboards, reports, and data sources into the groupings that you need.

‍

Publish Power BI Report using Business Central

‍

Once published, you will be able to open it in Power BI online and share the report with anyone else who needs access.

‍

Published Power BI Report with Business Central

‍

An instant ROI. With clear objectives and guidance your team got started quickly, and the time savings from pivot table maintenance is startling.

‍

Get a jump start on upgrading your teams reporting. Chat with our team to get a clear map to success.

‍