Change the Source of Power BI Datasets Dynamically Using Power Query Parameters

Parameters in Power Query are useful for many scenarios when you want to do something dynamic in the data transformation process. I have previously explained how helpful they are in creating a custom function. In this article, I am showing you another useful way of using Parameters to create dynamic datasets, that you can change the source, or anything else using it instead of opening your Power BI file each time, and republish. To learn more about Power BI, read Power BI book from Rookie to Rock Star.

Parameters in Power Query

Parameters in Power Query are a useful way to change values dynamically in your Get Data and Transform process. Parameters can be used to change values without opening the Power Query (Transform Data) window in the Power BI Desktop, and they are helpful even in the Power BI Service in a way that you can change values manually without the need to open PBIX file in the Desktop and re-publish it.

Parameters in Power Query can be used in many different scenarios. One of the most common examples of using parameters is to use it for creating custom functions. Let’s in this article focus on one of the challenges that can be easily resolved with parameters.

Changing Data Source Dynamically

Imagine a scenario like this:

You have connected to a data source using Power BI. That data source can be anything (a SQL Server or Oracle database, a folder, a file, a web API address or etc). You created your Power BI report, and then published the file to the service, and now you want to change the data source of the same type. However, the new data source is exactly similar to the old one in terms of structure. So all you need to do is just change part of the source connection (the database name, or the file name, or folder path, or the API URL etc). You can do these with changing values in Power Query Editor window. but that means you need to open the file in Power BI Desktop, change the value, save and re-publish it into the service. To avoid these extra steps, you can use Parameters. Let me walk you through it.

The process below is explained on a database source, but the same process can be used for ANY types of data sources with slight modifications.

Getting Data from SQL Server Database

I have a Power BI report reading data from a SQL Server database called AdventureWorksDW2012.

I have select FactInternetSales from this database, and showed the count of rows from this table in a visual in Power BI report:

Making the database name Parametric

I have a similar database to this one under the same server, but with a different name, which has a FactInternetSales table under it, but much bigger version of it:

I build my report on the AdventureWorksDW2012 which is much smaller (or you might do it on your DEV database server to make the process of development faster). but after publishing it, I want the report to be plugged into the AdventureWorksDW2017BIG database and get data from there. So I will use parameters like below.

Create the parameter for the database name

The first step is to create the parameter under Power Query Editor window (this parameter is different from What IF parameters in Power BI Desktop)

Then set the parameter name, data type and the default value:

Using the parameter as the source database

Now you can go to the query that you want the source of that to be dynamically changing, click on the source step to set the database name.

You can then change the database to come from a parameter

And then set the parameter you have created in the previous step there. That’s it all done. Easy and fast.

Bonus Step: Showing the database name in the report

If you also like to show the database name in the report page, you can create a new blank query,

Write the expression below in the formula bar:

The “Database” in the above script is my parameter name. if your parameter name has space or other special characters in it, then you might try this way: #”Database name”. For more information, read my article about the basics of M scripting.

Now you have a new query with the value of this parameter in it.

You can then use this value in a card visual in Power BI report like below example:

Changing the value of parameters from Power BI Desktop

If you ever want to change the value of parameters in Power BI Desktop, you don’t need to open Power Query Editor for that, you can change it easily with clicking on Transform Data and Edit Parameters

Please note that after changing the parameter value, you have to click on “Apply Changes” to get the dataset refreshed and see the changes applied.

Changing the value of parameters in the Power BI Service

After publishing your Power BI file to the service, you can go to the dataset settings (under schedule refresh)

You can then expand the Parameters section under dataset setting page, and change the value:

Extra Step: Sometimes you might need to set up Gateway connection too

If your data source is an on-premises data source, then you need to set up the gateway configuration for that.

Testing the Result

Now you can either refresh the Power BI dataset in the service manually or schedule it to refresh. Either way, you will get your report connected to a different data source without editing your Power BI file!

Summary

The method I showed here is useful in many scenarios. When you have the same data structure of the same data source type, but on a different server, different database, folder, file, API URL or etc. You can change that value easily with parameters without the need to edit your Power BI file.

This method is also used a lot for scenarios of changing connections from DEV, UAT, TEST, Prod environment without the need to make a lot of changes in the source code.

Related posts:

Power Query Parameters Vs. What If Parameters: Power BI Implementation Use Cases Meetup Data Source for Power BI Get List of Queries in Power BI Creating a Shared Dimension in Power BI Using Power Query: Basics and Foundations of Modeling

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.