Forum Discussion
Helper IIIHow to identify what is slowing down refresh
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:
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
Super UserHi 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:
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
Man I am a big fan of using whole tables and merges. No wonder my stuff is so slow
