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:
|
1 |
=Database |
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:
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.
Does this work using dataflow ?
Yes, You can use parameters in dataflows too.
Cheers
Reza
Hi reza rad,
Thanks for your post. we have a requirement as below. not sure if it is doable or not.
I have a slicer called “environment” (prod/test/ dev) . all three environments have same schema. can we change the environment based on selected value in slicer? means instead of go and edit parameters, parameter values should be chosen based on the selected value in the slicer.
This is not easily possible. The challenge is as below:
Slicer in Power BI affects the data AFTER loading data to Power BI. That means you have to import data from all the three environments (Dev, test, Prod), and then use the slicer to filter the data only for one environment.
I don’t think, however, the method above is what you are after.
If not, then changing it through Edit Parameters in Power BI Desktop, or through the website and then refresh is the other option
Cheers
Reza
Hi Reza
Can you switch Server Connections in the same manner ?
So instead of switching between databases switch between different environments for the same database. For e.g. if i want to report against reportserver or SSISDB catalog for different environment DEV, QA, PROD.
Sachin
Hi Sachin
Yes, you can, but apart from the server address and database name, what else you want to change in different environments?
Cheers
Reza
In the database-source window I have no option to change de database-source to Parameter. There is no dropdown list available next to the Database (optional) field. What am I missing?
You should create the Parameter first. have you done that step? Power Query Parameter
Cheers
Reza
I figured out that in Options you have to select Always allow parameterization in data source and transformation dialogs on the Power Query Editor tab before it is possible to use the parameter as database-source.
Hi Jan.
Yes, if you use that option, then you can even create the parameter from any window necessary. otherwise, you have to first create the parameter, and then see it in the drop-down.
Cheers
Reza
Does this work when the schema (i.e. columns) and data is different across data sources
if you have different schema and columns, then you have to change the Power Query steps too, or do the transformations in a way that column changes are not affecting it that much, which means some scripting as well.
Cheers
Reza
Hi,
Can a User with viewer role in power bi service can change the parameters? And can we pass parameter at run time by using power bi slicer/
Thanks
Hi
The viewer’s role in the workspace, cannot make any changes in the dataset.
However, my better answer would be; why you want to use dataset parameters for the user interaction? if you want the user to interact with parameters, you need to use What-if parameters. read my article about the difference between the two.
Cheers
Reza
Hi. For compliance reasons i have two SQL databases – one with pre-checked data and the other post-checked or validated data. The underlying databases are exactly the same structurally and once checked the data in both is the same..I therefore have two PBI datasets both exactly the same but which point to the differently named SQL databases – and so have twice the memory requirements. Ideally I would like to have just one PBI dataset which would use another condition (eg a true false flag) to dynamically determine which database to use as the parameter setup. Is it possible to create db connection parameters in the dataset power query which allows the source database to be determined dynamically ?
Thanks
You can use Power Query parameters to change the database source, similar to the approach I explained in this article.
However, changing those values has to be done in the dataset settings in the Power BI service, not inside the Power BI report.
Cheers
Reza
Thanks for your prompt reply. Yes, this would be added to the dataset. However, to confirm my understanding of the approach in this article you are manually updating the “Database” parameter by manually selecting a value once it’s defined ehic requires opening the dataset.
What i want to do is is have the database_name selected automatically based on the value of another (source) database field
eg : if table[fieldname] = true then
parameter_databasename = databasename_postrelease
else
parameter_databasename = databasename_prerelease
Hi Serge
It is possible to build that kind of logic because reading from a source is also a line of transformation in Power Query, which can be placed within your IF/THEN/ELSE logic
However, the parameterization of the data source itself can be sometimes tricky. Not all data sources are supporting that in the Power BI service.
Cheers
Reza
Hi Reza,
Can you create a parameter to a database as a placeholder (in case the data base doesn’t exist yet)?
We are planning to move to Azure in the future and would like to be able to use this strategy for our PBI reports. So if we already now can create a parameter called something like “AzureDBxxx” and later fill in the right details it would be quit cool.
Regards
Jakob
You can.
However, the credentials for that should be entered in advance (means that the server should actually exists so that you can enter credentials and pass that stage). and it might not be also supported depends on the type of data source in the scheduled refresh of Power BI service.
Cheers
Reza