Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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
Super User
Super User

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

3 REPLIES 3
moocifer
New Member

How do you avoid the:

 

Formula.Firewall: Query 'QUERYNAME' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

 .. error, that this generates ??

Vijay_A_Verma
Super User
Super User

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
Anonymous
Not applicable

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.