Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi folks,
I have a doubt about the behaviour of Power Query (using in Excel 365).
Scenario:
• I import a table from an Excel file (~10k rows);
• I link to an SQL Server database (~3mm rows) - I'm not doing "select from table", instead i'm selecting the table in list (i read or view in someplace i don't remember that is the "correct way"?)
• In some moment, i make an inner join between the two tables (Excel and SQL Server);
In this last step, I noted that Power Query "download" the entire SQL Server table (~3mm rows) to make the join. For me, this makes sense and at same time does not.
I have permission to write what I need in the SQL Server, but I was trying to do entirely in Power Query, but the processing time doing on it is unacetable.
Is there any recomendation to improve performance doing it at the "best practices" or is this the expected behavior?
Best regards.
Orlando
Solved! Go to Solution.
Hi @Anonymous ,
yes that's a know issue. Query folding will not happen in your case.
But there's a workaround: Solved: Parameterized SQL Query with query folding - Microsoft Power BI Community
More details about the reasons: SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel – The BIccountant
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
Also check the performance difference when you do the merge "the other way round". Instead of merging the SQL table to the Excel table, merge the Excel table to the SQL table. Play with Table.AddColumn instead of Table.NestedJoin etc.
Hi @Anonymous ,
yes that's a know issue. Query folding will not happen in your case.
But there's a workaround: Solved: Parameterized SQL Query with query folding - Microsoft Power BI Community
More details about the reasons: SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel – The BIccountant
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
50 | |
19 | |
12 | |
11 |