1

I've seen examples (and coded one) that parameterize a PowerQuery SQL statement with values read from cells in the spreadsheet'a table. But these always deal with simple logical conditionals like myField = <parameter>. I want to parameterize a IN clause (myField IN <values>), where an arbitrary number of values are read from an Excel table (also in the same file). Does anyone know how to do this?

1

ugly, but it seems to work.

  1. create PQ query to capture single column 'parameterValue' table of desired parameter values
  2. add dummy column and group all rows by dummy
  3. use Text.Combine to create parameter list and remove other columns
  4. drill down to cell value
  5. create desired sql query without IN clause (use 'select top 1...' for efficiency)
  6. using Advanced Editor, modify sql query delete 'top 1' and accept query from step 1 as IN parameter

query1:

let
    source = *yourData*,
    addDummy = Table.AddColumn(source, "Custom", each 1),
    group = Table.Group(addDummy, {"Custom"}, {{"Count", each _, type table}}),
    string = Table.AddColumn(group, "queryString", each Text.Combine([Count][parameterValue],"','")),
    removeOtherColumns = Table.SelectColumns(string,{"queryString"}),
    queryString = removeOtherColumns{0}[queryString]
in
    queryString

then

query2:

let
    Source = Sql.Database("*serverName*", "*dbName*", [Query=
Text.Replace("
select * from x
where parameterValue in('" & query1 & ")", ")", "')" )
])
in 
Source
0

Ken Puls has a good write up about using a parameter table with Power Query in his blog here: http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

Executive summary: Build a table for the parameters, write a function to read a specified parameter, use the function in the M code to dynamically apply the desired parameter.

You may need to extend this approach to build your SQL statement as a string.

  • as I mentioned in my question, I already know how to get single parameters (I even included a link to an example). And yes, I did see the comment about WHERE IN. That's still not what I'm asking about. What I want to do is read an array of values from a table. – Dmitry B. Mar 29 '16 at 22:51
0

I would first build a Power Query e.g. "Values" to read in the Excel Table rows. I would set the Load To = Connection Only.

Then in the main SQL-based Query I would Merge with that new "Values" query, and set the Join Kind = Inner.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.