If you want to optimize a very complex Power Query transformation that takes excessive time to refresh, you can adopt any of the following approaches:
Reduce Applied Steps and Data Load
Eliminating unnecessary columns very early in a query reduces the amount of processing to be done.
Filter the data at the source instead of Power Query wherever possible.
Avoid unnecessary transformations, such as unnecessary sorting or changing data types multiple times.
Optimize Merging and Joins
Queries must be merged on indexed or pre-aggregated columns to avoid any performance issues.
Whenever possible, joins are performed on the data source (SQL, etc.) rather than in Power Query.
Use Table.Buffer() strategically to cache intermediate results, ensuring results are not recalculated unnecessarily.
Use Query Folding and Performance-Optimized Functions
Check that query folding is working by ensuring that transformations are being pushed back to the data source (e.g., any filtering, grouping, or calculations in SQL-based sources).
Replace row-wise operations (like adding custom columns with complex logic) with bulk transformations that can process the data as efficiently as possible.
Avoid using List.Generate(), Table.AddColumn() and recursive functions in excess, as this slows down performance.
