Dynamic Data Source in Power BI

Dynamic Data Source in Power BI

Power BI is a very powerful tool, and we can do wonders with it, but when it comes to changing the source, most of us are afraid of it. This is because by doing so, we may need to make many changes related to a data model, DAX, visualizations, and other functionality. But there is a way to avoid such things, and we can do it by using the Power Query Parameters.

In this blog, I will explain how we can change data sources dynamically without a change in Power BI files and republish them using the parameter.

Let’s see the scenario. Suppose we have the same structure table in DEV, QA, and PRD environments (server or database). We developed a report in the development environment and want to move from one domain to another. We can do that by creating a power query parameter.

Below we have the Employee table in three different databases in SQL Server.

The critical thing to remember here is that the table name, column name, and data type must be the same in all environments.

Let’s see how it can be done by following the steps.

Step 1 – Connect to SQL Server and get Employee Table Data from DEV Database.

Created some basic visualizations in Power BI as below-

Step 2: Create a Parameter in Power Query Editor for Environment Definition.

Here we must give the parameter name, the data type of the value, and provide a list of environments (Database) values shown below screenshot. Note that we must enter precisely the same name as the name of the databases.

Unleash the Potential of Power Platform With a Center of Excellence

Business innovation often comes from within. Discover how to empower innovation from non-traditional developers with the Microsoft Power Platform.

Learn More

Step 3: Connecting Parameters to the Source Table

Now we have to configure the parameter in the table source setting, which we want to change dynamically. we can do this in two ways

·        Configuring in M code: In the query editor, we have to go to the advance editor for the table, which we want dynamically to change; there, we can see M code for the table.

In the source, replace the “DEV” with to name of the parameter which we have created in Step 2

Configuring in Source setting in APPLIED STEPS: Click on the settings icon for the Source step as shown below in the Power Query Editor.

The Database dropdown has the “Text” default selected option in the popup that appeared. Select the “Parameter” option from the dropdown, choose the name of the parameter created in Step 2, and click OK. Now parameter is successfully configured with the source table.

Step 4: Changing the parameter value

we can change the parameter value in the Power BI Desktop and Power BI Service. In Power BI Desktop, we can change the parameter value in transform data -> edit parameter, then select parameter value.

After changing the parameter value, click “Apply Changes” to refresh the dataset and see changes in the report.

After changing the parameter value from DEV to PRD, as we can see now, we are getting data from the PRD database, and values have changed.

To change the parameter value from the Power BI service, we must go to dataset setting -> Parameters. On the Parameter tab, we can change the parameter value then click on Apply.

We have successfully changed the data source. Now the report dataset is pointing to the QA database. So, from Power BI Service, we can change the source without any change in the Power BI file and publish it again.

Conditions and Limitations:

·       Table name, column name, and data type must be the same in all environments.

·        We need to configure the gateway connection for all environments if our data is on-premises to refresh the dataset in the Power BI service.

·        This solution needs dataset refresh after changing parameter value.

·        This solution is not for the end user because the user must have dataset access to change the parameter value and refresh the dataset.

Conclusion:

This is a very useful method when we have the same table (data structure, not data) in a different environment, different database, or different folder. With the help of the parameter, we can change the source. This method is very useful for the deployment of reports. Also, we can change the Source type dynamically, for example, Excel to SQL, one relational database to another, SharePoint folder to excel etc. which I will explain in the next blog.