Question

Why does Power Query slow down significantly after merging multiple large tables?
Merging large tables in Power Query often leads to performance issues, such as long load times and memory exhaustion. This question delves into the underlying reasons for these slowdowns—such as row-by-row evaluation, lack of query folding, or inefficient joins—and explores best practices to optimize merges in large-scale ETL scenarios.

Answer 1

After merging large tables, Power Query lags primarily because of:

• Power BI must process data locally rather than pushing operations to the source due to a lack of query folding.
• Evaluation of each row during joins, particularly in cases where merge keys have mismatched data types or are not indexed.
• Merging on non-unique columns or using inefficient join types (like full outer joins) lengthen processing times and use more memory.

Tips for optimization:

• Make sure the merge keys are indexed and clean.
• When feasible, use left/right joins rather than complete joins.
• Reduce the number of rows by applying filters prior to merges.
• Use the query diagnostics tools to see if folding is supported in the merge step.
• When working with big datasets, these procedures help keep performance stable.