How Do I: Use a Company Parameter in Power BI Desktop?

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.

This 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:

Then, in Manage Parameters, select Manage Parameters:

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

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:

Click Close and Apply.

Now, select Get Data, ODATA Feed:

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:

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

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

How cool is that!?

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

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

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:

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 😉