Forum Discussion
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:
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?
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
4 Replies
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 FonteHi, 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.
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 ??
