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?
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.
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 ?
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?
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?
yes! item, customer and vendor, ledger entries…
do you think that depends on Power BI license ?
Yes i test but i cannot see data.
So you can’t see data using the BC-connector and also using the ODATA connector?
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?
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?
No data in query editor and in the browser even i’m super user for all companies.
BC in Saas or BC on prem?
Which user do you use to connect to BC? O365 or username/webserviceaccesskey/…?
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?
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.
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.
Any news on fix for the connector problem? I also have the problem that all records are doubled pr company
Facing Same issue..Any update on this
As I explained, a fix is coming soon. Wait until the next update of Power BI Desktop…
No update yet, I’m still waiting for the January release of Power BI Desktop…
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 🙁
I think a workaround is to set the dataimport cache to 0. I will do some further testing…..
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 🙁
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.
Hope Feb 2020 release works fine for the above case. able to fetch multicompany data without duplications. Kindly share if anyone facing any issues.
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?
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.
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.
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?
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.
Vote for this idea:
to get it implemented…
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?
Personally have not set this up with BC on prem since some years now. I think it must be a bug indeed. I would imagine when you change company that you don’t have to do the setup again. It could be interesting to post on the Yammer group:
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 😉
I have already done that, you can find it here:
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.
Do you have the My Company, or other ’empty’ companies in BC? Those usually give errors. If you filter them out it should work.
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.
If you use the ODATA connector, then indeed, multi-company works in PQ, but does not load into the data model. That’s a bug, reported to Microsoft some time ago, and no fix yet.
With the BC Connector, it should work.
Maybe try this example: https://thinkaboutit.be/2022/02/business-central-power-bi-multi-environment-template/