Troubleshooting “Evaluation ran out of memory” in the Power Query Engine

The error "Evaluation ran out of memory" indicates that Power Query’s background mashup container (Microsoft.Mashup.Container.*) exceeded the physical or virtual memory allocated to it by the operating system. This occurs during resource-heavy data refreshes when the engine attempts to buffer uncompressed datasets, evaluates unoptimized joins, or runs on a 32-bit version of Microsoft Excel. It is a memory exhaustion crash, not an issue with broken logic or missing source files.

Fast Fix

Verify that you are using 64-bit Excel via File > Account > About Excel; 32-bit installations are strictly limited in addressable memory and frequently crash. In the Power Query Editor, remove any manual Table.Buffer() or Binary.Buffer() steps, filter out unneeded rows and columns early, and ensure that merge operations do not produce uncontrolled many-to-many row explosions.

---

Diagnostic Snapshot

Property	Value / Condition
Error String	Evaluation ran out of memory. / Out of memory (Mashup Engine)
Affected Process	Microsoft.Mashup.Container.NetFX45.exe (or 64-bit equivalent)
Primary Root Cause	32-bit memory limits, broken query folding, or unbuffered Cartesian join expansions
Immediate Mitigation	Migrating to 64-bit Office, removing Table.Buffer, pushing filters upstream

---

Why the Mashup Engine Runs Out of Memory

Power Query streams data row-by-row whenever possible to keep memory consumption low. However, certain operations force the engine to pause streaming and load the entire dataset into uncompressed RAM, a process known as “materialization.”

Memory crashes typically occur due to three main causes:

1. 32-bit Architecture Limits: 32-bit Excel processes can only address up to 2 GB of virtual memory (or roughly 3 GB if Large Address Aware is enabled). When you process large CSV files, Excel databases, or APIs, the uncompressed data in memory can easily exceed this limit.
2. Improper Use of Table.Buffer(): Developers often use Table.Buffer to freeze a table in memory to speed up downstream merges. But buffering a large table forces the mashup engine to hold every single row in uncompressed memory, which can lead to memory exhaustion.
3. Cartesian Product Merges (Many-to-Many): If you merge two tables on a column containing duplicate keys, Power Query expands every matching combination. A table with 100,000 rows merged against another table with 100,000 rows containing non-unique keys can accidentally expand into billions of rows, consuming all available RAM within seconds.

---

Step-by-Step Resolution

Step 1: Verify Office Bitness

The most common cause of memory exhaustion is running a 32-bit installation of Office on a 64-bit Windows PC.

1. In Excel, navigate to File > Account.
2. Click About Excel.
3. Check the version string at the top of the dialog.
   • If it reads 32-bit, Excel is capped at a fraction of your computer’s installed RAM.
   • Solution: Uninstall the 32-bit version of Office and install the 64-bit version from your Microsoft 365 dashboard. This allows Power Query to utilize all available system memory.

For deeper insights into legacy architecture limits, see Excel “Large Address Aware” (LAA) Errors on 32-bit Systems.

---

Step 2: Eliminate Memory-Intensive Buffering Steps

Review your query for explicit caching calls that prevent streaming.

1. In Excel, go to Data > Queries & Connections and double-click the failing query.
2. In the Home tab, click Advanced Editor.
3. Scan your code for Table.Buffer or Binary.Buffer: // Memory-hazardous step: BufferedTable = Table.Buffer(SourceData)
4. Remove the Table.Buffer() wrapper so that the downstream step references SourceData directly: // Memory-safe streaming step: BufferedTable = SourceData
5. Click Done.

Diagnostic Rule: Only use Table.Buffer on small reference tables (under 10,000 rows) where lookups need to be cached. Never buffer primary transaction tables.

---

Step 3: Prevent Cartesian Joins (Row Count Validation)

If your query merges two tables, verify that the join keys are unique in at least one of the tables:

1. In the Power Query Editor, open the primary table and locate the merge step.
2. Open the second table being merged.
3. Select the key column(s), go to the Transform tab, and click Count Distinct Rows.
4. Compare this to the total row count:
   • If the distinct count is significantly lower than the total row count, your join contains duplicate keys.
   • Merging on non-unique keys creates an exponential row expansion that can rapidly consume gigabytes of memory.
5. Apply Remove Duplicates or a Group By step on the key column of the lookup table before merging.

---

Step 4: Move Filters to the Top of the Query

Power Query evaluates steps sequentially unless query folding optimizes the process. If you transform or add columns to millions of rows before filtering out historical years, you force the engine to hold unnecessary data in memory.

1. Review your Applied Steps pane.
2. Drag any Filtered Rows or Removed Other Columns steps as close to the Source / Navigation steps as possible.
3. Discard unneeded columns immediately. Narrow tables require substantially less memory than wide tables with dozens of unused fields.

If your dataset continues to crash during loading even after taking these steps, consult Power Query “Buffer Overflow” when loading 1M+ Rows or see “Excel ran out of resources while attempting to calculate”. For database queries where broken folding forces client-side processing, review Troubleshooting Power Query “Timeout” Errors on Large SQL Datasets.