More than complex multi-step M queries in Power Query need to be well written; they have to be well-optimized and efficient overall. Here are some proven techniques for doing so.
"Reduce Early": Filter out the unnecessary rows and columns in as early a query stage as possible. In transformation, the speed improvement possible by reducing the amount of data that Power Query has to touch is enormous. Examples would be applying filtering and column selection immediately after importing data to eradicate all irrelevant information.
Optimize Query Folding: This is when transformations by Power Query are translated into native queries that run on the database for mass processing. Ensure most steps support query folding, and at the end of your transformations, do not perform any operations that break query folding, like adding custom columns or using non-foldable functions. Use tools such as "View Native Query" to check which steps have folded.
Modular Queries to Use: Dissect overarching queries into smaller, more adaptable, and reusable queries. This modular approach will make it easier to troubleshoot while ensuring that Power Query optimizes intermediary steps for performance. Define each base common query for repetitively executed tasks and reference them in the main query to save redundancy.
Reduce Usage of Complex Operations: Heavy operations, i.e., merging, appending, and grouping on more than one big reference dataset; sometimes, it would be better to do pre-aggregation of data in its source system. If we can't do it during table merging, then make certain that in the data source there are indexed or sorted columns.
Disable Load for Intermediate Queries: The creation of too many intermediate queries means that one should ensure that the 'Enable load' option is disabled for queries not to be used in the final report, hence preventing the consumption of unnecessary resources during refreshes.
Buffer Use: Whenever a dataset is used multiple times in a single query, the data should first be cached in memory by the Table.Buffer() function instead of reloading it or recalculating it several times. This should be used selectively as it will create a burden on memory.
Monitoring Performance and Tuning: Use Power Query's performance analyzer to find out which transformations take too long. Then, you can rearrange or refine certain steps that take too much time to complete.
Preprocess Data in Source: If you have large chunks of data that you can process at the source using SQL or other means before importing them in Power Query, do so. This reduces the load on Power Query and improves efficiency in general.
By applying these techniques, advanced data transforms could be run in Power Query, and the time required to refresh large datasets would be reduced considerably.
