rank in Access

Solved: Pass a Parameter to a SQL Query in Power Query

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 to do this and harness the power of query folding.  This solution works in Excel – the process is a bit different in Power BI.

Please note that 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.  This can be on any tab in your workbook.
  • 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).
  • 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.

Create the SQL query:

Build your SQL query as normal, but note that we are not going to put a where clause in referencing the new parameter here (see “Why Not Include the Parameter in the SQL Statement” section below).  Here’s my simple query example – notice that I’m not including the store field in the criteria section of the query though this is the field my parameter will ultimately filter on.

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 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!
  • Side note – 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.  You may find this useful for your query as well.

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 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.

Please bookmark and subscribe to my blog!  I am always curating and adding new, relevant content! Thanks so much!

Also, be sure to check out Dose for Excel (click image below)!  Add over 100 functions to Excel to increase your productivity and more!  They have a free trial right now, so you can try it out today!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in.  Thanks for supporting my blog!

Dose for Excel - +100 Functions

Power Query Filter Rows by NOT Contains Criteria – Single Formula Solution

Learn how to filter a table based on NOT Contains Criteria. Download an example workbook here.

Easy step by step instructions below.  See a single formula solution using the functions Splitter.SplitByAnyDelimiter, List.Count and Table.SelectRows. Amazing formula solution from Power Query Poet, Bill Szysz.

Please watch the video if you’d like for a guided walkthrough and also another method that can be useful for multiple tables.  

Criteria Table

  1. Create a new, separate table with the list of terms you will want to exclude. Name the table “NoCriteria”.
  2. Add the excluded item (NoCriteria) table to Power Query – click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section.
  3. In the Power Query window, select the Transform menu and click convert to list.
  4. Under the File menu, choose Close and Load To, then choose Connection Only.

Building the Filtering via Power Query

  1. Add or create the list/table that will ultimately be filtered.
  2. Click any cell within the table that will be filtered. Add the excluded item table to Power Query (click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section).
  3. In the Power Query window, click the Add Column menu, and select Custom Column.
  4. In the window that opens, type this:

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))=1)

  5. Change [PRODUCT_NAME] in that text to your own column in the table that you will be filtering on if it is different.
  6. Select the statement you have typed in and copy it (you will need to paste this formula in a following step).
  7. Click OK. You will see that the formula you typed was changed by the program and a column was added.
  8. To change the formula back, click the menu bar, highlight the entire text and then replace by pasting in the formula you copied. Hit enter.
  9. The extra column should be removed and the table should be filtered on you criteria from the NoCriteria table.
  10. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

Any time changes are made to the exclusion list, you will need to refresh the filtered table. Simply right click any cell within the filtered table, and select Refresh.

 

Bonus – Filtered table with the excluded items only (not shown in video)

You can additionally create a filtered table that only includes the terms in your NoCriteria table!  

  1. To do this, go into Power Query.  Right click on your filtered table and click Duplicate.
  2. In that new table, you will very slightly change the existing formula in your Power Query to not equal one (see orange text):

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))<>1)

  3. Hit enter.  The table should now only filter on the items in your exclusion list, instead of including them.
  4. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

View on YouTube

Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula

Download Excel Finished Files: http://bit.ly/2K9SNfn
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to Average a customers last three sales (last three dates) using Excel Worksheet Formulas, Power Query M Code and a Power Pivot & Power BI DAX Measure.
Topics:
1. (00:07) Introduction
2. (00:50) Sort Method
3. (02:09) Excel Worksheet Formulas, including AGGREGATE and AVERAGEIFS functions.
4. (05:27) LARGE Function and New Excel Office 365 Calculation Engine
5. (08:15) Power Query M Code, including Table.Sort, List.Average and List.FirstN M Code functions
6. (12:46) Power Query Parameter Query with condition coming from Excel Worksheet
7. (14:05) Power Pivot & Power BI DAX Measure, including TOPN, CALCULATE and AVERAGE DAX Functions
8. (17:08) DAX Parameter from Excel Worksheet using VALUES DAX Function.
9. (18:43) Add new records to table and test formulas
10. (19:00) Fix Power Query Table.Sort bug using Dummy Insert Column
11. (20:08) Fix Power Query Table.Sort bug using Table.Sort function inside Table.Group Function
12. (22:12) Conclusion

View on YouTube