I’ve done a fair amount of research around dynamic and parameterized queries, specifically around an Excel query parameter, as in trying to pass a parameter to a SQL query in Power Query. After much trial and error, I finally have found a solution that works. Here’s the quick and easy way. This solution works in Excel – I have not attempted to pass a parameter in Power BI yet.
Please note this post assumes you are at least moderately familiar with Power Query and SQL querying. Your mileage may vary depending on your situation.
In my example, “SParameter” is the name of the parameter I am using, and it represents a store number (retail location identifier). The related field in my SQL table is [Store].
Create the parameter:
Add a table, with a single record and column.
Add this table into Power Query by selecting the table, then Date > From Table/Range. When the query editor opens, if necessary, change the data type (in my case it need it to be text).
Right click on the record and select “Drill Down”. Since you have a single record, this step will automatically create a parameter.
In the properties, I name this SParameter (you may enter the name of your choice for your parameter). Note that the name is case sensitive.
Create the SQL query:
We are not going to put a clause in referencing the parameter here (see “Why Not Include the Parameter in the SQL Statement” section below for why). If you have other criteria for the statement, include that. Here’s my simple query – note that I’m not including the store field.
Select * FROM Storelist WHERE OpenDate < GetDate()-365
If you think like me, you may be pausing here – my table has millions of records and I don’t want them all pulling in – don’t worry, this is the beauty of the process.
Write your SQL statement.
When the Query Editor returns the columns (and/or record sampling), for the field you will be using your parameter, filter with any single value (Text Filters > Equals) – this is just a placeholder.
You will now see that filtering in the formula bar. Replace that value with the name of your parameter, removing any quotes from the placeholder. This is how it looks for me:
= Table.SelectRows(Source, each [Store] = SParameter).
Power Query is smart enough to modify it’s native query to use the parameter, so it’s not going to pull in the millions of records and then filter after the fact. Success!
Also, if you are wondering about GetDate(), this is the SQL current system date, so in a way this is technically a dynamic parameter as well. In my case, the query will only return stores with an open date that is older than 365 days based on today’s date.
Why Not Include the Parameter in the SQL Statement?
In the SQL statement, logic would lead us to add the store number/SParameter as part of the criteria in the select statement. It may seem counter intuitive, but we cannot successfully put the parameter directly into the SQL code/query. I have tried several times, but the challenge is that you are combining data sources and at this time, this is not allowed due to the built in security (SParameter portion is local, SQL portion is external).
If you are tempted to test this yourself, the format would be Select * FROM Storelist WHERE Open < GetDate()-365 and Store = “&SParameter&”. You will likely run into this error “Formula.Firewall: Query ‘SParameter (2)’ (step ‘Filtered Rows’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”
I hope this post helped you out. If so, please leave a comment below letting me know so, and if you’d like, add what you’d like me to cover in a future post.
Feel free to share this with someone else who may find it useful. Best of luck to you!