Dynamic cell range passed as SQL parameters in Power Query

[Kazlik]

I current have a working file with a table the users enters in a CategoryID that I then pass to a SQL query using Power Query. I would like to expand this for another use case but this time I want the user to enter in one or many rows of data and then pass all of the rows they enter into IN logic in my SQL.

//Current I use this
Team_parameter = Excel.CurrentWorkbook(){[Name="FullTextInputs"]}[Content],
SelectedCategoryID = Team_parameter[CategoryID]{0},
//And pass it into the SQL
team_id = " & Number.ToText(SelectedCategoryID) & "#(lf)

If I had a table like this what is the best way to get the 3 rows they entered comma separated.

Product
1
4
5


team_id IN ( " & Number.ToText(SelectedProduct) & ")

 

[Matt Allington]

Well the best way I can think of is to create 2 queries and then merge them inside power query and select the option to keep matching rows only.

 

[billszysz]

Hi Kazlik
Try something like this (If I understood correctly your problem)

let
    Source = Excel.CurrentWorkbook(){[Name="tblParameters"]}[Content],
    Id = Text.Combine(Table.TransformColumnTypes(Source,{{"Product", type text}})[Product], ",")
in
    Id

Result will be "1,4,5".... Is that what you need?

 

[Kazlik]

Thanks Bill that looks like it will work for me.

 

[albertan]

Hi Kazlik
Try something like this (If I understood correctly your problem)

let
    Source = Excel.CurrentWorkbook(){[Name="tblParameters"]}[Content],
    Id = Text.Combine(Table.TransformColumnTypes(Source,{{"Product", type text}})[Product], ",")
in
    Id

Result will be "1,4,5".... Is that what you need?

Hello Gents,

I really liked the idea of having a range for Parameter. But in my case for some reason the query still filters for one parameter only. What is the best way for me to ensure I can get two or more line parameter to be passed to a query? thank you so much