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

by ⋅ 44 Comments

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

Discover more from think about IT

Subscribe to get the latest posts sent to your email.

Posted in: Dynamics 365 Business Central, Dynamics NAV, Power BI

44 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?

    Reply↓
    • srenders on June 19, 2019 at 09:45 said:

      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.

      Reply↓
  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 ?

    Reply↓
    • srenders on October 16, 2019 at 13:25 said:

      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

      Reply↓
      • 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.

        Reply↓
        • srenders on October 16, 2019 at 14:02 said:

          and does your mycompany contain any data?

          Reply↓
          • 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.

          • srenders on October 16, 2019 at 14:42 said:

            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 ?

          • srenders on October 16, 2019 at 14:57 said:

            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.

          • srenders on October 16, 2019 at 16:48 said:

            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.

          Reply↓
  3. srenders on October 16, 2019 at 14:30 said:

    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?

    Reply↓
  4. Jeff on October 30, 2019 at 23:21 said:

    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?

    Reply↓
    • srenders on October 31, 2019 at 08:57 said:

      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?

      Reply↓
      • Jeff on November 1, 2019 at 19:54 said:

        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.

        Reply↓
        • srenders on November 2, 2019 at 13:46 said:

          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.

          Reply↓
          • srenders on December 15, 2019 at 15:23 said:

            After testing I found out there was actually a bug in the connector. Actually not a bug but a caching issue. It has been picked-up by Microsoft and a fix will be released very soon.

          • Claus Rasmussen on February 6, 2020 at 13:00 said:

            Any news on fix for the connector problem? I also have the problem that all records are doubled pr company

        • balamurugan on December 23, 2019 at 07:58 said:

          Facing Same issue..Any update on this

          Reply↓
  5. srenders on February 6, 2020 at 13:17 said:

    No update yet, I’m still waiting for the January release of Power BI Desktop…

    Reply↓
    • Claus Rasmussen on February 6, 2020 at 13:43 said:

      I just tried to download the newest version and test again. Now it does not duplicate the lines but just make one line pr company and no other data. This is wierd because in the query editor everything looks fine.. I hope they fix this soon as this i breaking reporting scenarios at two of our customers 🙁

      Reply↓
      • Claus Rasmussen on February 6, 2020 at 14:08 said:

        I think a workaround is to set the dataimport cache to 0. I will do some further testing…..

        Reply↓
        • Claus Rasmussen on February 6, 2020 at 14:21 said:

          Setting the cache to 0 works on the desktop Power Bi, but as soon as I upload the report to the cloud it is broken again 🙁

          Reply↓
          • Claus Rasmussen on February 19, 2020 at 09:43 said:

            I just downloaded the february version of Power Bi and did a quick test. I think the multicompany feature works again. I will do some further testing in the coming period. It would be nice if you could write back at some point if you find out if the issue is solved also. I really hope it is, because your multicompany solution is clearly the best approach.

  6. Balamurugan.r on March 3, 2020 at 09:33 said:

    Hope Feb 2020 release works fine for the above case. able to fetch multicompany data without duplications. Kindly share if anyone facing any issues.

    Reply↓
  7. Claus Rasmussen on March 30, 2020 at 12:57 said:

    It seems that when using this approach, the removal of the navigation step also remove the automatic type recognition on the columns, so one has the manual go through all columns and set the type again. Is there are smarter or more automated solution to this or am I doing something wrong?

    Reply↓
    • Matt Goodson on April 30, 2020 at 17:56 said:

      Claus, you are correct this does remove the automatic type recognition, but after doing the above steps, highlight all columns in the Power Query Editor and in the ‘Transform’ tab select Detect Data Type, and this will detect the data type. From the fields I used they were all set correctly.

      Reply↓
  8. Matt Goodson on April 30, 2020 at 17:52 said:

    Any thoughts on how this might work for creating ‘templated’ apps and distributing to clients via app source installer? Has anyone attempted doing so, because from my attempts it appears to break as it cannot find the Web API when connecting to “your data” during my tests.

    Reply↓
  9. Johan on June 2, 2021 at 16:30 said:

    Hi
    Thanks for the guide! With your help I have managed to make a report in Power BI Desktop that shows data from all companies in my onprem BC.
    It also works fine from within BC. In one company…
    When I change company I get a token error-message. If I set it up to work from that company (Assisted setup – set up Azure AD and the “Get started…” from Power BI page part) it does work, but if I switch company again, I get the token error in the first company.

    It seems I can not get the embedded Power BI reports to work in more than one company 🙁
    Have you managed to embed Power BI reports in several companies?

    Reply↓
    • srenders on June 4, 2021 at 09:05 said:

      No, embedding reports is currently a 1 company ‘thing’. I’m not aware of a way to pass along the company id to the Power BI report.

      Reply↓
      • Johan on June 4, 2021 at 13:13 said:

        Yes, I noticed that too. So I made a slicer like you did in your example above, to choose company to show data for.

        But it is when I change to another company that I cannot display any Power BI reports at all.
        In the Power BI section where the reports are displayed, it says: “powerbiToken parameter not specified.”
        Then I have to go through the set up of Azure AD and fill in Application ID and Key again (from the Azure Portal). After that I have the possibility to choose a report, but if I change company again I get the token error…

        I don’t know if it’s something with my onprem installation (BC 18) or if it’s a bug?

        Reply↓
  10. Christoph on May 23, 2022 at 12:03 said:

    Could you please write an explanation how this would look like in BC20 SaaS?
    I think i am not able to follow your steps in that version 😉

    Reply↓
  11. Vasant on June 9, 2022 at 13:30 said:

    I am not able to load a data in power bi for all companies. while connecting, it is showing me all the sandboxes and production in left pane, when I navigate to my Job table with respect to the required sandbox then data is not properly loading in query editor for all companies. It is loading data for the single company. How do I resolve this. I am trying to attach screenshot here but It will not get attached here.

    Reply↓
    • srenders on June 10, 2022 at 09:46 said:

      Do you have the My Company, or other ’empty’ companies in BC? Those usually give errors. If you filter them out it should work.

      Reply↓
  12. vasant Kolse on July 13, 2022 at 10:31 said:

    I have tried developing the multi company data report but data is not loading at the last step. May I know why it is not loading data.

    Reply↓

Leave a Reply

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

Post Navigation