How to identify what is slowing down refresh

[justlogmein]

Speed seems to be an issue with every project I do in Power Query (both Excel and Power BI). I don't think my queries are too complex, there are some merges with a couple of million rows but data science terms, that is not a high amount. I want to identify what queries and specifically what steps are taking so long, but I can't find any articles on how to do this easily. Looking for some starting pointers (for Excel).

[BA_Pete · Accepted solution]

Hi @justlogmein ,

In Power Query, I would start with what I would consider to be the two major players:

1) Whole-table operations i.e. functions that require the whole table to be pulled into memory to perform. This includes Group By, Pivot/Unpivot, Sorting etc. Practical mitigation might include performing these types of steps in a fully-folded query, on the source itself (via SQL views etc.), or just removing them altogether and doing the equivalent transformation via DAX or visual selection.

Some useful info on technical mitigation:

https://blog.crossjoin.co.uk/2021/06/06/speed-up-power-query-in-power-bi-desktop-by-allocating-more-... 

2) Merges. These are very expensive in Power Query. Practical mitigation might include performing merges in a fully-folded query, on the source itself (via SQL views etc.), replacing merges done to filter a table with buffered list lookups, or just removing them altogether and replacing them with the appropriate relationship in the data model.

Pete

[CRDover]

Man I am a big fan of using whole tables and merges. No wonder my stuff is so slow