none
passing Table content as multiple values into stored procedure from Powerquery RRS feed

  • Question

  • I have a sheet with one column which holds multiple rows dynamically as parameters to the next sheet with the powerquery. The powerquery will execute a stored proc. Any suggestion to write the m code will be appreciated. Thanks
    Monday, November 23, 2015 7:40 AM

Answers

  • The same approach should work for any number of parameters. You construct a table with columns holding the values for each different parameter you want to pass. You then substitute each fixed parameter value in the SP with the corresponding value in the parameter table.
    Tuesday, November 24, 2015 1:16 AM

All replies

  • IMHO, in order to get an answer, you should provide more concrete details about your case. Example of the column data, the result that you expect...etc. Something that someone can try to reproduce and use for building a solution.

    This said, with columns containing tables items, I have often used the statement Table.TransformColumns to modify each element. 

    Monday, November 23, 2015 9:58 AM
  • Thanks for the reply Colin but it is only good for a single value. I'm after ways to have multiple values passed to the SP
    Monday, November 23, 2015 9:41 PM
  • The same approach should work for any number of parameters. You construct a table with columns holding the values for each different parameter you want to pass. You then substitute each fixed parameter value in the SP with the corresponding value in the parameter table.
    Tuesday, November 24, 2015 1:16 AM
  • I'm doing something simualr, I want to use the entire contents of a column from a table (UPCs in this case) to use as a in an SQL WHERE IN statement.  So:

    InVariable=Text.Combine(#"Kept First Rows"[UPC Long], "','")

    Where #"Kept First Rows" is the name of the table in M

    This produces a text string that I can use in the IN statement

    0002250600200','0002250600205','0002250600206

    Then the SQL line looks like this:

    Sql.Database("sqldb", "dw", [Query="SELECT [Dt],[ConsumerUPC],sum([Sales]) AS Sales FROM [WeeklyRollups] WHERE [Dt]='2016-01-09' AND [ConsumerUPC] IN ('"& InVariable &"') GROUP BY [Dt],[ConsumerUPC]"])

    Friday, May 13, 2016 1:01 PM