Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.