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. Here’s the very quick and easy way to do this!
This exact solution works in Excel. Creating a dynamic SQL query with a parameter in Excel allows for amazing flexibility. The use cases are endless and can benefit so many types of Excel work across industries – if you are querying any data source, such as a database, even an Access database, then parameterized querying in Excel can truly simplify your workflow. I am going to cover SQL queries specifically, but please comment below if you would like a tutorial on using a parameter with other data sources. Nearly anything is possible!
You can also easily use parameters in Power BI, but the process for creating the parameter itself is slightly different – I will cover that in a future post. The query construction is the same in most cases though. Please note that this post assumes you are at least moderately familiar with SQL querying and Power Query. Your mileage may vary depending on your situation.
If you found this post because you are encountering a firewall error when incorporating a parameter, skip right on to that section below (click here) for the solution. After much trial and error, I have found a way that works to overcome this issue harnessing the power of query folding.
The Solution: Passing a Parameter to a SQL Query
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:
- In any worksheet, add a new table, with a single record and column. I highly recommend the Header clearly clarifies what the parameter is so there is no confusion in identifying it.
- Add this table into Power Query by selecting the table, then Data > From Table/Range.
- When the query editor opens, if necessary, change the data type (in my case I need it to be text). This is important!
- Right click on the record and select “Drill Down”. Since you have only 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.
This is how the table is situated in my worksheet (1 is the parameter/the value I will change as needed):
This is how it looks in Power Query after all steps above were completed (I named the column from Enter Store: to Store to make it cleaner (this is an optional step):
Open or Add your SQL Server Query in Power Query
Add your query into the Advanced Editor.
let
Source = Sql.Database(“your connection name”, “your database name”, [Query=”
your query goes here
“])
Create the SQL query with your parameter:
Here’s my simple query example to demonstrate how to build the query with the parameter. Please substitute your table name and fields, as well as your parameter name.
With the parameter in the WHERE clause
“Select * FROM StoreDetail.Store WHERE Store =” &SParameter&”“
- Simply build your unique SQL statement in Power Query following my example above, adding your parameter into the query with quotes and the & (ampersand) sign surrounding the parameter name.
- Check the results and modify as needed.
- If your query has no additional clauses or criteria, simply put quotes and one & sign ahead of the parameter, and then one after followed by two double quotes to close the query statement.
Additional criteria in the WHERE clause
Here is an example where I have additional criteria in mine, filtering on the OpenDate field; after the second quote, add a space and then continue on with the rest of your query.
Select * FROM Storelist WHERE Store =” & SParameter &” AND OpenDate < GetDate()-365“
This is an example of the properly formatted parameter looks in the Advanced Editor in Power Query (I have built out my query further with the Group By clause – this is optional if it doesn’t apply in your case).
If you are running into issues with the query, you may need to add in single quotes surrounding the parameter (inside of the double quotes – “WHERE Store =‘” & SParameter & “‘;”), or possibly # signs surrounding your parameter inside of the double quotes, if your parameter is a date. You may have to test with and without these depending on your unique scenario.
If the query is successful, you can proceed with saving it or adding in additional steps in Power Query if you are further filtering or transforming the resulting data you pulled.
BONUS Information – if you are wondering what GetDate() is, this is the MS 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. You may find this useful for your query as well if you are looking for dynamic dating.
After updating your parameter in the worksheet table, Select Refresh All from the Data menu and your updated query will run and return the results accordingly!
Managing Privacy Levels in Power Query/Excel
VERY IMPORTANT: If you are sharing your workbook with others, you may need to edit the Privacy Levels in order for the queries with parameters to work for them. Read on for these important steps.
In Excel Power Query, there are security measures in place to help prevent data from being shared unintentionally. When combining query sources by using the parameter (a value in your Excel file with a SQL server source), many times this security comes into play.
If other users are having an issue refreshing the query/queries where you have added a parameter into the SQL query, please have them follow these steps. I actually copy and paste this right into an Instructions tab of each of the shared workbooks containing parameters that I create for my clients and for colleagues.
- Go to Data, click Queries & Connections.
- Your query list will show up in the panel on the right.
- Right click any query in the list and choose Edit.
- A window will open. Click File, then Options and settings.
- Choose Query Options.
- Click Privacy, which is on the menu on the left, at the bottom.
- Select the radio button for the Ignore the Privacy Levels and potentially improve performance option.
- Click OK to save.
- Click Close & Load in the Power Query Editor window (at the top) to exit the settings.
Troubleshooting/Alternate Method – Firewall issue
If you run into this dreaded 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.”, then you will need to use this alternate method.
If you received the firewall error above, you can still use your parameter, but you will instead need to use the parameter as a filter in a subsequent step. Now you may be possibly thinking – my table has thousands (or even millions) of records and I don’t want them all pulling in – don’t worry, this is the beauty of the query folding process when it works properly in Power Query.
Without the parameter in the WHERE clause
Select * FROM StoreDetail.Store WHERE OpenDate < GetDate()-365
- Write your SQL statement in Power Query as noted above.
- When the Query Editor returns the columns and record sampling, for the field you will be using your parameter, filter with any single value (Text Filters > Equals). This is simply a placeholder, to create the Power Query M formula for the next step.
- You will now see that filtering in the formula bar.
- Replace the placeholder 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 its 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!! This is the power of Query Folding!
Why Can’t You Include the Parameter in the SQL Statement?
In the SQL statement, in most cases we will want add the store number/SParameter as part of the criteria in the select statement. It may seem counter intuitive, but we cannot always successfully put the parameter directly into the SQL code/query. 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). Unfortunately some organizations have policies that disallow this. Sometimes it is an Excel version issue.
I truly hope this post helped you out. If so, please feel free to 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. Also, feel free to share this with someone else who may find it useful.
Do You Need Personalized Help and Custom Solutions?
If you get stuck or you would like to explore solutions and automation possibilities, please can reach out to me for help as I do offer consulting services as time allows. I have over 20+ years’ of expert level experience delivering excellent, custom, strategic solutions in Excel, BI, Access, SharePoint and more.
I have been called a guru and hero more times than I can count, as I am a great listener and truly have a knack for asking the right questions to understand unique business challenges. I am very passionate about crafting tools and processes that work for users of all levels and experience.
Reach out today and let’s discuss how I can help you and your business!
I also offer one-on-one tutoring for customized learning and upskilling. Visit my consulting page and send a message if you are interested.
Other Excel Resources
Also, consider checking out some great Excel resources on Amazon! Disclosure: this is an affiliate link, so I may earn a small commission if you decide to make a purchase, which will help cover the cost of hosting this website.
Please bookmark and subscribe! I am actively working on adding new, relevant content to help others out! Thanks so much!
Subscribe via Email and Stay Up to Date!
Buy Me A Coffee/Support the Blog 🙂
If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!