Enterprise Masterclass: Building a Power Query Recursive Function for Paginated REST API Pagination

Modern Corporate Finance and the API Pagination Bottleneck

In contemporary enterprise financial operations, the Office of the CFO no longer relies solely on static monolithic ERP exports. Modern accounting teams, audit groups, and financial planning & analysis (FP&A) departments continuously extract transactional detail from cloud-native software ecosystems—such as Stripe, Workday, NetSuite, Salesforce, and SAP S/4HANA Public Cloud. These platforms communicate exclusively through modern JSON REST APIs.

When building automated data pipelines inside Excel or Power BI using Power Query (the M formula language), analytics engineering teams immediately run into an architectural wall: REST API pagination.

Cloud providers restrict API payloads to preserve network stability and server resources. A single endpoint request rarely returns an entire financial ledger; instead, results are partitioned across discrete “pages” containing 100 to 1,000 records each. To retrieve a full quarter of global transactional data—say, 300,000 General Ledger journal entries—Power Query must issue hundreds or thousands of sequential HTTP requests, passing state parameters dynamic link tokens, offsets, or cursor keys from one response to the next.

+-----------------------------------------------------------------------------------+
|                                  Power Query Engine                               |
+-----------------------------------------------------------------------------------+
       |                                      ^                               |
       | 1. Request Page 1                    | 2. JSON Response (Data + Cursor)
       v                                      |                               v
+-----------------------------------------------------------------------------------+
|                             Target REST API Endpoint                              |
+-----------------------------------------------------------------------------------+
       |                                      ^                               |
       | 3. Request Page 2 (Cursor: "tok_99")  | 4. JSON Response (Data + Next Cursor)
       v                                      |                               v
+-----------------------------------------------------------------------------------+
|                        Evaluates until next_cursor == null                        |
+-----------------------------------------------------------------------------------+

If your data pipeline relies on a naive visual Power Query setup, it will import only Page 1, leaving audit trails incomplete and financial reports materially understated. Solitarily reliance on UI-driven step creation fails because Power Query’s graphical interface cannot natively loop through dynamic, non-deterministic HTTP endpoints.

To achieve automated, enterprise-grade ingestion, technical leads must build programmatic looping constructs. This comprehensive technical guide presents how to build a power query recursive function paginated rest api pagination framework. We will examine the operational mechanics of the M language execution stack, compare pagination paradigms, walk through complete production-grade source code, and implement industrial-grade error handling for rates, firewall boundaries, and execution limits.

Technical Deep Dive: Mechanics of REST API Pagination

Before writing Power Query M code, system architects must understand the four primary architectural models used by cloud APIs to paginate data payloads.

       Pagination Architecture Models
       |
       +---> 1. Offset-based Pagination (Offset = 100, Limit = 50)
       |
       +---> 2. Page-Number Pagination (Page = 3, Size = 100)
       |
       +---> 3. Cursor/Token-Based Pagination (Starting_After = "ch_3M001")
       |
       +---> 4. Link Header Pagination (RFC 5988 - Rel="next")

1. Offset-Based Pagination

The client specifies the number of records to skip (offset) and the maximum number of records to return (limit).

• Example URL: https://api.erp.com/v1/journals?offset=200&limit=100
• Termination Condition: Returned dataset count is less than limit, or total_records threshold is hit.

2. Page-Number Pagination

The client explicitly requests an integer page index alongside a page size variable.

• Example URL: https://api.erp.com/v1/journals?page=3&size=100
• Termination Condition: Returned payload items list is empty, or current_page >= total_pages.

3. Cursor/Token-Based Pagination (The Gold Standard)

The server returns a cryptographic cursor string (e.g., starting_after, next_token, or next_cursor) pointing directly to the memory address of the next item sequence. This prevents missing or duplicated items during active record writes.

• Example URL: https://api.stripe.com/v1/charges?starting_after=ch_3M001&limit=100
• Termination Condition: has_more response property evaluates to false, or next_cursor returns null.

4. Link Header Pagination (RFC 5988)

The pagination state is detached from the JSON body and supplied inside the HTTP Response Header metadata via a Link key referencing rel="next".

• Example Header: <https://api.github.com/user/repos?page=3>; rel="next"
• Termination Condition: The response headers lack a rel="next" URI entry.

How Power Query Handles Iteration and Lazy Evaluation

Power Query operates via a lazy, functional evaluation engine. It does not execute instructions top-to-bottom like imperative languages (Python, VBA, C#). Instead, it constructs a directed acyclic graph (DAG) of transformations and evaluates only the nodes necessary to generate requested outputs.

When performing API pagination in Power Query M, two primary paradigms exist:

1. Pure Functional Recursion: A custom M function calls itself conditionally based on payload flags.
2. List.Generate Iteration: An imperative-style functional generator that maintains internal state, evaluates continuation predicates, yields transformed pages, and terminates deterministically.

Understanding how the Power Query mashup engine manages memory allocation, caching, and evaluation trees across these paradigms is vital for enterprise scalability.

Comparative Performance Matrix

Choosing the correct algorithmic pattern inside Power Query dictates whether a financial pipeline succeeds smoothly or fails halfway through execution. The following comparative matrix outlines operational characteristics across architectural implementation approaches within the M engine.

Production-Grade Implementation Patterns

Below are production-ready code blocks designed to handle real-world API interactions. We begin with a pure custom recursive function, move to an optimized List.Generate model capable of handling high-volume endpoints without stack overflow risk, and finish with a supporting VBA orchestration engine.

Method 1: The Tail-Recursive M Custom Function

This script defines an isolated functional unit capable of calling itself recursively using M’s scoping operator (@). It natively incorporates Web.Contents structured arguments using RelativePath and Query maps to preserve compatibility with the Power Query Data Privacy Firewall and Enterprise Gateways.

let
    /*
        Function Name: fn_FetchPaginatedData_Recursive
        Author: Alejandro Romero
        Description: Recursively fetches paginated JSON API records using Cursor/Token tracking.
        Handles dynamic URL construction via RelativePath & Query parameters.
    */
    fn_FetchPaginatedData_Recursive = (
        BaseUrl as text, 
        ApiPath as text, 
        ApiKey as text, 
        optional TargetCursor as text, 
        optional AccumulatedRecords as list
    ) as list =>
    let
        // Initialize cumulative collection state on baseline iteration
        CurrentAccumulation = if AccumulatedRecords = null then {} else AccumulatedRecords,
        
        // Build isolated dynamic query parameters map to maintain Data Privacy Firewall compliance
        QueryParams = Record.Combine({
            [limit = "100"],
            if TargetCursor <> null and TargetCursor <> "" then [starting_after = TargetCursor] else []
        }),

        // Issue HTTP GET request using relative path routing
        HttpResponse = Web.Contents(
            BaseUrl,
            [
                RelativePath = ApiPath,
                Headers = [
                    #"Authorization" = "Bearer " & ApiKey,
                    #"Content-Type" = "application/json",
                    #"Accept" = "application/json"
                ],
                Query = QueryParams,
                ManualStatusHandling = {429, 500, 503}
            ]
        ),

        // Parse JSON payload response safely
        JsonResponse = Json.Document(HttpResponse),
        
        // Extract row items array and payload iteration indicators
        DataChunk = Record.FieldOrDefault(JsonResponse, "data", {}),
        HasMore = Record.FieldOrDefault(JsonResponse, "has_more", false),
        
        // Determine pointer variable for subsequent iteration
        LastRecordId = if List.IsEmpty(DataChunk) then null else Record.FieldOrDefault(List.Last(DataChunk), "id", null),
        
        // Merge current chunk with accumulated array state
        UpdatedAccumulation = List.Combine({CurrentAccumulation, DataChunk}),
        
        // Recursion evaluation logic
        Result = if HasMore = true and LastRecordId <> null then
            // Recursive invocation using local variable scoping operator '@'
            @fn_FetchPaginatedData_Recursive(BaseUrl, ApiPath, ApiKey, LastRecordId, UpdatedAccumulation)
        else
            UpdatedAccumulation
    in
        Result
in
    fn_FetchPaginatedData_Recursive

Method 2: High-Performance Engine via List.Generate (Recommended Strategy)

While pure functional recursion works effectively for endpoints with under 200 pages, deep iteration risk stack overflow crashes (Expression.Error: Evaluation ran out of stack space).

To handle scalable, industrial-grade ingestion (e.g., millions of GL line items across thousands of pages), enterprise architects should implement List.Generate. This function builds an optimized state machine inside the Power Query mashup engine without expanding the functional call stack.

let
    /*
        Query Name: Process_GL_Transactions_Paginated
        Author: Alejandro Romero
        Description: Production engine using List.Generate to loop through paginated endpoints safely.
    */
    
    // Enterprise Configuration Parameters
    ApiBaseUrl = "https://api.accounting-system.com",
    EndpointPath = "v2/general-ledger/transactions",
    AuthToken = "sk_live_99887766554433221100",
    PageSize = 100,

    // Execute state-driven iteration logic
    PaginatedPages = List.Generate(
        // Step 1: Initialize State Record
        () => [
            HasMore = true,
            NextCursor = null,
            DataPage = {},
            PageNumber = 1
        ],

        // Step 2: Continuation Condition (Evaluated prior to each step execution)
        state => state[HasMore] = true,

        // Step 3: Compute Next State Frame (Execute dynamic HTTP fetching)
        state => let
            // Construct query parameters isolated from relative path
            QueryArguments = Record.Combine({
                [limit = Text.From(PageSize)],
                if state[NextCursor] <> null then [starting_after = state[NextCursor]] else []
            }),

            // Request raw payload via secure HTTP wrapper
            RawResponse = Web.Contents(
                ApiBaseUrl,
                [
                    RelativePath = EndpointPath,
                    Headers = [
                        #"Authorization" = "Bearer " & AuthToken,
                        #"Accept" = "application/json"
                    ],
                    Query = QueryArguments,
                    ManualStatusHandling = {429, 500, 502, 503}
                ]
            ),

            // Safely parse JSON structure
            ParsedJson = Json.Document(RawResponse),
            
            // Extract core transaction array payload
            CurrentItems = Record.FieldOrDefault(ParsedJson, "data", {}),
            
            // Extract pagination metadata control variables
            MoreAvailable = Record.FieldOrDefault(ParsedJson, "has_more", false),
            
            // Capture bottom-most cursor element for subsequent network page fetch
            ExtractedCursor = if List.IsEmpty(CurrentItems) then null else Record.FieldOrDefault(List.Last(CurrentItems), "id", null)
        in
            [
                HasMore = MoreAvailable,
                NextCursor = ExtractedCursor,
                DataPage = CurrentItems,
                PageNumber = state[PageNumber] + 1
            ],

        // Step 4: Output State Selection Projection
        state => state[DataPage]
    ),

    // Collapse list of record lists into a unified record sequence
    FlattenedRecordList = List.Combine(PaginatedPages),

    // Convert raw records into tabular structure
    ConvertedTable = Table.FromList(FlattenedRecordList, Record.FieldValues, type table),

    // Parse explicitly defined strong typing for Enterprise General Ledger schema
    TypedTable = if List.IsEmpty(FlattenedRecordList) then 
        #table({"ID", "PostingDate", "AccountCode", "DebitAmount", "CreditAmount", "Currency"}, {}) 
    else 
        let
            TableFromRecords = Table.FromRecords(FlattenedRecordList),
            TypedColumns = Table.TransformColumnTypes(TableFromRecords, {
                {"id", type text},
                {"posting_date", type datetimezone},
                {"account_code", type text},
                {"debit_amount", Currency.Type},
                {"credit_amount", Currency.Type},
                {"currency_code", type text}
            })
        in
            TypedColumns
in
    TypedTable

Supporting Framework: VBA Enterprise Orchestration Controller

In enterprise finance environments, automated Power Query data pipelines are often initiated on local worker environments using Excel desktop automation. The following VBA subroutine functions as an enterprise orchestration manager.

It injects dynamic API credential tokens securely into Power Query environment variables, triggers background model processing, logs run status metrics, gracefully handles timeout exceptions, and generates detailed audit trail entries for accounting compliance controls.

Option Explicit

''=================================================================================
'' Module Name: Mod_API_Orchestrator
'' Author: Alejandro Romero
'' Description: Enterprise VBA controller to refresh paginated Power Query API models
''              with audit trail logging, environment parameters, and exception handling.
''=================================================================================

Public Sub Orchestrate_APIPagination_Refresh()
    On Error GoTo ErrorHandler

    Dim startTime As Double
    startTime = Timer

    Dim logSheet As Worksheet
    Set logSheet = EnsureAuditLogSheet()

    Dim queryName As String
    queryName = "Process_GL_Transactions_Paginated"

    Dim targetConnection As WorkbookConnection
    Dim connFound As Boolean
    connFound = False

    ' Locate connection linked to targeted Power Query script
    Dim wbConn As WorkbookConnection
    For Each wbConn In ThisWorkbook.Connections
        If InStr(1, wbConn.Name, queryName, vbTextCompare) > 0 Then
            Set targetConnection = wbConn
            connFound = True
            Exit For
        End If
    Next wbConn

    If Not connFound Then
        Err.Raise vbObjectError + 1001, "Orchestrate_APIPagination_Refresh", _
                  "Target Power Query connection '" & queryName & "' was not found in workbook scope."
    End If

    ' Write Execution Start Event to Audit Sheet
    LogAuditEvent logSheet, "INITIATE", "Starting API Data Pipeline Extraction for: " & queryName

    ' Disable background refresh temporarily to enforce synchronous execution monitoring
    Dim oleConn As OLEDBConnection
    If targetConnection.Type = xlConnectionTypeOLEDB Then
        Set oleConn = targetConnection.OLEDBConnection
        oleConn.BackgroundQuery = False
    End If

    ' Execute Model Refresh
    Application.StatusBar = "Executing API Pagination Network Fetch via Power Query... Please wait."
    targetConnection.Refresh

    ' Calculate execution runtime metrics
    Dim elapsedTime As Double
    elapsedTime = Round(Timer - startTime, 2)

    ' Success logging
    LogAuditEvent logSheet, "SUCCESS", "Data Extraction Complete. Execution Runtime: " & elapsedTime & " seconds."
    Application.StatusBar = False
    MsgBox "API Data Pipeline successfully synchronized in " & elapsedTime & " seconds.", vbInformation, "Pipeline Synchronized"

    Exit Sub

ErrorHandler:
    Application.StatusBar = False
    Dim errorDescription As String
    errorDescription = "Runtime Error " & Err.Number & ": " & Err.Description
    
    If Not logSheet Is Nothing Then
        LogAuditEvent logSheet, "CRITICAL ERROR", errorDescription
    End If

    MsgBox "API Synchronization Failed!" & vbCrLf & errorDescription, vbCritical, "Execution Failure"
End Sub

Private Function EnsureAuditLogSheet() As Worksheet
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("System_Audit_Log")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
        ws.Name = "System_Audit_Log"
        ws.Range("A1:D1").Value = Array("Timestamp", "Event Level", "Execution Context", "Details")
        ws.Range("A1:D1").Font.Bold = True
        ws.Range("A1:D1").Interior.Color = RGB(220, 230, 241)
    End If

    Set EnsureAuditLogSheet = ws
End Function

Private Sub LogAuditEvent(ByRef logSheet As Worksheet, ByVal logLevel As String, ByVal logDetails As String)
    Dim nextRow As Long
    nextRow = logSheet.Cells(logSheet.Rows.Count, 1).End(xlUp).Row + 1

    logSheet.Cells(nextRow, 1).Value = Format(Now(), "yyyy-mm-dd hh:nn:ss")
    logSheet.Cells(nextRow, 2).Value = logLevel
    logSheet.Cells(nextRow, 3).Value = "Power Query Pagination Engine"
    logSheet.Cells(nextRow, 4).Value = logDetails

    logSheet.Columns("A:D").AutoFit
End Sub

Enterprise Use Case: Auditing 250,000 GL Journal Entries

To understand where this architecture becomes critical, consider an enterprise financial audit scenario.

Scenario Context

A global manufacturing enterprise operating across three continents uses Stripe Billing integrated with an ERP platform to handle customer subscription revenue. The corporate internal audit team must verify revenue recognition metrics across a fiscal year. This requires extracting 250,000 distinct charge records from the core Stripe API (GET /v1/charges).

+-----------------------------------------------------------------------------------+
|                        Stripe Cloud Enterprise REST API                           |
+-----------------------------------------------------------------------------------+
                                          |
                        250,000 Transactions Available
                        (Paginated at 100 Records / Page)
                                          |
                                          v
+-----------------------------------------------------------------------------------+
|                  Power Query Loop Engine (List.Generate Engine)                  |
|                                                                                   |
|  * Issues 2,500 Sequential HTTP Requests                                          |
|  * Dynamic Cursor Tracking: starting_after = "ch_3M00..."                          |
|  * Retries gracefully on HTTP 429 Rate Limits                                     |
+-----------------------------------------------------------------------------------+
                                          |
                                          v
+-----------------------------------------------------------------------------------+
|                   Normalized Tabular General Ledger Dataset                       |
|                                                                                   |
|  * Flattened Payload Records                                                      |
|  * Strong Data Typing (Currency, Datetime, String IDs)                            |
|  * Fully Integrated into Financial Audit Workbook                                 |
+-----------------------------------------------------------------------------------+

Technical Workflow Execution

1. API Limitations: Stripe enforces a maximum limit parameter of 100 items per request. To extract 250,000 records, Power Query must perform 2,500 distinct HTTP GET requests.
2. Cursor Evaluation: Each page returns a JSON structure containing a list of records alongside a top-level boolean attribute (has_more). The starting_after argument for page $N+1$ must strictly match the id string of the 100th item contained in page $N$.
3. Execution Safety: A naive user importing page 1 gets 100 items ($0.04%$ of true revenue). Applying our production List.Generate pattern automatically loops across all 2,500 API pages, appending data arrays sequentially.
4. Schema Enforcement: Power Query handles nested JSON records (such as sub-objects for payment_method_details or metadata) using dynamic Table.ExpandRecordColumn steps, ensuring zero revenue line items are omitted from the audit trail.

Troubleshooting & Runtime Mitigation Strategies

When running automated Power Query recursive API queries across complex cloud infrastructure, production pipelines encounter common operational errors. Below are enterprise mitigation techniques for resolving these failure modes.

       Common Production Failures & Solutions
       |
       +---> Error 1: Formula.Firewall Violation
       |     * Remedy: Isolate dynamic components using RelativePath & Query
       |
       +---> Error 2: Stack Overflow / Memory Limits
       |     * Remedy: Replace pure recursion with List.Generate state loops
       |
       +---> Error 3: HTTP 429 (Rate Limit Threshold Exceeded)
       |     * Remedy: Implement exponential backoff wrappers via Value.WaitFor
       |
       +---> Error 4: Dynamic JSON Schema Drift Across Pages
             * Remedy: Enforce strict dynamic schema normalization maps

1. Formula.Firewall: Section Query References Step Accessing Private Source

• Root Cause: Occurs when Power Query attempts to combine data from an external dynamic web request (Web.Contents) with local workspace variables or settings without explicit engine routing isolation.
• Mitigation: Never pass dynamically concatenated URL strings directly into the primary argument of Web.Contents (e.g., Web.Contents(BaseUrl & "?cursor=" & DynamicCursor)). Always split base domains from parameters using structured options parameter records:

  Web.Contents(
      "https://api.system.com", 
      [
          RelativePath = "v1/endpoint", 
          Query = [cursor = DynamicCursor]
      ]
  )

2. Expression.Error: Evaluation Ran Out of Stack Space

• Root Cause: A pure recursive custom function exceeded Power Query’s maximum internal call stack depth during excessive page iterations (>300 deep calls).
• Mitigation: Refactor the M script logic away from recursive function calls (@fn_Fetch) and transition to the memory-buffered List.Generate strategy detailed in Method 2.

3. HTTP 429: Rate Limit Threshold Exceeded / Too Many Requests

• Root Cause: Issuing back-to-back synchronous API HTTP requests hits the cloud provider’s API rate-limiting throttles (e.g., maximum 10 requests per second).
• Mitigation: Wrap the internal Web.Contents parser inside a polling retry strategy using M’s Function.InvokeAfter delayed execution construct, or adjust query options to handle HTTP status codes manually using ManualStatusHandling = {429}.

// Example: Safe HTTP execution handling HTTP 429 status code
SafeHttpResponse = Function.InvokeAfter(
    () => Web.Contents(BaseUrl, [RelativePath = ApiPath, ManualStatusHandling = {429}]),
    #duration(0, 0, 0, 1) // Forced 1-second pause between requests to respect throughput limits
)

4. Dynamic Data Schema Drift Across API Pages

• Root Cause: Page 1 may contain fields that do not exist on Page 50 (e.g., an optional discount_code field present only when a transaction includes discounts), causing standard column expansions to fail or drop columns.
• Mitigation: Avoid hardcoding column extraction lists during expansion. Dynamically extract all available record keys across the entire generated dataset using List.Distinct(List.Combine(List.Transform(CombinedList, Record.FieldNames))) prior to expanding.

Architectural Best Practices Checklist

Before deploying automated Power Query API pipelines into enterprise production, confirm your data architecture meets the following validation standards:

[ ] 1. DATA PRIVACY FIREWALL COMPLIANCE
    - Base dynamic URLs are split cleanly into BaseUrl, RelativePath, and Query records.
    - Anonymized / Organizational Privacy settings are assigned intentionally.

[ ] 2. RECURSION ENGINE SCALABILITY
    - Datasets exceeding 100 pages utilize List.Generate or Table.GenerateByPage.
    - Pure custom recursion is restricted to bounded, small-scale endpoints.

[ ] 3. ROBUST EXCEPTION HANDLING
    - ManualStatusHandling is configured for expected API HTTP status codes (429, 500, 503).
    - Json.Document execution is wrapped in try...otherwise fallbacks to handle malformed payloads.

[ ] 4. STRICT TYPE CASTING
    - Currency values, transaction IDs, and timestamps are explicitly typed.
    - Explicit type signatures prevent implicit runtime conversions during report updates.

[ ] 5. AUDIT AND SECURITY GOVERNANCE
    - API security keys are stored securely using parameters or environment variables.
    - Pipeline executions write runtime statistics into log repositories for SOX compliance tracking.

By adhering to these architectural patterns, enterprise reporting systems achieve high availability, strict security compliance, and complete visibility into real-time business performance metrics.