API Pagination with scroll ID

Hi Rabi 

The issue is likely caused by the scroll ID parameter name used in the follow-up requests.

The API response returns `_scroll_id`, but for the next requests you should pass that value as `scrollId`, not as `_scroll_id`.

You may also want to include the Bearer prefix in the Authorization header.

Try this pattern:

let
TokenResponse =
Json.Document(
Web.Contents(
"https://auth.go1.com",
[
RelativePath = "oauth/token",
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
Accept = "application/json"
],
Content =
Text.ToBinary(
Uri.BuildQueryString(
[
client_id = "",
client_secret = "",
grant_type = "client_credentials"
]
)
)
]
)
),

AccessToken = TokenResponse[access_token],

GetPage = (optional ScrollId as nullable text) as record =>
let
QueryParams =
if ScrollId = null then
[scroll = "true"]
else
[scroll = "true", scrollId = ScrollId],

Response =
Json.Document(
Web.Contents(
"https://api.go1.com",
[
RelativePath = "v2/enrollments",
Query = QueryParams,
Headers = [
#"api-version" = "2022-07-01",
Authorization = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
)
in
Response,

FirstPage = GetPage(null),

Pages =
List.Generate(
() =>
[
Page = FirstPage,
Hits = try FirstPage[hits] otherwise {},
ScrollId = try FirstPage[_scroll_id] otherwise null
],

each List.Count([Hits]) > 0,

each
if [ScrollId] = null then
[
Page = null,
Hits = {},
ScrollId = null
]
else
let
NextPage = GetPage([ScrollId]),
NextHits = try NextPage[hits] otherwise {},
NextScrollId = try NextPage[_scroll_id] otherwise null
in
[
Page = NextPage,
Hits = NextHits,
ScrollId = NextScrollId
],

each [Hits]
),

Combined = List.Combine(Pages),

ResultTable =
if List.Count(Combined) = 0 then
#table({}, {})
else
Table.FromRecords(Combined)
in
ResultTable

The important change is here:

[scroll = "true", scrollId = ScrollId]

instead of:

[scroll = "true", _scroll_id = sid]

I would also recommend temporarily returning the number of rows per page while testing, so you can confirm whether each scroll request is returning a new batch or only the first page again.

Hi Rabi ,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Ritaf1983 ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

Hi, 

Thanks for your response, At this point I am still getting timeout error when the query loads. I am not sure if its becasue of the volume of data.

Regards,

Hi Rabi ,

please try below troubleshoots and let me know if these help:

Large Data Volume Causing Timeout:

One of the most common reasons for timeout issues is pulling a huge amount of data in a single API request. When too much data is returned at once, Power Query takes longer to process it and memory usage also increases.

Recommended Approach 

• Reduce the limit or pageSize
• Apply date filters wherever possible
• Load the data in smaller batches instead of fetching everything together

Example

Web.Contents(

    url,

    [

        Query = [

            limit = "200"

        ]

    ]

)

Full Dataset Loading in One Query : 

Power Query tries to process the complete dataset in memory during refresh. For large datasets, this can slow down performance and sometimes cause refresh failures.

Recommended Approach

• Split the load using monthly or date-range partitions
• Store intermediate data in Lakehouse, Dataflow, or staging storage

Example

updatedAfter = "2026-01-01",

updatedBefore = "2026-01-31"

Pagination Re-Executing Multiple Times: 

Sometimes Power Query re-evaluates previous steps during execution, which can trigger repeated API calls and increase refresh time.

Recommended

Use List.Buffer() so the pagination results are cached in memory and reused instead of being recalculated.

Example

Pages = List.Buffer(List.Generate(...))

API Rate Limiting or Throttling:

If too many API requests are sent within a short duration, the API may start throttling requests and return HTTP 429 errors.

Recommended Approach

Introduce a small delay between API calls to avoid overwhelming the source system.

Example

Function.InvokeAfter(

    ()=> GetPage(PageNumber),

    #duration(0,0,0,2)

)

No Incremental Load Strategy

Reloading the entire historical dataset during every refresh increases execution time unnecessarily.

Recommended Approach

• Load historical data only once
• During subsequent refreshes, fetch only newly added or updated records

Example

updatedAfter = DateTime.ToText(LastRefreshDate)

Refresh Timeout in Power BI Service:

Sometimes the query works locally but fails in Power BI Service or Gateway due to refresh limits and memory constraints.

Recommended Approach

Instead of connecting the API directly to Power BI, use a staged architecture.

 like: API - > ADF/Fabric pipelines - > Storage - > Power BI

Thanks,

Prashanth

Hi Rabi,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support