Forum Discussion

adriannabell's avatar
adriannabell
Regular Visitor
4 years ago
Solved

Is it possible to perform a SQL select statement against an existing Power BI Query?

Disclaimer:  I am not a SQL developer and I am fairly new to Power BI but I'm trying to do something and I'm not sure it is even possible. ðŸ˜€   Query1 is data from a very LARGE database.  I am curr...
Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi adriannabell ,

    You can try to clear the query and re-write it like this:

    let

Source=Excel.Workbook(File.Contents("I:\ITS_Reference_Documentation\Manager Reference\ITS-AUs.xlsx"), null, true),

Table1_Table=Source{[Item="Table1",Kind="Table"]}[Data],

#"Changed Type"=Table.TransformColumnTypes(Table1_Table,{{"AU Number",Int64.Type}}),

Custom1 = "" & Text.Combine(List.Distinct(#"Changed Type"[AU Number]),"','") &"",

SQL_Data=Sql.Database("Servername","databasename",

[Query="SELECT * from HR_Base WHERE AU In ("&Custom&")"])

in

SQL_Data

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

adriannabell's avatar
adriannabell
Regular Visitor
4 years ago

I definitely prefer the option of only having one query.  Unfortunately my company has some security in place that won't let me upload a screen shot of my code and the site won't let me paste it so I'm ready to pull my hair out.  But I used the code you provided above  for the combined query and I'm getting a "Token comma expected" error on the second line that starts with Table1_Table = but  I can't figure out where the comma is missing.

ImkeF's avatar
ImkeF
Community Champion
4 years ago

Hi adriannabell ,
usually Power Query error messages are really good, but this one is usually misleading: It usually means that there is a missing or misplaced bracket. 

 

  • adriannabell's avatar
    adriannabell
    Regular Visitor
    4 years ago

    ok - i'm manually typing my code in to see if you can see anything obvious. I put the text in red like it shows in query editor to see if that helps find the problem?

     

    let

    Source=Excel.Workbook(File.Contents("I:\ITS_Reference_Documentation\Manager Reference\ITS-AUs.xlsx"), null, true),

    Table1_Table=Source{[Item="Table1",Kind="Table"]}[Data],

    #"Changed Type"=Table.TransformColumnTypes(Table1_Table,{{"AU Number",Int64.Type}}),

    Custom1="""&Text.Combine(List.Distinct(#"Changed Type[AU Number]),"','")&"",

    SQL_Data=Sql.Database("Servername","databasename",

    [Query="SELECT * from HR_Base WHERE AU In ("&Custom&")"])

    in

    SQL_Data

    • ImkeF's avatar
      ImkeF
      Community Champion
      4 years ago

      Hi adriannabell ,
      this first deviation I can spot is this:

      hopefully that will bring you onto the right track.

       

      • adriannabell's avatar
        adriannabell
        Regular Visitor
        4 years ago

        I put in the missing apostrophes on that line but am still getting the Token Comma expected error and it's pointing to the 2nd line that starts with Table1_Table...

    • v-yingjl's avatar
      v-yingjl
      Community Support
      4 years ago

      Hi adriannabell ,

      You can try to clear the query and re-write it like this:

      let

Source=Excel.Workbook(File.Contents("I:\ITS_Reference_Documentation\Manager Reference\ITS-AUs.xlsx"), null, true),

Table1_Table=Source{[Item="Table1",Kind="Table"]}[Data],

#"Changed Type"=Table.TransformColumnTypes(Table1_Table,{{"AU Number",Int64.Type}}),

Custom1 = "" & Text.Combine(List.Distinct(#"Changed Type"[AU Number]),"','") &"",

SQL_Data=Sql.Database("Servername","databasename",

[Query="SELECT * from HR_Base WHERE AU In ("&Custom&")"])

in

SQL_Data

       

      Best Regards,
      Community Support Team _ Yingjie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.