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)