How To Use Query Parameters In PowerBI Data Sources
I recently discovered the magic of Query Parameters when looking for a way to make Excel the data entry tool for a disconnected user scenario. But there was still one obstacle: how to make it easy to update the source location without causing chaos. Cue Query Parameters, a solution for simplifying the maintenance and migration of your PowerBI solutions.
Using PowerBI Query Parameters, you can enter many values, such as Development, QA, and Production, and then switch to the correct value for the final deployment without changing each data query.
Using Query Parameters in Flat File Data Sources
When you import Excel data into the Power BI Model, you can edit the queries in PowerBI. In this example, the following Excel File is used as the data source.
These tabs held tables with the same names used as sources.
Select Edit Query, Advanced Editor, and highlight one of the queries. The screenshot below shows the section of the query that specifies the source file’s location.
Original Query
Copy the file location; in this sample, it is the following, \Mac\Dropbox\Apps\Ulysses\IP Reuse\Consultant UBI CBI Log\Version2. Once you have this value, select the Manage Parameters toolbar option.
Setup Parameters
The following steps will set up options for the query parameter used in the source connection.
Select New to start the process.
Enter a name for the parameter without spaces or special characters. Again, it’s not a requirement, but I find that it works best, especially when trying to work with multiple parameters in your solution.
Enter a description, again recommending making maintenance a better experience for others on your team.
There are many types; Text is the one for this case. The following capture shows the options.
5. There are three choices for Suggested Values; we are selecting a List of values for this example. This can allow you to have multiple values to choose from as you migrate from environment to environment.
6. Paste the file Location that we copied from the Query Editor earlier in the process.
7. From the Default Value drop-down, select the value we just pasted in.
8. From the Current Value drop-down, select the value we just pasted in.
9. Hit OK to finish the setup.
Now that you have the parameter configuration add the parameter to the query. The screen capture below shows the final version. Make sure you have the “#” at the beginning.
Final Version
What if There is an Error?
Hitting Save will record your changes. If all is good, you should not see any Triangles indicating a query error, as shown in the image below. I removed the last slash from the file location to produce this error. If you do make a mistake, re-edit the query. Your error will disappear once a correct value is entered, and the table icon will return.
Error Sample
Now that you have a parameter, you can change all the file locations in your queries, reusing the same parameter and process. As you move the source file to different places, you only need to change the parameter and not all the queries.
Query Parameters in Server Data Sources
Query Parameters can also be used in server data sources, which is more straightforward, as shown in the picture below. You create the parameter the same way as above. However, you select the parameter from a drop-down when you choose the server connectivity options.
Server Connection via Parameter
Resources
There are many good resources for Query Parameters. The next level is using query parameters for dynamic reporting.
PowerBI Docs – Deep Dive into Query Parameters and Power BI Templates
Guy-In-a-Cube – Dynamic Power BI reports using Parameters
Here are some more references you may find helpful;
