Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help with Parent / Child Query Parameter

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

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      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. 

       

       

       

  • 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