Optimising Power Query in an excel spreadsheet

[gawumpki]

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

[Jimmy801]

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

Jimmy

[camargos88 · Accepted solution]

@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://support.microsoft.com/en-us/office/filter-a-table-power-query-b5610630-f5bf-4ba4-9217-a628f9...

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/

[gawumpki]

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