The user who asked this question, let's call them Falconflyer75, has already solved the hardest part of the problem. They recognized that Power Query, for all its elegance in transformation and merging, has a ceiling. When you push it past a certain volume of operations, it slows to a crawl. That moment of frustration is exactly where Power Pivot stops being a niche tool and becomes the obvious next step. Our opinion is straightforward: Falconflyer75 should absolutely explore Power Pivot, not as a replacement for what they know, but as a complementary engine that handles the heavy lifting their current workflow cannot.
Here is what that means in practical terms. Power Query excels at cleaning and shaping data row by row. Power Pivot excels at computing over that data in memory using its own compression and columnar storage engine. When Falconflyer75's file demands extensive merges and transformations, the bottleneck is almost certainly the iterative process inside Power Query, each merge step recalculates across rows. Power Pivot sidesteps that entirely. It loads the transformed tables into a data model, builds relationships between them, and lets you write DAX measures that compute on the fly without reprocessing every row. The speed difference is not incremental; it is structural.
The user's comfort with VBA and Power Query means they already think in terms of logic and automation. That mental model translates directly to DAX, the formula language of Power Pivot. The learning curve is real, but it is not steep for someone who already writes code and builds query steps. They do not need to abandon their existing work. They can bring the cleaned tables from Power Query into the data model and then let Power Pivot handle the aggregations and complex lookups that were dragging their file down. The result is a workflow that separates preparation from computation, each tool doing what it does best.
Falconflyer75 should start with a single test case: take one of the slowest merge operations from their current file, load the source tables into the Power Pivot data model, and write a simple DAX measure to produce the same output. The difference in calculation time will be immediate. That is the concrete point. Not a promise of revolution, just a direct comparison that proves the concept. Once they see that gap, the rest of the exploration will answer itself.