Streaming and Pagination Patterns in M: Handling Large APIs and Multi-Page Data Sources with Custom Iterators

Introduction

You've connected Power Query to an API. The first 100 records look perfect. Then you realize the API returns data in pages, and the full dataset has 47,000 rows spread across 470 pages. You write a function, wrap it in a list, and suddenly Power Query is firing 470 individual HTTP requests — some of which fail silently, some time out, and the whole thing takes six minutes to refresh. Welcome to the real world of API pagination in M.

This is one of the most common places where Power Query projects break down in production. The concepts involved — recursion, lazy evaluation, dynamic URL construction, and cursor-based navigation — aren't difficult once you understand what's actually happening under the hood. The problem is that most tutorials stop at "here's how to get one page" and leave you to figure out the rest. This lesson doesn't do that.

By the end of this lesson, you'll be able to build robust, production-ready pagination handlers in M that work across offset-based APIs, cursor-based APIs, and link-header APIs. You'll understand why certain patterns work and others don't, how to handle rate limiting and errors gracefully, and how to structure your code so it's maintainable six months from now.

What you'll learn:

• How M's evaluation model affects API pagination strategy and why naive approaches fail at scale
• How to implement offset/page-number pagination using List.Generate as a custom iterator
• How to handle cursor-based pagination where the next page URL is embedded in the response
• How to detect and handle the last page reliably across different API conventions
• How to build a reusable, parameterized pagination wrapper you can drop into any project

---

Prerequisites

You should be comfortable with:

• Writing M functions and understanding let...in structure
• Making basic API calls with Web.Contents
• Working with records, lists, and tables in Power Query
• JSON parsing with Json.Document

If List.Transform, List.Accumulate, and recursive let bindings are unfamiliar territory, spend some time with the intermediate M language lessons first. This lesson will reference those concepts but won't re-teach them.

---

How M's Evaluation Model Shapes Your Pagination Strategy

Before writing a single line of pagination code, you need to understand something fundamental about how M evaluates expressions: M is lazy by default, but Web.Contents calls are not free.

M uses lazy evaluation for lists and queries — it doesn't compute values until they're needed. This is why you can define a list of 10,000 rows and only pay for the work when something downstream actually requests those rows. However, when you wrap HTTP calls inside a list, Power Query has to actually execute each call during refresh. There's no way to "defer" a network request.

This has a concrete implication: pagination in M is fundamentally iterative, not declarative. You can't write a single expression that magically fetches all pages in parallel. What you can do is build a controlled iterator that fetches pages sequentially, stops at the right time, and handles failures without corrupting your dataset.

The second thing to understand is query folding. When you're working with databases, Power Query tries to push transformations back to the source. With web APIs, there is no folding — every step executes in the M engine. This means your pagination logic runs entirely in Power Query's memory, and the order of operations matters. Fetching 400 pages and then filtering for a date range is always more expensive than building the date filter into your URL before you start fetching.

Finally, there's the question of what counts as "one request" in Power Query's security and caching model. Power Query caches results from Web.Contents within a session, but only under specific conditions. If your URL changes between pages (which it always does with pagination), each call is treated as a fresh request. Understanding this is why the RelativePath and Query options in Web.Contents exist — they're not just cosmetic. Using them properly enables Power Query's buffering and avoids certain credential-related errors.

---

Understanding the Three Pagination Patterns

Real-world APIs use one of three pagination approaches, and your M code needs to match the API's convention:

Offset/Page-Number Pagination: The API accepts a page or offset parameter. You request page 1, then page 2, and so on until you get an empty result or reach a total_pages count. GitHub's REST API, many SQL-backed services, and most older RESTful APIs use this pattern.

Cursor-Based Pagination: Each response includes a token (a "cursor") that you pass as a parameter to get the next page. You stop when no cursor is returned. Shopify, Twitter/X's API, and Salesforce use this pattern. It's more efficient for large datasets because the server doesn't need to count total records.

Link Header Pagination: The API returns a Link HTTP header containing the URL for the next page. GitHub's API actually uses this for some endpoints. Handling this requires parsing response headers, which is slightly trickier in M.

We'll build concrete examples for each.

---

Pattern 1: Offset-Based Pagination with `List.Generate`

List.Generate is M's built-in iterator, and it's the right tool for offset-based pagination. Before reaching for recursion, try List.Generate — it's more readable and doesn't risk hitting M's recursion depth limits on large datasets.

List.Generate takes four arguments:

1. Initial value — the starting state
2. Condition — returns true if iteration should continue
3. Next value — how to compute the next state from the current one
4. Selector — what to extract from each state (optional)

Here's the anatomy of a pagination iterator. Imagine we're hitting a hypothetical analytics API that returns event data with page-number pagination:

// Base URL: https://api.example-analytics.com/v2/events
// Parameters: page (1-based), per_page (max 100), start_date, end_date
// Response structure:
// {
//   "data": [...array of event records...],
//   "meta": {
//     "current_page": 1,
//     "total_pages": 47,
//     "total_count": 4650
//   }
// }

let
    BaseUrl = "https://api.example-analytics.com/v2/events",
    ApiKey = "your-api-key-here",
    StartDate = "2024-01-01",
    EndDate = "2024-03-31",
    PageSize = 100,

    // Function to fetch a single page and return the parsed JSON
    FetchPage = (pageNumber as number) as record =>
        let
            Response = Web.Contents(
                BaseUrl,
                [
                    Query = [
                        page = Number.ToText(pageNumber),
                        per_page = Number.ToText(PageSize),
                        start_date = StartDate,
                        end_date = EndDate
                    ],
                    Headers = [
                        Authorization = "Bearer " & ApiKey,
                        #"Content-Type" = "application/json"
                    ]
                ]
            ),
            ParsedResponse = Json.Document(Response)
        in
            ParsedResponse,

    // Use List.Generate to iterate through all pages
    AllPages = List.Generate(
        // Initial state: fetch page 1, store the full response
        () => [Page = FetchPage(1), PageNumber = 1],

        // Condition: continue if current page <= total_pages
        (state) => state[PageNumber] <= state[Page][meta][total_pages],

        // Next state: increment page number and fetch the next page
        (state) => [
            Page = FetchPage(state[PageNumber] + 1),
            PageNumber = state[PageNumber] + 1
        ],

        // Selector: extract just the data records from each page
        (state) => state[Page][data]
    ),

    // AllPages is now a list of lists — flatten it into one list
    AllRecords = List.Combine(AllPages),

    // Convert to a table
    ResultTable = Table.FromList(
        AllRecords,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
    ),

    // Expand the record column
    ExpandedTable = Table.ExpandRecordColumn(
        ResultTable,
        "Column1",
        {"event_id", "event_type", "user_id", "timestamp", "properties"},
        {"event_id", "event_type", "user_id", "timestamp", "properties"}
    )

in
    ExpandedTable

There's a subtle but important issue with the code above that trips up nearly everyone. Look at the next state step:

(state) => [
    Page = FetchPage(state[PageNumber] + 1),
    PageNumber = state[PageNumber] + 1
]

This fetches page N+1 before the condition check runs for that new state. That means on the very last iteration, after you've fetched the last real page, the "next state" step will fetch a page beyond the total — which might return an empty response or an error.

A safer pattern is to check the condition before fetching:

// Safer: carry forward the total_pages from the first response
AllPages = List.Generate(
    () => [
        PageData = FetchPage(1),
        PageNumber = 1,
        TotalPages = FetchPage(1)[meta][total_pages]  // fetch twice on page 1
    ],
    ...
)

But that fetches page 1 twice. The cleanest approach is to restructure your state so that it always carries the total page count:

AllPages = List.Generate(
    // Initial state: fetch page 1, capture total pages
    () =>
        let
            FirstPage = FetchPage(1)
        in
            [
                Data = FirstPage[data],
                PageNumber = 1,
                TotalPages = FirstPage[meta][total_pages]
            ],

    // Condition: keep going while current page <= total
    (state) => state[PageNumber] <= state[TotalPages],

    // Next state: fetch next page and update state
    (state) =>
        let
            NextPage = FetchPage(state[PageNumber] + 1)
        in
            [
                Data = NextPage[data],
                PageNumber = state[PageNumber] + 1,
                TotalPages = state[TotalPages]
            ],

    // Selector: return just the data from each state
    (state) => state[Data]
),

Now TotalPages is captured once from the first response and carried through the state without requiring any additional API calls.

Performance tip: Always use the Query parameter inside Web.Contents rather than concatenating parameters into the URL string. Power Query handles URL encoding correctly this way, and it also works better with the credential management system. Concatenating query strings manually leads to subtle encoding bugs with special characters in filter values.

---

Pattern 2: Cursor-Based Pagination

Cursor pagination is actually cleaner to implement than offset pagination because you don't need to know the total number of pages upfront. The API tells you whether there's a next page by including (or omitting) a cursor in the response.

Let's build a paginator for a Shopify-style API. Shopify's orders endpoint returns a Link header, but many APIs return cursor information in the JSON body like this:

{
  "orders": [...],
  "pagination": {
    "has_next_page": true,
    "next_cursor": "eyJsYXN0X2lkIjo0NTY3OH0="
  }
}

Here's how to handle it:

let
    BaseUrl = "https://api.yourstore.example/v1/orders",
    ApiKey = "sk_live_your_key_here",
    PageSize = 250,

    // Fetch a page. cursor is null for the first page, a string thereafter.
    FetchPage = (cursor as nullable text) as record =>
        let
            // Build query parameters conditionally
            QueryParams =
                if cursor = null
                then [limit = Number.ToText(PageSize), status = "any"]
                else [limit = Number.ToText(PageSize), status = "any", page_cursor = cursor],

            Response = Web.Contents(
                BaseUrl,
                [
                    Query = QueryParams,
                    Headers = [
                        #"X-API-Key" = ApiKey
                    ]
                ]
            )
        in
            Json.Document(Response),

    // Iterator using cursor-based state
    AllPages = List.Generate(
        // Initial state: no cursor yet, fetch the first page
        () =>
            let
                FirstPage = FetchPage(null)
            in
                [
                    Orders = FirstPage[orders],
                    NextCursor = FirstPage[pagination][next_cursor],
                    HasMore = FirstPage[pagination][has_next_page]
                ],

        // Condition: continue while there are more pages
        (state) => state[HasMore] = true,

        // Next state: use the cursor from the previous response
        (state) =>
            let
                NextPage = FetchPage(state[NextCursor])
            in
                [
                    Orders = NextPage[orders],
                    NextCursor = NextPage[pagination][next_cursor],
                    HasMore = NextPage[pagination][has_next_page]
                ],

        // Selector: return the orders list
        (state) => state[Orders]
    ),

    // Combine all pages and convert to a table
    AllOrders = List.Combine(AllPages),

    OrdersTable = Table.FromList(
        AllOrders,
        Splitter.SplitByNothing(),
        {"Order"},
        null,
        ExtraValues.Error
    ),

    ExpandedOrders = Table.ExpandRecordColumn(
        OrdersTable,
        "Order",
        {"id", "created_at", "total_price", "customer", "line_items", "fulfillment_status"},
        {"order_id", "created_at", "total_price", "customer", "line_items", "fulfillment_status"}
    )

in
    ExpandedOrders

There's a defensive issue to address here. What happens if next_cursor doesn't exist in the response on the last page? Accessing a field that doesn't exist on a record throws an error in M. Use Record.FieldOrDefault to guard against this:

NextCursor = Record.FieldOrDefault(NextPage[pagination], "next_cursor", null),
HasMore = Record.FieldOrDefault(NextPage[pagination], "has_next_page", false)

This makes the code resilient to API responses that omit fields rather than returning null for them — which is very common in practice.

Warning: Some APIs return has_next_page: false on the last page but still include a next_cursor. Others include a cursor but no flag. Read your API's documentation carefully and test the last-page response explicitly. Printing the raw JSON of the final page using Table.FromValue(Json.Document(lastPageResponse)) during development saves a lot of debugging time.

---

Pattern 3: Handling Link Header Pagination

Some APIs, including GitHub's, embed the next page URL in the HTTP Link response header rather than the JSON body. This looks like:

Link: <https://api.github.com/repos/owner/repo/issues?page=2>; rel="next", <https://api.github.com/repos/owner/repo/issues?page=8>; rel="last"

M doesn't give you direct access to HTTP response headers from Web.Contents by default, but you can request them using [ManualStatusHandling] combined with Value.Metadata. Here's how to read the Link header:

let
    // Fetch raw response with header access
    FetchPageWithHeaders = (url as text) as record =>
        let
            RawResponse = Web.Contents(
                url,
                [
                    Headers = [
                        Authorization = "token ghp_your_token_here",
                        Accept = "application/vnd.github+json"
                    ],
                    ManualStatusHandling = {200, 404, 422}
                ]
            ),
            ResponseMetadata = Value.Metadata(RawResponse),
            ResponseHeaders = ResponseMetadata[Headers],
            ParsedBody = Json.Document(RawResponse),

            // Extract the Link header value if it exists
            LinkHeader = Record.FieldOrDefault(ResponseHeaders, "Link", null),

            // Parse out the "next" URL from the Link header
            NextUrl =
                if LinkHeader = null
                then null
                else
                    let
                        // Split on comma to get individual link entries
                        LinkParts = Text.Split(LinkHeader, ","),
                        // Find the entry containing rel="next"
                        NextParts = List.Select(LinkParts, each Text.Contains(_, "rel=""next""")),
                        // If found, extract the URL from between < and >
                        NextUrl =
                            if List.IsEmpty(NextParts)
                            then null
                            else
                                let
                                    Part = List.First(NextParts),
                                    // URL is between the < and > characters
                                    Start = Text.PositionOf(Part, "<") + 1,
                                    End = Text.PositionOf(Part, ">"),
                                    Url = Text.Middle(Part, Start, End - Start)
                                in
                                    Url
                    in
                        NextUrl
        in
            [
                Data = ParsedBody,
                NextUrl = NextUrl
            ],

    // Starting URL — GitHub's issues endpoint for a repo
    StartUrl = "https://api.github.com/repos/octocat/Hello-World/issues?per_page=100&state=all",

    AllPages = List.Generate(
        () => FetchPageWithHeaders(StartUrl),
        (state) => state[NextUrl] <> null,
        (state) => FetchPageWithHeaders(state[NextUrl]),
        (state) => state[Data]
    ),

    AllIssues = List.Combine(AllPages),

    IssuesTable = Table.FromList(
        AllIssues,
        Splitter.SplitByNothing(),
        {"Issue"},
        null,
        ExtraValues.Error
    ),

    ExpandedIssues = Table.ExpandRecordColumn(
        IssuesTable,
        "Issue",
        {"number", "title", "state", "created_at", "closed_at", "user", "labels"},
        {"number", "title", "state", "created_at", "closed_at", "user", "labels"}
    )

in
    ExpandedIssues

The Value.Metadata trick is the key here. When you call Web.Contents with ManualStatusHandling, Power Query attaches the HTTP response metadata (including headers) to the binary value as metadata. Calling Value.Metadata() on that binary surfaces it as a record. From there, accessing [Headers] gives you a record where each field is an HTTP header name.

Note: Using ManualStatusHandling means Power Query won't automatically throw an error for non-200 status codes. If you use it, you should check ResponseMetadata[Response.Status] yourself and handle error codes appropriately. For a production system, wrap the entire fetch in a try...otherwise and log errors rather than failing the entire refresh.

---

Building a Reusable Pagination Function

Once you've implemented pagination more than twice, you want to stop copy-pasting and build a proper reusable function. Here's a generalized pagination wrapper that handles the most common case — offset/page-number APIs where the total page count is known from the first response:

// PaginatedFetch: A reusable pagination wrapper
// Parameters:
//   baseUrl        - The API endpoint URL (text)
//   staticParams   - Record of query parameters that don't change between pages
//   headers        - Record of HTTP headers (auth, content-type, etc.)
//   pageParam      - Name of the query parameter for the page number (text)
//   dataPath       - Function to extract the data list from a parsed response
//   totalPagesPath - Function to extract the total page count from a parsed response
//   startPage      - First page number (usually 0 or 1 depending on the API)

let
    PaginatedFetch = (
        baseUrl as text,
        staticParams as record,
        headers as record,
        pageParam as text,
        dataPath as function,
        totalPagesPath as function,
        startPage as number
    ) as list =>
        let
            FetchOnePage = (pageNum as number) as record =>
                let
                    PageParams = Record.AddField(staticParams, pageParam, Number.ToText(pageNum)),
                    Response = Web.Contents(
                        baseUrl,
                        [Query = PageParams, Headers = headers]
                    )
                in
                    Json.Document(Response),

            FirstPage = FetchOnePage(startPage),

            AllPages = List.Generate(
                () => [
                    PageData = dataPath(FirstPage),
                    PageNum = startPage,
                    TotalPages = totalPagesPath(FirstPage)
                ],
                (s) => s[PageNum] <= s[TotalPages],
                (s) =>
                    let Next = FetchOnePage(s[PageNum] + 1)
                    in [
                        PageData = dataPath(Next),
                        PageNum = s[PageNum] + 1,
                        TotalPages = s[TotalPages]
                    ],
                (s) => s[PageData]
            )
        in
            List.Combine(AllPages)

in
    PaginatedFetch

To use this function against a real API, you'd call it like this:

let
    // Load the reusable function (assuming it's stored as a query named "PaginatedFetch")
    Fetcher = PaginatedFetch,

    AllEvents = Fetcher(
        "https://api.example-analytics.com/v2/events",
        [per_page = "100", start_date = "2024-01-01", end_date = "2024-03-31"],
        [Authorization = "Bearer your-api-key"],
        "page",
        (response) => response[data],
        (response) => response[meta][total_pages],
        1
    ),

    EventsTable = Table.FromList(
        AllEvents,
        Splitter.SplitByNothing(),
        {"Event"},
        null,
        ExtraValues.Error
    )

in
    EventsTable

Storing PaginatedFetch as its own query in Power Query and referencing it from other queries is a good architectural pattern. You define the complex iteration logic once, and each API connection becomes just a few lines of configuration.

---

Handling Rate Limits and Errors in Production

A pagination loop that works perfectly in testing will eventually hit rate limits in production. APIs throttle requests, return transient 429 or 503 errors, and occasionally time out. Your pagination code needs to handle this gracefully.

M doesn't have a native sleep function, but you can work around rate limiting with a few strategies:

Strategy 1: Add a computed delay using List.Accumulate

// This forces sequential evaluation and adds a tiny computational delay
// between requests. Not a true sleep, but helps with lenient rate limits.
FetchWithDelay = (pageNum as number) =>
    let
        // Burn some CPU cycles (crude but sometimes effective)
        Delay = List.Sum(List.Generate(() => 0, each _ < 1000, each _ + 1)),
        Result = FetchOnePage(pageNum)
    in
        Result

This is a hack, not a real solution. For APIs with strict rate limits, a better approach is to batch your requests into smaller chunks and schedule refreshes accordingly.

Strategy 2: Wrap fetches in error handling

SafeFetch = (pageNum as number) as record =>
    let
        Result = try FetchOnePage(pageNum)
    in
        if Result[HasError]
        then
            [
                data = {},
                _error = Result[Error][Message],
                _page = pageNum
            ]
        else
            Result[Value]

This returns an empty data list and an error field when a page fails, rather than crashing the entire refresh. You can then filter out error records and log them separately.

Strategy 3: Validate your data volume before looping

Before starting a pagination loop, check if the total record count is reasonable:

FirstPage = FetchOnePage(1),
TotalRecords = FirstPage[meta][total_count],
Guard =
    if TotalRecords > 500000
    then error "Dataset too large: " & Number.ToText(TotalRecords) & " records. Apply filters."
    else TotalRecords,

This prevents accidental runaway queries from hammering an API for hours.

---

Hands-On Exercise

Build a paginated GitHub repository issue tracker

Your task is to build a Power Query solution that fetches all open and closed issues from a public GitHub repository, handles pagination, and returns a clean table with calculated fields.

Step 1: Create a new Power Query query named GitHubIssues. Use the Link header pagination pattern from Pattern 3 above, pointing at https://api.github.com/repos/microsoft/vscode/issues?per_page=100&state=all. (This is a public repo — no auth token needed for reading, though unauthenticated requests are rate-limited to 60/hour.)

Step 2: After expanding the issue records, add these calculated columns:

• days_open — the number of days between created_at and either closed_at (if closed) or today (if still open)
• label_count — the number of labels on each issue (the labels field is a list)
• is_bug — a boolean indicating whether any label has "name" equal to "bug"

// Adding days_open after expanding
WithDaysOpen = Table.AddColumn(
    ExpandedIssues,
    "days_open",
    (row) =>
        let
            StartDate = DateTime.From(DateTimeZone.From(row[created_at])),
            EndDate =
                if row[state] = "closed" and row[closed_at] <> null
                then DateTime.From(DateTimeZone.From(row[closed_at]))
                else DateTime.LocalNow()
        in
            Duration.Days(EndDate - StartDate),
    Int64.Type
),

// Adding label_count
WithLabelCount = Table.AddColumn(
    WithDaysOpen,
    "label_count",
    (row) => List.Count(row[labels]),
    Int64.Type
),

// Adding is_bug flag
WithBugFlag = Table.AddColumn(
    WithLabelCount,
    "is_bug",
    (row) =>
        List.AnyTrue(
            List.Transform(
                row[labels],
                (label) => Record.FieldOrDefault(label, "name", "") = "bug"
            )
        ),
    type logical
)

Step 3: Filter the result to only show issues with days_open > 30 and save the query. Verify the row count matches what you'd expect from the GitHub UI.

Bonus challenge: Parameterize the query so that owner, repo, state, and min_days_open are all parameters defined at the top of the query, making it reusable for any GitHub repository.

---

Common Mistakes & Troubleshooting

Mistake 1: Fetching page 0 when the API starts at page 1 (or vice versa)

This is the most common off-by-one error. If your API is 1-indexed and you start at 0, you'll get the second page first and the first page never. Always read the API docs for whether pagination is 0-based or 1-based, and always test by checking that your first page's data matches a direct API call in a browser or Postman.

Mistake 2: The iterator runs forever

If your condition never returns false, List.Generate will run until Power Query exhausts memory or times out. This usually happens when total_pages is misread from the response — for example, reading total_count (the record count) instead of total_pages (the page count). Add a maximum page guard:

(state) => state[PageNum] <= state[TotalPages] and state[PageNum] <= 500

The and state[PageNum] <= 500 is a hard safety limit. Remove it once you're confident the logic is correct.

Mistake 3: Duplicate records on page boundaries

Some APIs are buggy and return the last record of page N as the first record of page N+1. After List.Combine, run Table.Distinct on your primary key column to deduplicate. This is a cheap operation relative to the cost of fetching all those pages.

Mistake 4: Dynamic URL building triggers credential errors

When Power Query can't determine which credentials apply to a URL (because the URL is being built dynamically at runtime), it may throw a DataSource.Error about credentials. The fix is to use the BaseUrl and RelativePath / Query parameters in Web.Contents rather than concatenating URLs, and to make sure the base URL is set as the data source credential.

// Correct: Power Query knows the credential applies to BaseUrl
Web.Contents(BaseUrl, [Query = [page = "2", ...]])

// Problematic: credential matching may fail on dynamically built URLs
Web.Contents(BaseUrl & "?page=2&...")

Mistake 5: Not accounting for empty results

If a date range or filter returns zero records, some APIs return {"data": [], "meta": {"total_pages": 0}} and others return {"data": [], "meta": {"total_pages": 1}}. Both are valid responses, but your condition needs to handle both:

(state) => state[PageNum] <= Number.Max(state[TotalPages], 1)
// This ensures you always process at least the first page
// and stop immediately if it's empty

Actually, the cleaner fix is to check whether data is empty in the condition:

(state) => state[PageNum] <= state[TotalPages] and List.Count(state[PageData]) > 0

Mistake 6: Power Query refreshes the first page multiple times

This happens when you reference FetchPage(1) more than once in your let block. M may re-evaluate it each time. Capture the first page result in a named binding (FirstPage = FetchPage(1)) and reference FirstPage everywhere rather than calling FetchPage(1) inline in multiple places.

---

Summary & Next Steps

You now have a complete toolkit for handling paginated APIs in Power Query. Let's recap the key ideas:

The mental model: Pagination in M is iterative, not declarative. Use List.Generate to build a controlled state machine that fetches one page, updates its state, and stops cleanly.

Pattern selection: Match your M pattern to the API's pagination style. Offset APIs → List.Generate with page-number state. Cursor APIs → List.Generate with cursor state and null as the terminal signal. Link header APIs → parse Value.Metadata response headers to extract the next URL.

Production resilience: Wrap fetches in try...otherwise, guard against runaway loops, validate data volume before iterating, and always use Record.FieldOrDefault when accessing fields that might be absent.

Reusability: Abstract your pagination logic into a parameterized function stored as its own query. API-specific queries then become thin wrappers that provide configuration, not logic.

For your next steps, consider these directions:

• Add incremental refresh: Combine pagination with Power Query's incremental refresh policies to only fetch new data since the last load. This is especially powerful for high-volume APIs.
• Parallel pagination with known page counts: When you know total page count upfront and the API doesn't have strict rate limits, you can use List.Transform over a list of page numbers to build requests in parallel. This is more complex but significantly faster.
• Explore the OData.Feed function: If you're working with OData-compliant APIs, Power Query has a built-in connector that handles pagination automatically. Understanding manual pagination makes you appreciate — and debug — OData connector behavior much more effectively.
• Error logging to a staging table: Build a pattern where failed page fetches write to an error log table rather than silently dropping records. This is essential for auditable data pipelines.

The ability to wrangle any paginated API in M is one of those skills that compounds over time. Every new API you connect to gets easier as your mental model of these patterns solidifies. The code looks complex at first, but once you can see it as a state machine with a start, a condition, and a transition, the structure becomes second nature.