How Do I: Create a multi-company Power BI report with the Business Central connector?

Imagine that you would like to build a report where you display the inventory by item by company:

and you want to do this for Business Central (Saas) using the Power BI connector.

Let’s see how that would normally go…

Launch Power BI Desktop and Get Data using the Business Central connector:

In the prompt, authenticate with your Office 365 login (email and password). This connector then automatically connects to your Business Central cloud tenant. And this is a great feature, because you don’t need to figure out the ODATA URL, the tenant ID, the user web service access key, and so on…

Now the navigator opens and displays the following:

The Power BI Navigator connects to my Business Central instance and displays the list of companies (I have access to). In each of these companies, when you expand them, you can see the list of ODATA webservices:

And when you select an odata feed in one of the companies, it will only return the data for that feed for the selected company.

So how can we turn this into a multi-company feed?

Well this requires some fiddling in the query editor…

Select the feed you are interested in. In my case I want to create an overview of the inventory by item, so I will select my query: _qryItems and then open it in the editor:

As you can see in the image here above, in the query editor there are two steps: Source and Navigation. When you remove the Navigation step, the source is visible and it contains the company names:

Hmmm, this is interesting. Now we have the company and also the data. Let’s see what wen can do now…

Let’s start by removing the columns we don’t need:

Now expand the Data column:

Then remove all other columns except: Name, Name.1 and Data.1, the Display_Name is optional:

Then apply a filter in the Name.1 column to select the query you are interested in:

Then expand the Data.1 column and select the fields you are interested in:

And voila, you are ready:

You can now rename your columns if required, and verify the data types. Then hit Close and Apply:

Your item data has been imported, including a company column. Now you can let loose your designing skills and design the layout:

And if you require more data then you can repeat these steps to import another web service

Would you like to know more about this subject?

Comment List