Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
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 ...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 ,
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.
Hi @adriannabell ,
this first deviation I can spot is this:
hopefully that will bring you onto the right track.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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...
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
64 | |
27 | |
18 | |
13 |