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 ,

to increase performance you want to ensure that the query will fold against the database, meaning that the filter is applied by the database engine and only the filtered result is returned to PQ.
Merging against a non-SQL-source will usually break query folding. The workaround is to transform the content of your Excel-column to a text string and then use that in an in-statement in a query like that:

 

let
Source = Sql.Database("xxxxx", "xxxxx", [Query="SELECT * FROM Sales WHERE Region In ("&Region&")"])
in
Source

see: Solved: Re: Parameterized SQL Query with query folding - Microsoft Power BI Community

 

The 2nd challenge is that converting the type at the SQL-source will usually also break query folding. So best would be if you could convert the type from your Excel source instead. But if that isn't possible, you might have success with the technique described here: Chris Webb's BI Blog: Data Type Conversions For SQL Server Sources And Query Folding In Power Query Chris Webb's BI Blog (crossjoin.co.uk)

 

adriannabell's avatar
adriannabell
Regular Visitor
4 years ago

I can change the field type in my Excel sheet to match without an issue. I found this code on the link you referenced on how to transform the content of my Excel column to a text string:

let

Source=data,

Region="'"&Text.Combine(List.Distinct(Source[Region]),"','")&"'"

in

Region

 

However...I have a follow up question (because I don't know what I'm doing - ha!).

 

Using my data, is this what the code should look like?

 

let

Source=Excel.Workbook("I:\ITS-AUs.xlsx"),null,true),

AU = "'"&Text.Combine(List.Distinct(Source[AU Number]),"','")&"'"

in

AU

 

If this is the correct code, I don't understand "where" I put the convert to text code so I can use it in my SQL which I believe would look like this?

 

let

Source=Sql.Database("servername","databasename",[Query="SELECT * FROM HR_Base WHERE AU In ("&AU&")"])

in

Source

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    Hi adriannabell ,

    almost.
    If you import Excel data and select a table or a sheet, an couple of steps will ususally be generated automatically.  First is the selection of the Excel file, second the selection of the Excel object within the file and third some type conversion. It would look like so and the 4th step is how the text conversion would work:

     

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\imkef\Downloads\PBI_AU.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]),"','")&"'"
in
    Custom1

     

    Easiest would probably be to integrate the SQL step right into that query:

     

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\imkef\Downloads\PBI_AU.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 ("& Custom1 &")"])
in
    SQL_Data

     

     

    But if you want to keep that in 2 different queries, you'd have to refence to the created filter string by using the name of the query from which it is returned:

     

    let

    Source=Sql.Database("servername","databasename",[Query="SELECT * FROM HR_Base WHERE AU In ("& <NameOfTheQueryThatReturnsTheString> &")"])

    in

    Source

     

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