Fix Excel Power Query Table.Buffer Issues with Nested Joins

A user on a popular Power BI and Excel forum recently asked: “I am merging a large SQL table with a small filtered table. I used Table.Buffer on the filtered table to prevent it from re-running the SQL query for every row, but the Trace logs show the source is still being hit thousands of times during the NestedJoin. Is Table.Buffer broken?”

This is a classic Power Query behavior. Because the engine is designed to be “lazy” and optimize for “Query Folding,” it often ignores Table.Buffer if it thinks it can create a more efficient execution plan by folding the join back to the server.

The Quick Fix: The “Index Break” Method

If Table.Buffer isn’t stopping redundant calls, the most reliable fix is to force the engine to materialize the table by adding an index column immediately after the buffer.

Tested on: Excel for Microsoft 365 (Version 2408) as of 2024.

---

Step-by-Step: Forcing Power Query to Respect the Buffer

Follow these steps to ensure your “Lookup” table is loaded into memory exactly once before the Table.NestedJoin (Merge) occurs.

1. Buffer the Source Table

In your Power Query Editor, go to the table you want to cache (usually the smaller “right” side of the join). Wrap your final step in Table.Buffer.

• Example: BufferedData = Table.Buffer(PreviousStepName)

2. Add a “Hard Break” Index

This is the secret sauce. By adding an index column, you prevent the engine from “folding” the join back to the source.

1. Select your buffered step.
2. Go to the Add Column tab.
3. Click Index Column > From 0.

3. Perform the Merge

Go to your main “Fact” table and perform the Merge (Home > Merge Queries). Select the table you just indexed.

4. Remove the Index

Once the merge is complete, you can safely remove the Index column from the nested tables or the expanded results. The engine will have already “frozen” the data in memory.

---

Why the Standard Table.Buffer Fails

Power Query uses a “streaming” logic. Table.Buffer caches data in memory, but the Table.NestedJoin function often triggers a re-evaluation of the “Right” table for every row of the “Left” table if it detects that the source could be queried again (especially with SQL or Web sources).

Feature	Table.Buffer alone	Table.Buffer + Index	Result
Execution	Lazy / Deferred	Eager / Materialized	Indexing forces immediate calculation.
Query Folding	Often maintained	Broken (Intentional)	Folding causes the “re-pulling” behavior.
Memory Usage	Moderate	Higher	You are strictly holding the table in RAM.
Best For	Internal Tables	SQL / Web API Sources	Use the Index trick for external data.

---

Troubleshooting & Common Questions

“Will this make my file size huge?”

No. Power Query’s internal cache during the “Refresh” process is separate from the data loaded into the Excel Grid. While it will use more RAM during the refresh, the final file size remains determined by the rows loaded to the worksheet or Data Model.

“What if I’m joining on multiple columns?”

The “Index Break” method works regardless of your join keys. The goal isn’t to use the index for the join, but to use it as a “checkpoint” that forces Power Query to finish the lookup table before moving to the main table.

“Is there a way to do this without adding columns?”

If you are comfortable with the Advanced Editor, you can use Table.StopFolding(Table.Buffer(Source)). This is a semi-documented function specifically designed to prevent the engine from reaching back to the source. However, it is only available in more recent versions of the Power Query engine (example — verify in your environment).

“Does this work for Web.Contents calls?”

Yes, this is highly recommended for Web API calls. Without a buffer and a hard break (Index), Power Query might try to call the API for every row in your main table, which can lead to “429 Too Many Requests” errors or extremely long refresh times.

Summary Checklist

1. Buffer the table you want to cache.
2. Add an Index Column to that table immediately after buffering.
3. Merge your main table with this indexed table.
4. Remove the index column if it’s no longer needed.