How to Parameterize Data Sources in Power BI

Introduction

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 this is completed, repeat the same steps and create a parameter for DB to represent the different databases you would like to connect to. In this example, the DB names are generalized again, please ensure that you are using your respective DB names.

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.

FAQs

Question: What functionality in the Power BI Service with a Premium Capacity License automates this entire process? 

Answer: Power BI Deployment Pipelines

 

Question: When are parameters not able to be used in the service? 

Answer: When the type is “Any” or “Binary” due to security reasons.

Comments are closed.