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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How would you do this for more than 1000 items in a list?

I am trying to send a list back to Oracle and there is a 1000 item limit in the where clause.

If anyone has any ideas about this or has gotten around it in any way please let me know.

I am very new to power query and welcome all feedback.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    OR the requests together

     

    where x in (,,,,,)

    or x in (,,,,,)

    or x in (,,,,,)

     

    etc.