Use Business Central in Power BI Dashboard

Quick guide. How to create Power BI Dashboards & Reports using Microsoft Dynamics 365 Business Central as the source of data. Set up, configuration, and ready for design.

GET MORE INFORMATION

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:

  1. Purchase a Power BI Pro License
  2. Download and install Power BI Report Designer
  3. Connect Power BI to Microsoft Dynamics 365 Business Central using the data source connector
  4. Select appropriate Business Central environment
  5. 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!

Need more information?
Chat with us

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.

"We bring the most value to our clients when we can simply help them optimize for what they do best. The only way that happens is by being hands-on & working closely with them the entire time. So that's what we do."

Kenn Andersen | LinkedIn | Let's chat!

Managing Director

Want to chat about what you read?

Send any questions you might have below & we will respond ASAP

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.