Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

Power BI and Business Central

Power BI Sharing

Power BI and Business Central

 

Does your company use Business Central.  Today I’ll be showing you how to connect Power BI and Business Central using the Odata connector.

 

Microsoft Business Central is an all-in-one business management solution that helps organizations streamline their processes, improve visibility, and drive growth. It offers comprehensive functionalities for finance, supply chain management, sales, and more. Business Central exposes its data through OData, enabling seamless integration with other applications and services.

 

I know you can embed Power BI reports within Business Central, however that is not what this post is about.  I am going to show how to query basic BC data within Power BI and publish it to the service.

 

Connecting Power BI and Business Central

 

To connect Power BI and Business Central, you will use OData connector in Power BI.  This you will find in the Get Data option in Power BI and search for OData.

Odata GetData

 

Once you get to this screen you need to get the URL from Business Central (BC) itself.

Odata Feed

Go to Business Central

 

Head over to BC.  Say, for example, you want to query Posted Sales Invoices. Go to that table in BC.

Go to Help and choose Help & Support.

HelpandSupport

Choose Inspect Pages

InspectPages

 

Take note of the Page Detail.

Page Inspection

 

Next you search for Web Services on BC.

Webservices BC

 

Search for the table you noted above, if it is not there, you can simply add it.  The URL you need will be in this table.

OData URL

 

Back in Power BI

Simply copy the URL from the above step and paste it in Power BI.

PasteURL

 

This brings in ALL of your invoices with many columns.  Now you can transform your data as you see fit, by renaming columns, removing duplicates, creating calculated columns, and merging tables to prepare the data for analysis.

 

As you will find, is that this can be a lot of data, perhaps some unnecessary data.  So next I will show you how to add a filter to your URL to reduce the size of your data, should that be needed.

 

Identify how you want to filter the data.  I will use the Posting date, so only bring in data from 1 Jan 2024.

 

There are 2 ways of doing this, either by adding a parameter and querying the parameter in the URL, or just putting the actual date in the URL.  See both options below.

Parameter option:

Set up parameter by adding a New Parameter.

NewParameter

 

Populate the fields as below.

Parameter

 

When you use the parameter option, you have to change your URL to look like this.

URL_Parameter

 

Actual Data option:

 

You also put the date straight into the URL to filter by the date.  Below is the URL that will give the same result as the parameter option.

URL_Date

 

Check out this blog if you experience any issues with the Odata Connector.

Once the data is imported and transformed, you can start building reports and visualizations using Power BI’s intuitive drag-and-drop interface. Explore your data, create insightful visualizations, and gain actionable insights to drive informed decision-making.

 

Business data is dynamic and constantly evolving. Power BI allows you to schedule automatic data refreshes to ensure your reports and dashboards are always up to date with the latest information from Microsoft Business Central.

 

In conclusion, connecting Power BI to OData sources like Microsoft Business Central empowers organizations to unlock the full potential of their data, driving smarter decision-making and fostering innovation. By leveraging the integration capabilities of Power BI and the comprehensive data insights offered by Business Central, businesses can gain a competitive edge in today’s data-driven world.

 

Don’t forget to check out our training page to learn more about Power BI.

Leave a Comment

Your email address will not be published. Required fields are marked *