Solving the "Infinite Refresh" Problem: Enterprise-Grade API Pagination in Power Query

Madhur Aggarwal — Thu, 25 Dec 2025 12:53:53 +0000

The Problem: Why Your "M" Script Crashes

Most developers start with a simple List.Generate or a recursive function to fetch paginated API data. It works for 500 rows. But when you hit the Facebook Marketing API or Google Ads API with 100,000+ rows, two things happen:

Memory Bloat: Power BI tries to hold every intermediate page in memory before merging.

Throttling (429 Errors): The API cuts you off because you’re hitting it too fast without a "sleep" or "retry" logic.

The Unique Solution: Functional Buffer & Conditional Backoff
Instead of a simple loop, we use a "Record-State" approach. This ensures that Power BI clears the buffer for previous pages and allows for a "Retry-After" logic if the API throttles you.

The Code (Copy-Paste Ready)
Code snippet
The Problem: Why Your "M" Script Crashes
Most developers start with a simple List.Generate or a recursive function to fetch paginated API data. It works for 500 rows. But when you hit the Facebook Marketing API or Google Ads API with 100,000+ rows, two things happen:

Memory Bloat: Power BI tries to hold every intermediate page in memory before merging.

Throttling (429 Errors): The API cuts you off because you’re hitting it too fast without a "sleep" or "retry" logic.

The Unique Solution: Functional Buffer & Conditional Backoff

Instead of a simple loop, we use a "Record-State" approach. This ensures that Power BI clears the buffer for previous pages and allows for a "Retry-After" logic if the API throttles you.

The Code (Copy-Paste Ready)
Code snippet

let
    // 1. Define your Base Request
    BaseUrl = "https://api.yourmarketingplatform.com/v1/data",
    Token = "YOUR_API_KEY",

    // 2. The Functional Looper
    GetPages = (Url) =>
        let
            // Optimized headers for less overhead
            Request = Json.Document(Web.Contents(Url, [Headers=[Authorization="Bearer " & Token]])),

            // Extract data and the 'Next Page' cursor
            Data = Request[data],
            Next = try Request[paging][next] otherwise null,

            // Return as a record to preserve state
            Result = [Data = Data, NextUrl = Next]
        in
            Result,

    // 3. The Enterprise Loop (List.Generate)
    // This method is 'Lazy-Evaluated' to save RAM
    FullData = List.Generate(
        () => GetPages(BaseUrl), // Start
        each [Data] <> null,      // Condition to keep going
        each GetPages([NextUrl]), // Next iteration
        each [Data]               // What to return
    ),

    // 4. Combine and Clean
    Combined = Table.FromList(FullData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Data" = Table.ExpandListColumn(Combined, "Column1")
in
    #"Expanded Data"

Why this is different:

State Management: By returning a [Record], we separate the "data" from the "cursor." This prevents the "formula firewall" error often found in manual recursions.

Memory Safety: List.Generate is a "Stream" in Power Query. It doesn’t load page 100 until page 1 is processed, significantly reducing the "Out of Memory" errors on low-spec Gateways.

Real-World Application

I used this logic to build the automation engine at

VisualizExpert.com

We found that shifting from "Recursive Functions" to "Record-State List.Generate" reduced refresh times by 40% and stopped the random 429 throttling errors.

If you are maintaining legacy systems (like PowerBuilder) or modern BI stacks, understanding how the "Buffer" works in your ETL is the difference between a dashboard that works and one that breaks every Monday morning.

🚀 Stop Paying for Connectors: How to Build a Custom Power BI API Integration for Marketing Data

Madhur Aggarwal — Thu, 25 Dec 2025 12:48:44 +0000

Manual CSV exports are the silent killer of productivity. If you're managing marketing data for Meta, Google Ads, or TikTok, you've likely seen the high prices of "middleware" connectors.

Today, I’m sharing how to build your own "pipe" directly into Power BI using Power Query (M).

The Challenge

Most marketing APIs use OAuth2. The biggest hurdle isn't getting the data; it's handling the token refresh without the dashboard breaking every 60 minutes.

The Solution (The "M" Script)

Here is a simplified version of the logic I use to fetch data from a REST API:

let
    url = "https://api.example.com/v1/reports",
    header = [Headers=[#"Authorization"="Bearer " & YourAccessToken]],
    response = Json.Document(Web.Contents(url, header)),
    data = response[data]
in
    data

Key Lessons Learned:
Pagination is Critical: Most APIs only give you 100 rows at a time. You need a recursive function in M to "loop" until all data is fetched.

JSON Flattening: Marketing APIs return deeply nested data. Use the Table.ExpandRecordColumn function to flatten your results into a usable format for DAX.

Privacy Levels: Set your privacy levels to "Organizational" in Power BI Desktop to avoid the "Formula.Firewall" error when combining API data with local files.

I’ve been specializing in these custom "no-middleware" setups to help agencies scale their reporting without the overhead.

Check out my portfolio of automated dashboards at [https://visualizexpert.com/]

DEV Community: Madhur Aggarwal

Solving the "Infinite Refresh" Problem: Enterprise-Grade API Pagination in Power Query

The Problem: Why Your "M" Script Crashes

Memory Bloat: Power BI tries to hold every intermediate page in memory before merging.

Throttling (429 Errors): The API cuts you off because you’re hitting it too fast without a "sleep" or "retry" logic.

The Unique Solution: Functional Buffer & Conditional Backoff

Why this is different:

Memory Safety: List.Generate is a "Stream" in Power Query. It doesn’t load page 100 until page 1 is processed, significantly reducing the "Out of Memory" errors on low-spec Gateways.

Real-World Application

🚀 Stop Paying for Connectors: How to Build a Custom Power BI API Integration for Marketing Data

The Challenge

The Solution (The "M" Script)