Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Context:
We have two queries in our Power BI project.
Query #1 has a data source type: SQL Database.
Query #1: (EmployeeDetails)
SELECT *
FROM Employee
WHERE FirstName IN ('&list&')
Query #2 has a data source type: CSV
Query #2: (BadFirstNames)
Bob,
Tom,
Jack,
Jill,
.....
I am trying use the CSV data from Query #2 inside the WHERE Clause of Query #1.
I have tried using the Advanced Query Editor to do this but with no luck. (Example Below)
let
list =#"BadFirstNames",
Source = Sql.Database("NorthStar", "NorthStarTestDatabase", [Query="SELECT * #(lf)#(tab)#(tab)#(tab)FROM Employee#(lf)#(tab)#(tab)#(tab)WHERE TestCode IN ('&list&')#(lf)#(tab)#(tab)) "])
Hi @cshepler ,
you need proper quotes like shown here: https://community.powerbi.com/t5/Desktop/Parameterized-SQL-Query-with-query-folding/td-p/171503
so this: ("&list&") instead of this: ('&list&')
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I tried the following,
let
list =#"BadFirstNames",
Source = Sql.Database("NorthStar", "NorthStartDB", [Query="SELECT * #(lf)#(tab)#(tab)#(tab)FROM [Employee]#(lf)#(tab)#(tab)#(tab)WHERE FirstName IN ("&list&")#(lf)#(tab)#(tab))
I recieved an "Expression.Error: We cannot apply & to types Text and Table."
"BadFirstNames is the name of the query that is JUST a CSV file containing a list of bad first names.
Hi @cshepler ,
This is a good question for a great expert in M language to answer you.
@ImkeF can you help out in this one maybe you have a great trick to pass the parameters from CSV files to SQL query.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |