Forum Discussion

schdef's avatar
schdef
Frequent Visitor
9 years ago
Solved

Parameterized SQL Query with query folding

I have a table loaded to my data model containing IDs. I want use these IDs to filter another query connecting to a SQL table. I tried to merge the SQL query with the ID table but then the query does...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi schdef,

     

    You can tansfrom your data to text, then use it into sql query.

     

    Sample: Convert region records to text.

     

    let
    Source=data,
    Region = "'"&Text.Combine(List.Distinct(Source[Region]),"','")&"'"
in
    Region

     

     

    Insert into sql query:

     

    let
Source = Sql.Database("xxxxx", "xxxxx", [Query="SELECT * FROM Sales WHERE Region In ("&Region&")"])
in
Source

     

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi schdef,

 

You can tansfrom your data to text, then use it into sql query.

 

Sample: Convert region records to text.

 

let
    Source=data,
    Region = "'"&Text.Combine(List.Distinct(Source[Region]),"','")&"'"
in
    Region

 

 

Insert into sql query:

 

let
Source = Sql.Database("xxxxx", "xxxxx", [Query="SELECT * FROM Sales WHERE Region In ("&Region&")"])
in
Source

 

 

Regards,

Xiaoxin Sheng

schdef's avatar
schdef
Frequent Visitor
9 years ago

Thanks Xiaoxin! Was hoping to get this adressed with query folding and applying filters dynamically but your solution works as well.