Handling “Expression.Error: Evaluation ran out of memory.”

In Power Query, Expression.Error: Evaluation ran out of memory means the Power Query Mashup Engine (Microsoft.Mashup.Container) exceeded the physical or virtual memory limits assigned to its process. This happens when processing massive datasets, running inefficient nested row-by-row loops, breaking query folding, or buffering massive tables into local RAM.

Quick Answer

Disable background data previews under File > Options and Settings > Query Options > Data Load. Ensure your query maintains Query Folding so the database handles filtering and joins instead of your local machine. Remove unnecessary Table.Buffer calls, and switch to 64-bit Excel if you are currently running the 32-bit edition.

---

Understanding Power Query’s Memory Architecture

Power Query uses a dedicated streaming engine designed to process data in chunks without loading entire files into system RAM at once.

However, certain operations force the mashup engine to abandon streaming and load the entire dataset into memory simultaneously:

• Sorting an unindexed table.
• Grouping large unindexed datasets.
• Merging across disparate sources (e.g., combining a 10-million-row SQL view with a local Excel file).
• Using Table.Buffer or List.Buffer.

When this happens, the memory footprint of the Microsoft.Mashup.Container process spikes. If you run 32-bit Excel, the process is hard-capped at 2 GB of virtual address space. Once that threshold is crossed, or once physical RAM is exhausted in 64-bit Excel, Power Query immediately terminates the operation:

Expression.Error: Evaluation ran out of memory.

---

Critical Adjustments to Eliminate Memory Exhaustion

1. Turn Off Background Preview Caching

By default, Power Query continuously fetches and caches data in the background for every step of every query to keep the interface fast and responsive. On large models, this background engine can consume gigabytes of memory before you even hit “Close & Apply.”

1. In Excel, go to File > Options and Settings > Query Options.
2. Under Global, select Data Load.
3. Under Background Data, uncheck:
   • Allow data preview to download in the background.
4. Under Current Workbook, select Data Load, and uncheck:
   • Automatically detect column types and headers for unstructured sources.
5. Click OK, save the file, close Excel, and reopen the workbook.

2. Remove Indiscriminate Buffering (Table.Buffer)

A common piece of advice online is to wrap slow merge tables in Table.Buffer(...). While Table.Buffer can speed up lookups on small reference tables (under 50,000 rows), applying it to large datasets forces Power Query to pull the entire table into system RAM, instantly consuming all available memory allocations.

• Audit Step: Open the Advanced Editor for your query and search for .Buffer.
• If you see Table.Buffer(#"Previous Step"), delete the wrapper and reference #"Previous Step" directly. Let the engine stream the data instead of forcing a full memory load.

3. Maintain Query Folding on Server Sources

When connecting to databases like SQL Server, Oracle, or Snowflake, Power Query attempts to convert your steps into native SQL statements (Query Folding). This offloads data processing, filtering, and aggregation to the database server.

If you insert a non-foldable step (such as an index column, a complex custom M function, or certain text manipulation steps) early in your query, query folding breaks:

• Everything before that step runs on the SQL server.
• Everything after that step forces Power Query to download all remaining rows across the network and process them inside local machine memory.

How to verify: Right-click your transformation steps in the Applied Steps list. If View Native Query is grayed out, folding has broken. Move your filters, column removals, and groupings to the very top of your Applied Steps list so they fold to the database before any non-foldable transformations execute.

For a complete breakdown of query folding optimization and diagnosis, see Troubleshooting “Query Folding” breaks: Why your SQL is running slow in Excel.

4. Filter Rows and Drop Unused Columns Early

Never carry unnecessary fields through your transformation pipeline. If an external table has 80 columns and 2 million rows, but your final dashboard only needs 4 columns:

1. Immediately after your Source or Navigation step, use Choose Columns to delete the 76 unneeded columns.
2. Apply date or category filters immediately.
   Dropping unnecessary columns early reduces the memory footprint of every subsequent step by up to 90%.

5. Verify Excel Bitness (32-Bit vs. 64-Bit)

If you are processing large tables on 32-bit Excel, your mashup container is severely restricted by memory architecture:

1. In Excel, click File > Account.
2. Click About Excel.
3. Look at the top line: it will indicate either 32-bit or 64-bit.
   If it says 32-bit, work with your IT department to migrate to 64-bit Office. 64-bit Excel removes the 2 GB process limit, allowing Power Query to access all available physical RAM installed on your computer.

---

Deeper Resource Escalation

If your query still runs out of memory after applying these optimizations, you are likely hitting an operational scale boundary where Power Query inside Excel is no longer the appropriate tool for the job.

• Complex Cross-Source Merges: If you are joining multiple files totaling over 10 million rows, consider staging your data in a relational database or using SQL views to handle the join before importing the clean output into Excel.
• Large Folder Imports: If you are combining thousands of CSV or text files using Folder.Files, see Fixing errors in Folder.Files when a temp file (~$) is open in the folder to optimize folder parsing pipelines and avoid memory leaks caused by temporary system files.