Paginated REST APIs in Power Query That Actually Refresh in the Service

Most Power Query pagination tutorials work great in Desktop and break the moment you publish to the Service. The culprit is almost always the same: a dynamic URL in the first argument of Web.Contents.

This post walks through a complete, production-ready pattern for consuming cursor-based paginated REST APIs in Power Query. The code refreshes in Power BI Service without the "dynamic data sources aren't supported" error.

The Problem

Here's the pagination pattern you'll find in most tutorials and forum answers:

text
// Breaks in Power BI Service
Web.Contents(nextUrl)

Where nextUrl is a variable - maybe it comes from parsing the previous API response, or it's assembled by string concatenation:

text
Web.Contents("https://api.example.com/v2/accounts?page[size]=100&cursor=" & nextCursor)

Both fail for the same reason. Power BI Service runs static analysis on your M code to identify data sources and map them to credentials. It looks at the first argument of Web.Contents and expects a string literal it can evaluate at design time. If that argument is a variable or an expression, static analysis can't determine the data source, and the refresh fails.

The Fix: RelativePath and Query

The solution is to keep the base URL static and move all dynamic parts into the options record:

text
// Works in Power BI Service
Web.Contents("https://api.example.com", [
    RelativePath = "/v2/accounts",
    Query = [#"page[size]" = "100", cursor = nextCursor],
    Headers = [Authorization = "Bearer " & token]
])

The base URL (first argument) never changes. RelativePath and Query can be dynamic - the engine resolves them at runtime but doesn't need them for credential mapping.

NOTE

The credential scope in Power BI is tied to the base URL. Changing RelativePath or Query parameters doesn't create new credential entries. This is exactly what makes this pattern work for pagination - every page hits the same base URL with different query parameters.

Three Things Most Tutorials Miss

1. ExcludedFromCacheKey

Power Query caches HTTP responses keyed by the full request (URL + headers + body). If your auth token changes between pages or between refreshes, every request gets a fresh cache entry. Exclude volatile headers from the cache key:

text
Web.Contents("https://api.example.com", [
    RelativePath = "/v2/accounts",
    Query = [#"page[size]" = "100"],
    Headers = [Authorization = "Bearer " & GetAccessToken()],
    ExcludedFromCacheKey = {"Authorization"}
])

Without this, you're wasting cache and potentially re-fetching data unnecessarily.

2. Binary.Buffer for Preventing Duplicate HTTP Calls

Power Query's lazy evaluation model means expressions can be evaluated more than once. If you access both the response body (to parse JSON) and the response metadata (to check the status code), Power Query may issue the HTTP request twice.

Wrap your Web.Contents call in Binary.Buffer to ensure the response is fetched once and held in memory:

text
let
    Buffered = Binary.Buffer(
        Web.Contents("https://api.example.com", [
            RelativePath = "/v2/accounts",
            Query = QueryParams,
            Headers = [Authorization = "Bearer " & token],
            ExcludedFromCacheKey = {"Authorization"}
        ])
    ),
    Status = Value.Metadata(Buffered)[Response.Status],
    Data = Json.Document(Buffered)
in
    Data

This is especially important inside pagination loops where you're making dozens or hundreds of calls. Without Binary.Buffer, you could double your API request count without realizing it.

3. The Last Page Problem

This is the one that silently corrupts your data.

List.Generate evaluates its condition function before deciding whether to keep an iteration's result. In a standard pagination loop, the condition checks whether there's a "next" link. The problem: on the last page, the API returns no next link, so the condition fails - and List.Generate discards that iteration's data entirely.

You fetch 10 pages. You get 9 pages of data. No error, no warning. Just missing records.

The fix is a look-ahead pattern where the condition tests the previous iteration's "next" link, not the current one. I'll show the complete implementation below.

Complete Pattern: Cursor-Based Pagination

Here's the full implementation. This handles cursor-based APIs where the response includes a "next" link (e.g., links.next in JSON:API style responses).

text
let
    BaseUrl = "https://api.example.com",
    PageSize = "1000",

    // Helper: extract cursor parameter from a full "next" URL
    // e.g., "https://api.example.com/v2/accounts?page[size]=1000&page[after]=abc123"
    // returns "abc123"
    ExtractCursor = (url as text) as nullable text =>
        let
            Parts = Uri.Parts(url),
            QueryParams = Parts[Query],
            Cursor = try Record.Field(QueryParams, "page[after]") otherwise null
        in
            Cursor,

    // Fetch a single page using static base URL + dynamic query params
    GetPage = (cursor as nullable text) as record =>
        let
            QueryParams = if cursor = null
                then [#"page[size]" = PageSize]
                else [#"page[size]" = PageSize, #"page[after]" = cursor],
            RawResponse = Binary.Buffer(
                Web.Contents(BaseUrl, [
                    RelativePath = "/v2/accounts",
                    Query = QueryParams,
                    Headers = [
                        #"Content-Type" = "application/vnd.api+json",
                        Authorization = "Bearer " & GetAccessToken()
                    ],
                    ExcludedFromCacheKey = {"Authorization"},
                    Timeout = #duration(0, 0, 2, 0)
                ])
            ),
            Response = Json.Document(RawResponse),
            Data = Response[data],
            NextLink = try Response[links][next] otherwise null,
            NextCursor = if NextLink <> null
                then ExtractCursor(NextLink)
                else null
        in
            [Data = Data, NextCursor = NextCursor],

    // Paginate using List.Generate with look-ahead
    // to avoid skipping the last page
    AllPages = List.Generate(
        // Seed: fetch the first page, mark as "has more"
        () => [Page = GetPage(null), HasMore = true],

        // Condition: continue while there are more pages
        each [HasMore],

        // Next: look ahead - if there's a cursor, fetch next page;
        // otherwise signal stop
        each let
            Cursor = [Page][NextCursor],
            NextPage = if Cursor <> null
                then GetPage(Cursor)
                else null
        in [
            Page = if NextPage <> null then NextPage else [Page],
            HasMore = Cursor <> null
        ],

        // Selector: extract just the data from each iteration
        each [Page][Data]
    ),

    Combined = List.Combine(AllPages),
    AsTable = Table.FromList(
        Combined,
        Splitter.SplitByNothing(),
        null, null, ExtraValues.Error
    )
in
    AsTable

How It Works

The key is the relationship between the condition and next functions in List.Generate:

Seed: Fetch the first page. Set HasMore = true to ensure we enter the loop.
Condition (each [HasMore]): Checks the flag from the previous iteration.
Next: If the previous page had a cursor, fetch the next page. If not, keep the current page but set HasMore = false.
Selector (each [Page][Data]): Extracts the data array from each iteration.

On the last page, NextCursor is null. The next function sets HasMore = false but still returns the current page. List.Generate sees HasMore = false on the following check, stopping the loop - but the last page's data has already been emitted in this iteration.

About GetAccessToken()

The code above references GetAccessToken() as a placeholder. In practice, this would be a separate query that handles your OAuth flow - client credentials, refresh tokens, or whatever your API requires. A simple example:

text
// Separate query: GetAccessToken
let
    TokenUrl = "https://auth.example.com/oauth/token",
    Body = "grant_type=client_credentials"
        & "&client_id=YOUR_ID"
        & "&client_secret=YOUR_SECRET",
    Response = Json.Document(
        Web.Contents(TokenUrl, [
            Content = Text.ToBinary(Body),
            Headers = [
                #"Content-Type"
                    = "application/x-www-form-urlencoded"
            ],
            ManualStatusHandling = {400, 401}
        ])
    ),
    Token = Response[access_token]
in
    Token

WARNING

Don't hardcode secrets in your M code. For production, use Power BI parameters or Azure Key Vault integration to manage credentials securely.

Adding Error Resilience

For production workloads, add ManualStatusHandling to catch rate limits and transient failures:

text
let
    RawResponse = Binary.Buffer(
        Web.Contents(BaseUrl, [
            RelativePath = "/v2/accounts",
            Query = QueryParams,
            Headers = [Authorization = "Bearer " & token],
            ManualStatusHandling = {429, 500, 502, 503},
            ExcludedFromCacheKey = {"Authorization"}
        ])
    ),
    Status = Value.Metadata(RawResponse)[Response.Status],
    Result = if Status = 200 then Json.Document(RawResponse)
             else if Status = 429 then
                 error "Rate limited - reduce page size or add delay"
             else
                 error Error.Record(
                     "API Error",
                     "HTTP " & Text.From(Status)
                 )
in
    Result

NOTE

Power Query already has built-in retry logic for status codes 408, 429, 500, 502, 503, and 504 with exponential backoff. ManualStatusHandling overrides that automatic behavior for the specified codes, giving you control but also responsibility. Only use it when you need custom handling.

Quick Reference

Concern	Solution
Dynamic data source error	Keep base URL static, use `RelativePath` and `Query` for dynamic parts
Auth token caching	Add `ExcludedFromCacheKey = {"Authorization"}`
Duplicate HTTP calls	Wrap `Web.Contents` in `Binary.Buffer`
Last page skipped	Use look-ahead pattern in `List.Generate`
Rate limiting	`ManualStatusHandling` + status check
Timeout on slow APIs	`Timeout = #duration(0, 0, 5, 0)` in options
Credential scope	One credential per base URL - all pages share the same credential entry

Checklist Before Publishing to the Service

The first argument of every Web.Contents call is a string literal (no variables, no concatenation)
All dynamic values (cursors, page numbers, filters) go through RelativePath or Query
Auth tokens are excluded from cache key
Web.Contents calls inside loops are wrapped in Binary.Buffer
Token acquisition is a separate query (not inline in the pagination loop)
Test the refresh in the Service - Desktop success does not guarantee Service success

The Problem

Here's the pagination pattern you'll find in most tutorials and forum answers:

text
// Breaks in Power BI Service
Web.Contents(nextUrl)

Where nextUrl is a variable - maybe it comes from parsing the previous API response, or it's assembled by string concatenation:

text
Web.Contents("https://api.example.com/v2/accounts?page[size]=100&cursor=" & nextCursor)

The Fix: RelativePath and Query

The solution is to keep the base URL static and move all dynamic parts into the options record:

text
// Works in Power BI Service
Web.Contents("https://api.example.com", [
    RelativePath = "/v2/accounts",
    Query = [#"page[size]" = "100", cursor = nextCursor],
    Headers = [Authorization = "Bearer " & token]
])

The base URL (first argument) never changes. RelativePath and Query can be dynamic - the engine resolves them at runtime but doesn't need them for credential mapping.

NOTE

Three Things Most Tutorials Miss

1. ExcludedFromCacheKey

text
Web.Contents("https://api.example.com", [
    RelativePath = "/v2/accounts",
    Query = [#"page[size]" = "100"],
    Headers = [Authorization = "Bearer " & GetAccessToken()],
    ExcludedFromCacheKey = {"Authorization"}
])

Without this, you're wasting cache and potentially re-fetching data unnecessarily.

2. Binary.Buffer for Preventing Duplicate HTTP Calls

Wrap your Web.Contents call in Binary.Buffer to ensure the response is fetched once and held in memory:

text
let
    Buffered = Binary.Buffer(
        Web.Contents("https://api.example.com", [
            RelativePath = "/v2/accounts",
            Query = QueryParams,
            Headers = [Authorization = "Bearer " & token],
            ExcludedFromCacheKey = {"Authorization"}
        ])
    ),
    Status = Value.Metadata(Buffered)[Response.Status],
    Data = Json.Document(Buffered)
in
    Data

This is especially important inside pagination loops where you're making dozens or hundreds of calls. Without Binary.Buffer, you could double your API request count without realizing it.

3. The Last Page Problem

This is the one that silently corrupts your data.

You fetch 10 pages. You get 9 pages of data. No error, no warning. Just missing records.

The fix is a look-ahead pattern where the condition tests the previous iteration's "next" link, not the current one. I'll show the complete implementation below.

Complete Pattern: Cursor-Based Pagination

Here's the full implementation. This handles cursor-based APIs where the response includes a "next" link (e.g., links.next in JSON:API style responses).

text
let
    BaseUrl = "https://api.example.com",
    PageSize = "1000",

    // Helper: extract cursor parameter from a full "next" URL
    // e.g., "https://api.example.com/v2/accounts?page[size]=1000&page[after]=abc123"
    // returns "abc123"
    ExtractCursor = (url as text) as nullable text =>
        let
            Parts = Uri.Parts(url),
            QueryParams = Parts[Query],
            Cursor = try Record.Field(QueryParams, "page[after]") otherwise null
        in
            Cursor,

    // Fetch a single page using static base URL + dynamic query params
    GetPage = (cursor as nullable text) as record =>
        let
            QueryParams = if cursor = null
                then [#"page[size]" = PageSize]
                else [#"page[size]" = PageSize, #"page[after]" = cursor],
            RawResponse = Binary.Buffer(
                Web.Contents(BaseUrl, [
                    RelativePath = "/v2/accounts",
                    Query = QueryParams,
                    Headers = [
                        #"Content-Type" = "application/vnd.api+json",
                        Authorization = "Bearer " & GetAccessToken()
                    ],
                    ExcludedFromCacheKey = {"Authorization"},
                    Timeout = #duration(0, 0, 2, 0)
                ])
            ),
            Response = Json.Document(RawResponse),
            Data = Response[data],
            NextLink = try Response[links][next] otherwise null,
            NextCursor = if NextLink <> null
                then ExtractCursor(NextLink)
                else null
        in
            [Data = Data, NextCursor = NextCursor],

    // Paginate using List.Generate with look-ahead
    // to avoid skipping the last page
    AllPages = List.Generate(
        // Seed: fetch the first page, mark as "has more"
        () => [Page = GetPage(null), HasMore = true],

        // Condition: continue while there are more pages
        each [HasMore],

        // Next: look ahead - if there's a cursor, fetch next page;
        // otherwise signal stop
        each let
            Cursor = [Page][NextCursor],
            NextPage = if Cursor <> null
                then GetPage(Cursor)
                else null
        in [
            Page = if NextPage <> null then NextPage else [Page],
            HasMore = Cursor <> null
        ],

        // Selector: extract just the data from each iteration
        each [Page][Data]
    ),

    Combined = List.Combine(AllPages),
    AsTable = Table.FromList(
        Combined,
        Splitter.SplitByNothing(),
        null, null, ExtraValues.Error
    )
in
    AsTable

How It Works

The key is the relationship between the condition and next functions in List.Generate:

Seed: Fetch the first page. Set HasMore = true to ensure we enter the loop.
Condition (each [HasMore]): Checks the flag from the previous iteration.
Next: If the previous page had a cursor, fetch the next page. If not, keep the current page but set HasMore = false.
Selector (each [Page][Data]): Extracts the data array from each iteration.

About GetAccessToken()

text
// Separate query: GetAccessToken
let
    TokenUrl = "https://auth.example.com/oauth/token",
    Body = "grant_type=client_credentials"
        & "&client_id=YOUR_ID"
        & "&client_secret=YOUR_SECRET",
    Response = Json.Document(
        Web.Contents(TokenUrl, [
            Content = Text.ToBinary(Body),
            Headers = [
                #"Content-Type"
                    = "application/x-www-form-urlencoded"
            ],
            ManualStatusHandling = {400, 401}
        ])
    ),
    Token = Response[access_token]
in
    Token

WARNING

Don't hardcode secrets in your M code. For production, use Power BI parameters or Azure Key Vault integration to manage credentials securely.

Adding Error Resilience

For production workloads, add ManualStatusHandling to catch rate limits and transient failures:

text
let
    RawResponse = Binary.Buffer(
        Web.Contents(BaseUrl, [
            RelativePath = "/v2/accounts",
            Query = QueryParams,
            Headers = [Authorization = "Bearer " & token],
            ManualStatusHandling = {429, 500, 502, 503},
            ExcludedFromCacheKey = {"Authorization"}
        ])
    ),
    Status = Value.Metadata(RawResponse)[Response.Status],
    Result = if Status = 200 then Json.Document(RawResponse)
             else if Status = 429 then
                 error "Rate limited - reduce page size or add delay"
             else
                 error Error.Record(
                     "API Error",
                     "HTTP " & Text.From(Status)
                 )
in
    Result

NOTE

Quick Reference

Concern	Solution
Dynamic data source error	Keep base URL static, use `RelativePath` and `Query` for dynamic parts
Auth token caching	Add `ExcludedFromCacheKey = {"Authorization"}`
Duplicate HTTP calls	Wrap `Web.Contents` in `Binary.Buffer`
Last page skipped	Use look-ahead pattern in `List.Generate`
Rate limiting	`ManualStatusHandling` + status check
Timeout on slow APIs	`Timeout = #duration(0, 0, 5, 0)` in options
Credential scope	One credential per base URL - all pages share the same credential entry

Checklist Before Publishing to the Service

The first argument of every Web.Contents call is a string literal (no variables, no concatenation)
All dynamic values (cursors, page numbers, filters) go through RelativePath or Query
Auth tokens are excluded from cache key
Web.Contents calls inside loops are wrapped in Binary.Buffer
Token acquisition is a separate query (not inline in the pagination loop)
Test the refresh in the Service - Desktop success does not guarantee Service success