How To Use Query Parameters In PowerBI Flat File 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, say, Development, QA, and Production, 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 have the Excel data imported into the Power BI Model, the queries in PowerBI can be edited. In this example, the following Excel File is used as the data source.
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.
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.
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.
- 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.
- Paste the file Location that we copied from the Query Editor earlier in the process.
- From the Default Value drop-down, select the value we just pasted in.
- From the Current Value drop-down, select the value we just pasted in.
- 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.
What if There is an Error?
Hitting Save will record your changes. If all is good, you should not see any Triangles which specify you have 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 have a mistake, re-edit the query. Your error will disappear once a correct value is entered, and the table icon will return.
Now that you have a parameter, you can change all the file locations in your queries, reusing the same parameter and the same process. As you move the source file to different places, you only need to change the parameter and not all of 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.
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
Some more references you may find useful;
