Back in 2017, I wrote a blog post describing how to create a Power BI model across multiple companies.
Power BI – Choosing Company name in oData connection – Peik’s Corner (versionmanager.dk)
This will still work in Dynamics NAV installations. However, in Business Central installations and in the cloud, it is necessary to use the method described in Steven Renders’ blog post:
HOW DO I: CREATE A MULTI-COMPANY POWER BI REPORT WITH THE BUSINESS CENTRAL CONNECTOR?
So here is the same setup as I made it in 2017 once again using the Business Central connectors.
Create three queries:
- Customers, based on an oData connection published from page 21.
- CustTrans, based on an oData connection published from page 25.
- SalesPeople, based on an oData connection published from page 14.
Normally they would be based on the OData connection:
http:[Server]:[Port]/[Instance]/OData/Company(‘CRONUS%20International%20Ltd.’)/Customers
to make a query to a table, but if the company name should be selectable, that is not an option.
Instead, make a query based on the Business Central connector, which demands a secure connection. Therefore, we will use the BC220_NavUser service tier:
And enter the endpoint:
That way, you will get the following choices:
So, open the first company and select the desired table:
Then click OK/Transform Data depending on where the Get Data was initiated.
Here it is showing the data from the first company only. Therefore, the last applied step is deleted, giving us this:
Mark the Company (Name) and the Data columns and remove the remaining:
This will leave these two columns:
Now click the Expand icon and select the Name and Data Columns:
This gives a view of all tables in the companies:
Filter to show only the Customers
Lastly, Expand the Data.Data column:
Now select the fields to include in my dataset. The Company Name column will be added to the query with the name: Name. This will have to be renamed to Company Name. That can be done later.
Then click OK.
The result looks something like this:
Remove the Data.Name Columns and Rename The columns to CompanyName,
Customers.Name, Customers.No, Customers.SalesPerson_Code.
Then add the other two tables by clicking Recent query:
Repeat the process described above. Name the two tables CustTrans and SalesPeople. Include the following fields in each table:
CustTrans:
- Posting_Date
- Document_Type
- Document_No
- Customer_No
- Currency_Code
- Amount
- Amount_LCY
SalesPeople:
- Code
- Name
Next, it is necessary to go through all fields and make sure that the datatypes are correct. Strangely enough the datatypes of the fields are better than if the CustTrans query is address directly, but it still needs to be done. Now only the Date Time fields must be changed to Date.
Now it is necessary to rename all company names from Name to CompanyName in all queries.
Power BI cannot handle linking two queries together with multiple fields, therefore we need to make a small work-around.
We will create a new primary key for each table.
To make a new primary key consisting of both CompanyName and Customers.No and CompanyName and SalesPeople.Name, we need to create one or more extra fields in each query.
Therefore, click the Add Column tab, (If you don’t have an Add Column tab, then you are not in the Power Query editor, and you need to click Transform Data first) and click the Custom Column action and add the fields in each query:
Customer:
- CustomerKey = [CompanyName] & “-” & [Customers.No]
- SalesPersonKey = [CompanyName] & “-” & [Customers.SalesPerson_Code”]
CustTrans:
- CustomerKey = [CompanyName] & “-” & [Customers.Customer_No]
SalesPeople:
- SalesPersonKey = [CompanyName] & “-” & [SalesPeople.Code]
Now we can link the tables together.
Click the Home tab and then Close & Apply.
Then click the Relationships icon.
Now you will see that, because I named the fields the same, Power Bi automatically found the link between the queries. If you have named the fields differently, you will have to make the links manually.
Then I need to make a filter on the CustTrans query to see only Invoices and Credit Memos:
Now it is possible to make a model:
So, I go to the report area and select a Matrix visualization:
Rows:
- Customers.Name
Columns:
- SalesPeople.Name
Value:
- Amount
And a Slicer to select the CompanyNames.
You can add other visualizations to add more filters and spice it up a little.
It could look like this:
Note that the Amount and Amount_LCY fields are not the most suitable fields for the model since it can include VAT. The best would be to publish a new page as a copy of page 25 (Customer Ledger Entries) with the Sales (LCY) field and use that instead.
