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?

https://plataan.tv/en/plataan/training-course/business-central/power-bi-for-microsoft-dynamics-365-business-central

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

  1. Avinash on June 19, 2019 at 00:02 said:

    how you resolve the relationship? Multiple company could have same primary key values. Is it efficient to work with multiple tables?

    • A very good question, and I’m surprised it was not raised before. You need to create a unique key, for example in power query (or dax). An example of such a key could be the concatenation of the Company and the Pk of the table in question.

  2. Steven on October 16, 2019 at 13:11 said:

    Hello,
    In cannot access to mycompany. How can i have access to the other company ?
    I’m a super user in BC.

    Could you help me ?

    • Hi Steven,

      Where can’t you access mycompany? In PowerBIDesktop, in Business Central?
      Are you a SUPER user in both companies? Do you receive any error message?

      Greetings,
      Steven

      • Steven on October 16, 2019 at 13:29 said:

        through power bi desktop. yes super user both companies.
        No error message. It’s when I tried to use BC Connector as you described in your post.
        I only have one company.

        • and does your mycompany contain any data?

          • Steven on October 16, 2019 at 14:27 said:

            yes! item, customer and vendor, ledger entries…
            do you think that depends on Power BI license ?

          • Steven on October 16, 2019 at 14:41 said:

            Yes i test but i cannot see data.

          • So you can’t see data using the BC-connector and also using the ODATA connector?

          • Steven on October 16, 2019 at 14:53 said:

            No I cannot see data on both with BC connector and odata connector.
            Do you know what is the problem ?

          • And can you see the data in the query editor? I recently found a bug where I could see the data in the query editor, but not whan I applied it in PBI-Desktop.
            And, when you use the ODATA-URL and paste it in your browser, do you get any data?

          • Steven on October 16, 2019 at 15:50 said:

            In Power BI => O365.
            In odata => username/WSkey

            this is BC Saas.

          • Hmmm, really strange…
            Just as a test, can you create a new company (standard withndemo data) and then test to see if you can see it’s data in PBI?

        • Steven on October 16, 2019 at 15:03 said:

          No data in query editor and in the browser even i’m super user for all companies.

  3. No, I think it might be a bug in the connector. Can you test if you can see the data from mycompany if you use the ODATA feed instead?

  4. Great article. I was able to pull data from 15 companies with 1 query in BC. However, I’m having an issue when I pull the GL Entries table. I get all 15 companies, but the data is repeating for each company. In other words, If I count the number of transactions for each company; I get the same count. I even tried to make individual queries to each company and get the same result. The exact same transactions from one company are applied to all companies. I think it may be something with the Power BI Connector. Any ideas?

    • You should investigate the filters you have put on the query. Normally it should only pull the data per company. If you do the same thing in Excel, does that work better?

      • I can’t call the Business Central Connector from Excel. This seems to be a Power BI issue not an issue with pulling data from BC. If I use the OData links from BC, I can open them fine in Excel; but its for each individual company. I’m trying to pull data from all companies as described above into one table.

        The method above doesn’t seem to work. It basically pulls all of the data from 1 company and then replicates that same data set for each company in the production BC tenant. If I use Power BI and the BC connector to pull data for a table for 1 company at a time (with unique queries); it still duplicates the data. I’ve also taken off parrallel mode too.

        Any ideas? Seems to be a buggy connector.

        • I will also do some more tests. But I can confirm I also don’t like the connector. Instead I always try to use the ODATA feeds, they are much more reliable and allow for more parameterisation and allow you to connect to a sandbox.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Post Navigation