How do I pull Excel table column values into a SQL WHERE clause using Power Query M?

Question

I have an Excel table in which the end user enters reference numbers into the "RefNum" column. I also created a "RefNum_SQL" column with a formula that automatically formats the list with commas and parentheses for the WHERE clause, in case that makes the SQL/M portions easier. We will refer to this table as the "Input" table:

RefNum  RefNum_Formatted
2123    (2123,
2456    2456,
2789    2789)

I then have a SQL query which pulls additional details tied to those reference number and loads them into a table on another sheet of the Excel workbook.

SELECT RefNum, LocationID, ShipDate
FROM database.dbo.Products (NOLOCK)
WHERE RefNum IN (2123, 2456, 2789)

The results of the query are then loaded to a table on another sheet in the workbook, which we will call the "Output" table:

RefNum  LocationID  ShipDate
2123    13321       12/3/2019
2456    16654       5/17/2019
2789    19987       8/24/2019

Can Power Query M code pull the values in the RefNum column directly into the WHERE clause of the SQL query, so that the end user does not have to manually adjust queries in the Advanced Editor as the list changes?

Here is the code I attempted based on the resources I have found thus far:

let
  Source = Sql.Database("server", "database")
  RefNum_Formatted = table.Column(Input, RefNum_Formatted)
  Output = Value.NativeQuery( Source,
    "SELECT RefNum, LocationID, ShipDate
     FROM database.dbo.Products (NOLOCK)
     WHERE RefNum = @RefNum_Formatted")
in
  Output

When attempting to run the above M code, I receive the following error:

DataSource.Error: Microsoft SQL: Must declare the scalar variable "@RefNum_Formatted".

I do understand that as it's currently written, Power Query expects @RefNum_Formatted to be a declared variable in SQL as opposed to one from an Excel table; however, I cannot figure out how to create a connection to the "Input" table values that can be pulled into the query using M. Can this be done?

Similar questions are asked in the links below but I have not been able to apply any of the answers successfully:

Power Query to Filter a SQL view based on an Excel column list

Using Excel Columns in SQL Query for Where Clause

https://techcommunity.microsoft.com/t5/excel/create-dynamic-power-query-sql-by-using-excel-table-column/m-p/211504

@GordonLinoff That would work for the SQL itself; however, the issue I am currently trying to solve for is exactly how to get values from an Excel column to be stored as a parameter to then be referenced in the SQL portion of the Power Query M code. — iopa

Stachu Stachu · Accepted Answer · 2020-01-13T18:55:44

there are few things incorrect here

"@RefNum_Formatted" is just that a string not a reference to previous step
RefNum_Formatted step is most likely a list rather than a string

try this code (you may need to adjust the table/column references)

let
  Source = Sql.Database("server", "database"),
  RefNum_Formatted = List.Accumulate(Input[RefNum_Formatted],"",(state, current) => state & current),  //List.Accumulate converts list to string
  SQL_Code = "SELECT RefNum, LocationID, ShipDate
     FROM database.dbo.Products (NOLOCK)
     WHERE RefNum IN " & RefNum_Formatted,  //that's how you pass a reference to a step, it cannot be done inside a string
  Output = Value.NativeQuery( Source, SQL_Code)
in
  Output

EDIT: added Source step

How do I pull Excel table column values into a SQL WHERE clause using Power Query M?

1 Answers