Configure Data Source Setting Parameters for a Power BI Data Model

Source:

https://blog.magnetismsolutions.com/blog/colinmaitland/2019/09/06/how-to-configure-data-source-setting-parameters-for-a-power-bi-data-model

 

The following shows a Web API URL configured in Power BI Desktop for connecting the Power BI Data Model to Dynamics 365 (Online). In this example, the Data Model is connected to the Dynamics 365 (Online) development instance for an organisation named “Acme”; i.e. “acmedev”.

Any Power Queries in the Data Model subsequently configured from this Data Source will include this Web API URL in their Source step. In the accounts Power Query, shown in the following image, you will notice that the Source step includes the name of the Dynamics 365 (Online) instance.

To connect the Data Model to another Dynamics 365 Organisation, you can simply change the Data Source Setting in Power BI Desktop as shown in the following images. Here the Dynamics 365 Organisation is being changed from “acmedev” to “acme”.

As a result, the Source step of any existing Power Queries in the Data Model that use this connection are also updated. The following image shows the updated accounts Power Query.

However, a disadvantage of this approach to configuring the Data Source Settings is that the Dynamics 365 (Online) instance the published Data Model is connected to, cannot be changed in the Power BI Service without the need to update the Data Source Settings and republish the Data Model from the Power BI Desktop.

The solution is to add Connection Parameters to the Data Model as shown in the following images. These Connection Parameters have been added using Manage Parameters from the Power Query Editor in the Power BI Desktop.

The first parameter is the prefix for the Web API URL; i.e. “https://”.

The second parameter is the Organisation for the Web API URL; e.g. “acmedev”, “acmetest” or “acme”. This is the part of the Web API URL that must be changed to connect the Data Model to a different Dynamics 365 (Online) instance.

The third parameter is the suffix for the Web API URL; i.e. “.api.crm6.dynamics.com/api/data/v9.1”.

I have prefixed each of these parameters with a number to make them easier to use. I have also configured the Suggested Values for each to be a List of Values instead of Text. This is especially helpful for the second parameter where there is a configured list of more than one Dynamics 365 Organisation to choose from.

In Power BI Desktop, the Data Source Setting for any Power Queries can now be configured as shown in the following image.

Here, when configuring the Data Source Setting, the Advanced option has been selected, Add Part has been used to add two additional parts, the option prior to each parts has been set to Parameter, and the corresponding Connection Parameter has then been selected.

Any existing or new Power BI Queries that are configured from this Data Source will now include these parameters in their Source step. The following image shows the updated accounts Power Query.

The advantage of this approach is that the Dynamics 365 (Online) Organisation that the Data Model is connected to can be changed from the Settings area for the published Data Model in the Power BI Service. In particular, you can now change the second parameter to another Dynamics 365 (Online) Organisation The Data Model will then be connected and refreshed accordingly as long as the Data Source Credentials are correctly configured.

Finally, you should consider creating and using a Power BI Template for your all your Dynamics 365 (Online) Data Models that already include these preconfigured Connection Parameters.