cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FranciscoHoff
Frequent Visitor

Using a list as a parameter of an SQL query

Hello.

I'm not sure how to use a list as a parameter of an SQL query. The list I have is of integers:

FranciscoHoff_0-1649364324897.png

I'm trying to use it as a "WHERE IN" parameter inside an SQL query through the advanced editor:

 

let
    Fonte = Sql.Database("test", "test_readonly", [Query="SELECT *#(lf)FROM TEST tst#(lf)WHERE tst.field IN (" &ProtocolosEquipe10& ")"])
in
    Fonte

 

What I want to reproduce in the SQL query is:

 

SELECT *
FROM TEST tst
WHERE tst.field IN (9875825, 9872576, 9870536, 9870530, 9870524, 9870482, 9870455, 9870449, 9870391, 9870383, 9870375, 9870371, 9870159, 9870086, 9870078, 9870074, 9870071, 9869966, 9869845, 9869838)

 

How can I do this without having to generate a parameter inside Power Query?

1 ACCEPTED SOLUTION
Vijay_A_Verma
Community Champion
Community Champion

You are just there. Just replace ProtocolosEquipe10 by following

Text.Combine(ProtocolosEquipe10,", ")

let
    Fonte = Sql.Database("test", "test_readonly", [Query="SELECT *#(lf)FROM TEST tst#(lf)WHERE tst.field IN (" & Text.Combine(ProtocolosEquipe10,", ") & ")"])
in
    Fonte

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Community Champion
Community Champion

You are just there. Just replace ProtocolosEquipe10 by following

Text.Combine(ProtocolosEquipe10,", ")

let
    Fonte = Sql.Database("test", "test_readonly", [Query="SELECT *#(lf)FROM TEST tst#(lf)WHERE tst.field IN (" & Text.Combine(ProtocolosEquipe10,", ") & ")"])
in
    Fonte

Hi, @Vijay_A_Verma! Thanks for your reply.

I got this error, right on the first iteration of the list:

 

Expression.Error: We were unable to convert the value 9810656 to type Text.
Details:
     Value=9810656
     Type=[Type]

 

EDIT:

I converted my data type to text before converting it to list and I was able to perform the query. I don't know if it's the most appropriate way, but it worked.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors