Reply
Frequent Visitor
Posts: 5
Registered: ‎04-26-2017
Accepted Solution

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 not get folded as it should (which is a problem as my SQL source has millions of rows). Any practice that makes sense thinking of performance in particular... Thxs much!!


Accepted Solutions
Super Contributor
Posts: 3,025
Registered: ‎08-14-2016

Re: Parameterized SQL Query with query folding

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

 

Capture.PNGCapture2.PNG

 

Insert into sql query:

 

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

 

 

Regards,

Xiaoxin Sheng

View solution in original post


All Replies
Super Contributor
Posts: 3,025
Registered: ‎08-14-2016

Re: Parameterized SQL Query with query folding

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

 

Capture.PNGCapture2.PNG

 

Insert into sql query:

 

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

 

 

Regards,

Xiaoxin Sheng

Frequent Visitor
Posts: 5
Registered: ‎04-26-2017

Re: Parameterized SQL Query with query folding

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