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

[adriannabell]

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.

[ImkeF]

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

[adriannabell]

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]

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]

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]

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]

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

[v-yingjl · Accepted solution]

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]

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

hopefully that will bring you onto the right track.

[adriannabell]

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]

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.