Help with Parent / Child Query Parameter

[Anonymous]

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)) "])

[ImkeF]

Hi @Anonymous ,

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&')

[Anonymous]

@ImkeF 

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. 

[MFelix]

Hi @Anonymous ,

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