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.

     

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...

      • ImkeF's avatar
        ImkeF
        Community Champion
        4 years ago

        Please copy the query and delete all the steps after that step in question.
        This will tell you if that step actually caused the problem.
        If so, delete that step as well and navigate to the table that you want to use manually (with mouseklicks).
        Then add the rest of the code.

         

  • 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.