How to Configure Power Query Custom Functions, Dynamic Parameters, and API Paginated Ingestion in Excel

Modern business intelligence analysts and financial modellers frequently extract enterprise data from REST APIs—such as Stripe, Jira, GitHub, or internal ERP endpoints—directly into Microsoft Excel. However, standard REST endpoints enforce pagination limits (returning 50 to 250 records per HTTP payload) to conserve bandwidth. Attempting to manually ingest multi-page JSON feeds by copying and pasting URLs or generating dozens of static queries is fragile and unscalable. Microsoft Excel solves this through Power Query M Code Custom Functions and recursive pagination generators.

Operating inside Excel’s native Mashup Engine, Power Query’s functional language (M) allows developers to encapsulate API authentication, dynamic parameters, and payload parsing into parameterized custom functions. By chaining custom functions with List.Generate, Power Query automates cursor-based and offset-based pagination, ingesting thousands of records across hundreds of API pages into a unified, auto-refreshing Excel table while strictly respecting Formula Firewall boundaries.

Architectural Overview: The Power Query Mashup Engine and Dynamic Web Sources

Power Query evaluates data ingestion scripts lazily through a directed acyclic graph (DAG):

Component	Technical Implementation	Operational Function
M Engine Execution	Lazy Evaluation DAG	Evaluates steps only when requested by the final output table, optimising memory footprint.
Web.Contents Options	RelativePath & Query Records	Separates the static base URL from dynamic query parameters, preventing dynamic data source firewall blocks.
Custom M Function	(param as type) as table => ...	Parameterized query block that accepts variable page numbers or cursor tokens and returns structured tables.
List.Generate Loop	Recursive State Machine	Iteratively evaluates API pages until a termination condition is met (e.g. empty results or null next-cursor).
Formula Firewall	Privacy Isolation Partitions	Isolates queries of differing privacy levels (Public, Organizational, Private) to prevent unauthorized data leakage.

Avoiding the Dynamic Data Source Firewall Error

A common mistake in Power Query is concatenating URLs inside Web.Contents("https://api.domain.com/v1/orders?page=" & Text.From(PageNum)). The Power Query Mashup Engine refuses to evaluate dynamically constructed root URLs for security reasons, throwing the error: “Formula.Firewall: Query references other queries or steps, so it may not directly access a data source.”

The solution is declaring the static root in Web.Contents and passing parameters via RelativePath and Query:

# Correct non-dynamic declaration compliant with Formula Firewall
Source = Json.Document(
    Web.Contents(
        "https://api.enterprise.co.uk",
        [
            RelativePath = "v1/transactions",
            Query = [
                page = Text.From(PageNumber),
                limit = "100"
            ],
            Headers = [
                Authorization = "Bearer " & ApiKey,
                Accept = "application/json"
            ]
        ]
    )
)

Writing a Reusable Paginated API Custom Function in M

Follow these steps to create an M custom function that ingests a single page of JSON records and formats it into a typed tabular structure.

Step 1: Open the Power Query Advanced Editor

1. In Microsoft Excel, navigate to the Data tab.
2. Click Get Data > From Other Sources > Blank Query.
3. On the Home tab of the Power Query Editor, click Advanced Editor.

Step 2: Input the Function Definition

Replace existing text with the following parameterized M script:

(PageNumber as number) as table =>
let
    BaseUrl = "https://api.enterprise.co.uk",
    Response = Web.Contents(
        BaseUrl,
        [
            RelativePath = "v1/invoices",
            Query = [
                page = Text.From(PageNumber),
                per_page = "100"
            ],
            Headers = [
                #"Authorization" = "Bearer sec_live_998877665544",
                #"Accept" = "application/json"
            ]
        ]
    ),
    Json = Json.Document(Response),
    Data = Json[data],
    ConvertedToTable = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedRecord = Table.ExpandRecordColumn(
        ConvertedToTable, 
        "Column1", 
        {"id", "amount", "currency", "status", "created_at"}, 
        {"Invoice_ID", "Amount", "Currency", "Status", "Created_At"}
    )
in
    ExpandedRecord

Click Done. In the left query pane, rename this query to fnGetInvoicePage. It converts into a recognized function icon (fx).

Automating Pagination Using List.Generate

Now create a secondary master query that calls fnGetInvoicePage repeatedly across pages until an empty page signals the end of the dataset.

Step 1: Create the Master Ingestion Query

1. In Power Query Editor, click New Source > Other Sources > Blank Query.
2. Open the Advanced Editor and paste the recursive generator:

let
    // Iterates starting at page 1, continuing while table is non-empty, incrementing by 1
    AllPages = List.Generate(
        () => [Page = 1, Data = try fnGetInvoicePage(1) otherwise null],
        each [Data] <> null and Table.RowCount([Data]) > 0,
        each [Page = [Page] + 1, Data = try fnGetInvoicePage([Page] + 1) otherwise null],
        each [Data]
    ),
    // Consolidates list of tables into a single unified table
    UnifiedTable = Table.Combine(AllPages),
    // Enforce strict data types
    TypedTable = Table.TransformColumnTypes(UnifiedTable, {
        {"Invoice_ID", type text},
        {"Amount", Currency.Type},
        {"Currency", type text},
        {"Status", type text},
        {"Created_At", type datetime}
    })
in
    TypedTable

Step 2: Close and Load to Excel Worksheet

1. Rename the query to All_Invoices_Master.
2. Click Close & Load on the Home ribbon.
3. PowerQuery executes the recursive loop, downloads all pages in sequence, and loads the combined dataset into a structured Excel table.

Whenever new invoices are generated in the remote system, clicking Data > Refresh All in Excel re-runs the pagination pipeline and appends new records automatically.

Troubleshooting Power Query API Pagination Errors

Consult this diagnostic reference table to resolve authentication failures, firewall violations, and infinite pagination loops:

Issue	Underlying Root Cause	Remediation Procedure
Formula.Firewall: Query references other queries or steps	Dynamic URL string concatenation used inside Web.Contents, triggering cross-partition security blocks.	Refactor URL construction: declare the root domain statically in Web.Contents and route path and query parameters via [RelativePath=..., Query=...].
Query runs indefinitely without completing (infinite loop).	Termination condition in List.Generate failed to detect the final empty page or API returns HTTP 200 with [].	Check Table.RowCount([Data]) > 0 in the condition clause. For APIs returning a total page count header, cap iteration using [Page] <= MaxPages.
DataFormat.Error: We found extra characters at the end of the JSON input.	API returned an unhandled HTTP 429 (Rate Limit) or 500 error formatted as HTML rather than JSON.	Inspect raw response via Web.Contents(..., [ManualStatusHandling={429, 500}]) and implement retry-after delay logic or throttle request frequency.
Scheduled workbook refresh fails in Power BI Service / Excel Online.	OAuth2 or Bearer tokens embedded directly into M query code without Web API credentials configured.	Move API keys to the Power Query Data Source Credentials settings: select Edit Permissions > Web API Key rather than hardcoding tokens in code.

Architecting custom Power Query M functions and recursive pagination engines enables finance and operations teams to automate complex REST API data pipelines directly into Excel, achieving enterprise data warehouse integration without external ETL middleware.