How To Use Query Parameters In PowerBI Flat File Data Sources
I needed a PowerBI solution that could be used while the user was disconnected, independently from other users. Using Excel as the data entry tool solved a lot of problems, except one. How do I make it easy for the end user to move the source around while making it easy to update the source location? Query Parameters solved that issue making the solution a lot easier to maintain. Using this feature can make any development and implementation scenario easier by allowing for easier migrations.
When you have many data queries, each will point to the file’s source location, even if you change the location of the source file. Using PowerBI Query Parameters, you can enter many values, let’s say, Development, QA, 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.
Selecting Edit Query, Advanced Editor and highlight one of the queries. The screenshot below shows the section of the query that specifies the location of the source file.
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. Not a requirement, but I find that it works best, especially when you are trying to work with multiple parameters in your solution.
- Enter a description, again, recommended 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 3 choices for Suggested Values, we are selecting 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, simply re-edit the query. Once a correct value is entered, your error will disappear, 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 just 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
