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.
Once you get to this screen you need to get the URL from Business Central (BC) itself.
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.
Choose Inspect Pages
Take note of the Page Detail.
Next you search for Web Services on 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.
Back in Power BI
Simply copy the URL from the above step and paste it in Power BI.
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.
Populate the fields as below.
When you use the parameter option, you have to change your URL to look like this.
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.
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.
With Power BI, you can customise your reports, drill into transaction-level details, and merge Business Central data with other sources such as Excel, CRM systems, or SQL databases. For organisations looking to maximise the value of their Microsoft ecosystem, working with a trusted Dynamics 365 consulting partner ensures your Business Central environment is properly configured, maintained, and aligned with your broader data strategy.
Don’t forget to check out our training page to learn more about Power BI.
