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 currently loading it with this syntax:

Source=Sql.Database("servername","MyDatabase", [Query="select * from HR_Base WHERE SECTION_DESCR='Infra Tech Svcs'"])

 

However, this is not actually the filter I want on it.  HR_Base has a field called AU.  I also have an existing query in Power BI called ITSAUs that was created from an Excel Spreadsheet import with a field called AU Number.  

 

What I want to do is -- only load those records from HR_Base where AU matches what is in AU Number of the ITSAUs query.

 

so only give me records from HR_Base where HR.Base[AU]=ITSAUs[AU Number] (I know this isn't correct syntax for anything - just trying to show the data I'm comparing)

 

Another factor is -- I need to convert the AU field to a whole number before it can compare because the data types are not the same in the two sources.

 

Is this possible?

 

I originally did an import of all of the data from HR_Base and then did a Merge Query as New to try to create my new query but due to the size of HR_Base, the performance in doing this is not reasonable so I was looking for some alternatives to getting only the data I need and having it load more efficiently.

 

 

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.

     

10 Replies

  • 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