Forum Discussion
Optimising Power Query in an excel spreadsheet
Hello
I have a Power query which directly queries SQL Server database and loads a few tables into the excel sheet. The tables are quite large, most over 300k rows. I actually only need around 600 rows in the query so i apply filters using DAX. Is there a way to filter the query before it loads the data? Currently, refreshing the spreadsheet and all of the tables takes approx. 30-40 mins.
If this isn't possible, is there a forum page which has tips on how to optimise/streamline the power query?
Any advice will be gratefully received,
Laura
gawumpki ,
You can apply filters on Power Query for the SQL tables, so will can take advantage of Query Folding, bringing of the necessary amount of data.
https://docs.microsoft.com/en-us/power-query/power-query-folding
https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding
https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/
3 Replies
Hello gawumpki
as camargos88 already mentioned Power Query can fold, that means that only the data you requested is transfered from SQL to Excel. However, when you write a DAX, exactly that is queried. The best practise is to connect to SQL and put all steps, that are reducing the amount of that in the beginning (removing rows, columns). Don't put any sort 🙂
Hope this helps
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmygawumpki ,
You can apply filters on Power Query for the SQL tables, so will can take advantage of Query Folding, bringing of the necessary amount of data.
https://docs.microsoft.com/en-us/power-query/power-query-folding
https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding
https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/
Great thank you so much for the links. The 'View Native Query' did not work for my query but i used 'Value.NativeQuery' to get to what i needed.
Thanks again
Laura
