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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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