Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Pass List Value through SQL Statement without Parameter

Hi,   I am using a parameter to filter a table in Query Editor - the parameter is filtering the table based on an account name. This table will return one single row of information.   From this s...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

Hi Anonymous,

 

I make an example for your reference.

 

1.Import an excel file to desktop add a custom column based on id column then create the parameter in power query.

 

 

 

M code in the power query is like this for step1.

 

(para as text) as table =>
let
    Source = Excel.Workbook(File.Contents("D:\Case\20180810\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "account name"}, {"Column2", "id"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([account name] = para)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each "'" &[id] &"'")
in
#"Added Custom"

 

2.Then we can add some steps in the Advanced editor.

 

(para as text) as table =>
let
    Source = Excel.Workbook(File.Contents("D:\Case\20180810\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "account name"}, {"Column2", "id"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([account name] = para)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each "'" &[id] &"'"),
    keylist= Text.Combine(#"Added Custom"[Custom],","),
    select1="SELECT * FROM servername.databasename.dbo.tablename WHERE  id IN (" & keylist & ")",
    Source1 = Sql.Database("servername ", "databasename", [Query=select1])
in
    Source1

 

3.Then we can get the excepted result once we invoke the parameter.

 

 

If you still have questions, please share sample data of your table and post SQL query here.

 

Regards,

Frank