Forum Discussion
API Pagination with scroll ID
Hi Rabi,
Yes, you’re on the right track. The behavior you’re seeing—very high row counts with mostly duplicates, usually happens when the Scroll ID is not being passed correctly in subsequent requests. In scroll-based pagination, each response returns a new _scroll_id, and that value must be used in the next call to fetch the next batch of records.
In your query, the main issue looks to be how the parameter is passed in the function. The API returns _scroll_id, but for the next request it should typically be sent as GetScroll (without the underscore and with correct casing). If this is not handled properly, the API may keep returning the same page repeatedly, which explains the duplicates.
Also, make sure your Authorization header includes the Bearer prefix, as some APIs require that for proper authentication.
Once the scroll parameter is corrected and the new Scroll ID is passed on each iteration, the results should return unique pages and align more closely with the expected total row count.
Thanks,
Prashanth
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