Falconflyer75 has put their finger on a real tension in modern spreadsheet work, and the answer is straightforward: Yes, Power Pivot solves the problem they're describing, and it does so in a way that respects both their data integrity and their system's memory limits. The concern about a pivot table refreshing before the Power Query output finishes is legitimate in a traditional Excel setup, but Power Pivot changes the architecture entirely. Instead of each pivot table pulling fresh data from the query output, which creates that race condition, Power Pivot loads the transformed data into its own in-memory columnar engine once, and then all pivot tables draw from that single, stable source. The refresh order is managed at the model level, not the worksheet level, so the risk of stale or partially loaded data disappears.
The memory issue is the more compelling part of this question. Running the same complex Power Query transformation three or four times on a 32-bit system is exactly the kind of workflow that grinds Excel to a halt. Each query instance consumes its own memory allocation, and on a 32-bit system, that allocation is capped at roughly 2GB total. Power Query is a powerful transformation engine, but it was not designed to be a visualization layer. Power Pivot, by contrast, compresses data aggressively and keeps it in a single model that all pivot tables share. The user is not running the query multiple times; they are running it once, loading the result into the model, and then creating as many pivot tables as needed without additional memory strain. For someone already feeling the ceiling of a 32-bit environment, this is not a nice-to-have, it is the difference between a file that works and one that crashes.
We would push back gently on the idea that Power Pivot is unfamiliar territory worth avoiding. The learning curve is real, but it is shallow compared to the alternative of re-running heavy queries. The key insight is that Power Pivot is not an entirely separate tool, it is a feature that sits inside Excel, and it uses the same DAX language that Power BI users rely on. Start by loading your Power Query output into the Data Model instead of a worksheet. From there, you create pivot tables that point to the model. The refresh sequence becomes: Power Query runs once, the model updates, then all pivot tables refresh from the model in a controlled order. No race conditions, no duplicated memory load, and no need to restructure your existing query. For anyone working with 32-bit Excel on complex transformations, this is the practical path forward.