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!
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.