When you use Power BI Desktop (or Power Query) to connect to a Dynamics NAV ODATA feed, the URL you pass looks like this:

http://thinkaboutit:7048/NAV2016CU6/OData/Company('CRONUS%20BELGI%C3%8B%20NV')/

where thinkaboutit is the server running your service tier, 7048 is the ODATA port and NAV2016CU6 is the Service Tier Instance.

Company('CRONUS%20BELGI%C3%8B%20NV')/

means that you are retrieving data from a specific company, in this case: CRONUS BELGIË NV.

pbi1

pbi2This means that, if your report (or dashboard), needs to fetch (or display) data from another company, you need to update all your Queries.

For example, the following URL fetches data from the company CRONUS BELGIQUE SA:

http://thinkaboutit:7048/NAV2016CU6/OData/Company('CRONUS%20BELGIQUE%20SA')/

What if you could manage this to be more dynamic? Well, you can…

In one of the last updates of Power BI Desktop, Parameters were introduced.

So, instead of starting via Get Data and then ODATA Feed, let’s start with Edit Queries:

pbi2

Then, in Manage Parameters, select Manage Parameters:

pbi3

In the window that opens, create a new parameter, named DynamicsNAV as follows:

pbi4

In Type select Text, in Allowed Values select List of Values, and in the list paste both URLs:

http://thinkaboutit:7048/NAV2016CU6/OData/Company(‘CRONUS%20BELGI%C3%8B%20NV’)/

http://thinkaboutit:7048/NAV2016CU6/OData/Company(‘CRONUS%20BELGIQUE%20SA’)/

Then select one of the two as the Default and Current Value.

This results in the following Parameter:

pbi5

Click Close and Apply.

Now, select Get Data, ODATA Feed:

pbi6

As you can see, now you can select your DynamicsNAV parameter here, which contains the URL to the NAV ODATA web services.

When you select OK, you need to authenticate:

pbi7

And it connects, and you can see the available web services:

pbi8

If you open the Advanced Editor, you will see the following M (Power Query) statement:

pbi9

How cool is that!?

Now, if you select information from one of the available ODATA Web Services, for example powerbifinance:

pbi10

pbi11

and click on Close & Apply, then you see this:

pbi12

The query returns data from CRONUS BELGIQUE SA, and you see the KPI_Name in French.

Now select Edit Parameters and select the other value:

pbi13

pbi14

The table refreshed and now you see data from the other company.

Now you have made your query, report, dashboard so it can connect and display data from any of the companies in your database.

Unfortunately, the possibility to select a value for a query parameter is only available in Power BI Desktop. This means that when you upload your Power BI Desktop file to PowerBI.com, it will always use the default value for the parameter. But I think, in the future this will probably change.

So, now you can create Power BI Desktop files as templates, using parameters to connect to any company in your database. Nice, isn’t it?

Parameters have many more advantages and in the latest update of Power BI Desktop they can now receive their values from another query. I’ll blogg about that nifty feature very soon 😉

3 Thoughts on “How Do I: Use a Company Parameter in Power BI Desktop?

  1. Pingback: How Do I: Use a Company Parameter in Power BI Desktop? - Microsoft Dynamics NAV Community

  2. Pingback: How Do I: Use a Company Parameter in Power BI Desktop? - think about IT - Dynamics NAV Users

  3. Anthony Newell on August 19, 2016 at 06:11 said:

    “Parameters have many more advantages and in the latest update of Power BI Desktop they can now receive their values from another query.”

    Very interested in this!, can you explain?

Leave a Reply

Post Navigation