Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
Hello.
I'm not sure how to use a list as a parameter of an SQL query. The list I have is of integers:
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?
Solved! Go to Solution.
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
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 ??
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
8 | |
7 | |
6 |