Fix Excel Power Query Table.Buffer Multi-Call Issues

A user on a popular Excel BI forum recently posted: “I have a large SQL table and a small mapping table. I used Table.Buffer on the mapping table before merging them, but my SQL Profiler shows Excel is still querying the database hundreds of times—once for every row in the join. Isn’t buffering supposed to prevent this?”

This is a classic Power Query “Lazy Evaluation” trap. Even when you tell Excel to buffer a table, the engine’s optimizer may decide to “stream” the data instead to save memory, effectively ignoring your buffer and re-requesting the data from the source repeatedly.

The One-Click Quick Fix

To force Power Query to respect the buffer during a nested join or Table.AddColumn operation, you must break the “Query Folding” chain. The most reliable way is to reference the buffered table as a separate named step and then perform a “Dummy Sort” or a “Dummy Filter” on it.

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

---

Why Table.Buffer Fails (and How to Fix It)

The Power Query Mashup Engine is designed to be “lazy”—it only grabs data when absolutely necessary. When you perform a nested join (like a “Left Outer Merge”), the engine often thinks it is more efficient to re-run the “Right” table for every row of the “Left” table.

Method	Why it usually fails	The “Pro” Workaround
Standard Join	The engine “folds” the request back to the source, causing multiple hits.	Use Table.Buffer on the smaller table.
Simple Table.Buffer	The optimizer might ignore the buffer to prioritize low memory usage.	Add a “Dummy Step” (e.g., Table.SelectRows) after the buffer.
List.Buffer	Works for single-column lookups but fails for multi-column joins.	Convert the lookup key to a buffered list for List.Contains checks.

---

Step-by-Step: Forcing the Buffer

Follow these steps to ensure your data source is only called once.

1. Isolate the “Lookup” Table

Before merging, go to the table that is being “called” too many times. In the Power Query Editor, ensure you have a dedicated step for buffering.

1. Select the query you want to cache.
2. In the Formula Bar, wrap the previous step name: = Table.Buffer(Source)
3. The Secret Step: Create a new step by clicking the fx icon next to the formula bar and enter: = Table.SelectRows(StepName, each true) (This “Identity Filter” forces the engine to materialize the buffered table because it cannot “predict” the filter outcome through the buffer.)

2. Perform the Nested Join

When you go back to your “Main” table to perform the merge:

1. Go to Home > Merge Queries.
2. Select your main table and the buffered “Lookup” table.
3. Perform the join as usual.
4. In the M-Code (Advanced Editor), ensure the join refers to the Dummy Filter step from Step 1, not the original Source step.

3. Use a Primary Key (Optional but Recommended)

If you are joining on a specific ID, adding Table.AddKey before the buffer tells Excel the data is unique, which prevents unnecessary re-scans.

// Example snippet — verify in your environment
let
    Source = Sql.Database("Server", "DB"),
    Data = Source{[Schema="dbo", Item="MappingTable"]}[Data],
    AddKey = Table.AddKey(Data, {"ID"}, true),
    Buffer = Table.Buffer(AddKey),
    ForceMaterialize = Table.SelectRows(Buffer, each [ID] <> null)
in
    ForceMaterialize

---

Common Traps to Avoid

• Buffering the wrong table: Always buffer the smaller table (the “Right” table in a join). Buffering a 1-million-row table will likely cause Excel to crash due to memory exhaustion.
• Privacy Levels: If your Privacy Levels (File > Options and Settings > Query Options > Privacy) are set to “Combine data according to… settings,” Excel may ignore the buffer to ensure data doesn’t leak between a local file and a cloud SQL server. Setting levels to “Organizational” or “Ignore” (if safe) can sometimes resolve multi-call issues.
• Query Folding: If you see the “View Native Query” option on your merge step, the buffer is being ignored. The buffer’s job is specifically to stop query folding at a certain point.

---

Frequently Asked Questions

What if I need to apply this to filtered rows? If you only need a subset of the data, apply your Table.SelectRows before the Table.Buffer. This ensures only the necessary data occupies your RAM.

Can I automate this with VBA? No, VBA cannot directly control the Power Query Mashup Engine’s internal buffering logic. You must modify the M-code steps within the Power Query Editor.

Does this work for Web/API sources? Yes. In fact, this is most critical for Web.Contents calls. Without Table.Buffer and a “Dummy Step,” Excel might hit an API endpoint once for every row in your spreadsheet, leading to 429 (Too Many Requests) errors or API rate-limiting.