title: How do I pull Excel table column values into a SQL WHERE clause using Power Query M?
body: <p>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:</p>
  
  <pre><code>RefNum  RefNum_Formatted
  2123    (2123,
  2456    2456,
  2789    2789)
  </code></pre>
  
  <hr>
  
  <p>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.</p>
  
  <pre><code>SELECT RefNum, LocationID, ShipDate
  FROM database.dbo.Products (NOLOCK)
  WHERE RefNum IN (2123, 2456, 2789)
  </code></pre>
  
  <hr>
  
  <p>The results of the query are then loaded to a table on another sheet in the workbook, which we will call the "Output" table:</p>
  
  <pre><code>RefNum  LocationID  ShipDate
  2123    13321       12/3/2019
  2456    16654       5/17/2019
  2789    19987       8/24/2019
  </code></pre>
  
  <hr>
  
  <p><strong>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?</strong></p>
  
  <hr>
  
  <p>Here is the code I attempted based on the resources I have found thus far:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>When attempting to run the above M code, I receive the following error: </p>
  
  <blockquote>
    <p>DataSource.Error: Microsoft SQL: Must declare the scalar variable "@RefNum_Formatted".</p>
  </blockquote>
  
  <p>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?</p>
  
  <hr>
  
  <p>Similar questions are asked in the links below but I have not been able to apply any of the answers successfully:</p>
  
  <p><a href="https://stackoverflow.com/questions/55070454/power-query-to-filter-a-sql-view-based-on-an-excel-column-list">Power Query to Filter a SQL view based on an Excel column list</a></p>
  
  <p><a href="https://stackoverflow.com/questions/38078350/using-excel-columns-in-sql-query-for-where-clause">Using Excel Columns in SQL Query for Where Clause</a></p>
  
  <p><a href="https://techcommunity.microsoft.com/t5/excel/create-dynamic-power-query-sql-by-using-excel-table-column/m-p/211504" rel="nofollow noreferrer">https://techcommunity.microsoft.com/t5/excel/create-dynamic-power-query-sql-by-using-excel-table-column/m-p/211504</a></p>