Forum Discussion
Is it possible to perform a SQL select statement against an existing Power BI Query?
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_DataBest 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.
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
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.
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.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
Hi adriannabell ,
this first deviation I can spot is this:hopefully that will bring you onto the right track.