When developing Power BI reports for projects, you will find that switching over data sources from development to production can often be tedious if there are a lot of queries. Parameters in Power BI allow for so many different possibilities, especially within the optimization of tasks like data source migration. In this blog post, we will walk through how to use parameters in Power Query to help make migrating data sources much easier.
What can Parameters on your Data Sources Help Accomplish?
One of the capabilities that it allows for is being able to switch between the different sources simultaneously. This is something that not only optimizes the experience for developers but also helps to be able to quickly shift back and forth from development to staging to production while performing user acceptance testing (UAT). In the situation where your reports are already published and were not set up using a deployment pipeline, parameters can also be a quick way to remedy this while keeping all of your reports in a single workspace.
How to Setup Data Source Parameters in Power BI
In the example below, you can see that there are 17 queries in our report. Let’s walk through how we can switch over these queries to be controlled by a parameter. To set up data source parameters, you must first go to the “Power Query Editor” and click on the “Manage Parameters” dropdown and select “New Parameter”.
Now, enter in a suitable name for your Parameter such as “Server” and change the suggested values to “List of values.” Once “List of values” is selected, define your servers using the current names of the servers you connect to. For instance, if you connect to “Prod-east…” Enter the entire server name into the list of values to indicate which servers you are going to swap between. For this example, “Dev, Stg, and Prod” are only there to signify the different servers you should be adding to your list. *The type has to be text, as the Power BI Service will not allow you to modify parameters that have a type of “any.”*
After these steps are completed, select your first query and go to source, then click on the “⚙️” icon and change your Server and Database to your previously defined parameters. Repeat this process for all of your queries that you would like to have under the parameters.
Once you have them all swapped over you can simply change the parameters to change all of the sources for your queries instead of having to manually go back and forth between two different sources as shown below:
Once your reports have been published and you are the owner of the dataset, you can go in to change the parameters on the service as well, which allows you to swap between sources in the service without having to re-publish. To do so, you will need to go into the dataset settings, navigate to parameters, and type in your desired value. After doing so, you can refresh your dataset to bring in the data from the server/database you have pointed the dataset to through the parameters.
Closing
By allowing you to change the sources on your queries within the Power BI Service or through the click of a button as opposed to manually switching multiple sources over, parameterizing your data sources can lead to an optimized experience on Power BI as well as allow for efficiency when performing user acceptance testing (UAT). You can also take it one step further by having a table that is controlled by your parameters to show your end-user which environment they are currently on when viewing to report to ensure accuracy in reporting.
