Stop Redundant API Calls in Power Query List.Generate Loops

A user on a Power BI and Excel forum recently posted: “I am using List.Generate to loop through 50 pages of an API. I used Table.Buffer on my ‘Credentials’ table so the API key isn’t re-fetched every time, but looking at my web logs, Power Query is still hitting the Auth endpoint for every single page iteration. Why is Table.Buffer failing me?”

This is a classic Power Query “Lazy Evaluation” trap. The Mashup Engine is designed to stream data, and it often decides that re-calculating a step is “cheaper” than keeping a large table in memory—even if you explicitly use Table.Buffer.

Comparing Methods to Prevent Redundant Calls

Method	Reliability	Complexity	Best For
Standard Table.Buffer	Low	Low	Simple local data joins.
Record Injection	High	Medium	API Pagination with static keys/tokens.
External Query Reference	High	Low	Large configuration tables or slow SQL lookups.
Function.ScalarValue	Very High	High	Forcing single-evaluation of a specific value.

---

Recommended Approach: The “Record Injection” Pattern

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

Instead of referencing a buffered table inside the List.Generate function (which causes the engine to re-evaluate the source query for every loop iteration), you should extract the values into a Record first and pass that record as part of the state in List.Generate.

Step 1: Create and Buffer your Config

Don’t just buffer the table; convert it into a record so it exists as a static object in memory.

// Query Name: GetConfig
let
    Source = Excel.CurrentWorkbook(){[Name="ApiSettings"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Key", type text}, {"Value", type text}}),
    // Convert the table to a Record: [ApiKey = "...", BaseUrl = "..."]
    ConfigRecord = Record.FromList(Typed[Value], Typed[Key]),
    BufferedConfig = CheckValue = List.Buffer({ConfigRecord}){0}
in
    BufferedConfig

Step 2: Inject the Record into List.Generate

By passing the Config as part of the “Initial” state of List.Generate, you “pin” it to the loop’s scope.

let
    Config = GetConfig,
    Pages = List.Generate(
        () => [
            Page = 1,
            Data = Json.Document(Web.Contents(Config[BaseUrl] & "?page=1", [Headers=[Auth=Config[ApiKey]]])),
            Config = Config // Pinning the config here prevents re-fetching
        ],
        each [Page] <= 10, // Limit or condition
        each [
            Page = [Page] + 1,
            Data = Json.Document(Web.Contents([Config][BaseUrl] & "?page=" & Text.From([Page] + 1), [Headers=[Auth=[Config][ApiKey]]])),
            Config = [Config] // Pass it to the next iteration
        ],
        each [Data]
    )
in
    Pages

---

Why “Table.Buffer” fails in loops

Power Query utilizes “Folding” and “Streaming.” When you place Table.Buffer inside a recursive function or List.Generate, the engine often views the buffer as “stale” once the next iteration starts. It attempts to ensure data integrity by re-requesting the source.

By converting your data to a List or Record and buffering that specifically, you move the data from a “Table” (which the engine likes to stream) to a “Scalar/Value” (which the engine is more likely to keep in a single memory address).

Troubleshooting Common Traps

• The “Privacy Levels” Error: If you reference a buffered API key query inside another query, you may get a “Formula.Firewall” error. To fix this, ensure File > Options and Settings > Query Options > Privacy is set to “Ignore Privacy Levels” for the specific workbook (not recommended for sensitive enterprise data) or ensure both queries share the same Privacy Level (e.g., both “Organizational”).
• Buffer is not Cache: Table.Buffer only lasts for the duration of a single refresh. If you close the file and re-open it, the first call will always be live.
• Memory Exhaustion: If you use List.Buffer on a dataset with millions of rows to try and speed up a loop, Excel might crash. Buffering is best used for small “lookup” or “configuration” tables, not the primary data being processed.

Related Questions

What if I need to apply this to filtered rows? If you are filtering a table and then running a function on each row (e.g., via Table.AddColumn), use Table.Buffer on the filtered result before adding the column. This prevents the filter logic from re-calculating for every row.

Can I automate this with VBA or Power Automate? While you can trigger an Excel Power Query refresh via VBA (ActiveWorkbook.Connections("Query - MyQuery").Refresh), the internal buffering logic is strictly handled by the Mashup Engine. VBA cannot force the engine to keep a buffer alive longer than its internal logic dictates.

Does this work in Power BI? Yes, the logic is identical. In Power BI, however, you have more control over data sources in the Service. If you are hitting API limits, consider using a Dataflow to stage the API data first, then consume the Dataflow in your report. This acts as a permanent “buffer” in the cloud.