API Pagination with scroll ID

[Rabi]

Hi Everyone, 

I am trying to retrieve entire set of enrollments via API. The API supports pagination with scroll ID. However I am not able to retrieve entire set of data.

Below is the attached query:

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],

    BaseUrl = "https://api.go1.com/v2/enrollments",

    FirstResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [scroll = "true"],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),

    ScrollId = FirstResponse[_scroll_id],
    FirstHits = FirstResponse[hits],

    GetScroll = (sid as text) =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [
                        scroll = "true",
                        _scroll_id = sid
                    ],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),

    Pages =
        List.Generate(
            () => [Sid = ScrollId, Result = FirstHits, More = true],

            each [More] = true and [Result] <> null,

            each
                let
                    next = GetScroll([Sid]),
                    hits = try next[hits] otherwise {},
                    newSid = try next[_scroll_id] otherwise null
                in
                    [
                        Sid = newSid,
                        Result = hits,
                        More = List.Count(hits) > 0 and newSid <> null
                    ],

            each [Result]
        ),

    Combined =
        List.Combine(Pages),

    ResultTable =
        Table.FromRecords(Combined)

in
    ResultTable

[v-prasare]

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

[Rabi]

Hi Everyone, 

Thanks for your assistance with this. I managed to retrieve 852,320 rows of enrollment data. However, it appears that all but 659 of the retrieved rows are duplicates.

Could this be because the Scroll ID is not being passed correctly? As I understand it, each subsequent query must use the new Scroll ID returned from the previous scroll request.

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 = "IDHERE",
                                    client_secret = "SECRETHERE",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),
    AccessToken = TokenResponse[access_token],
    BaseUrl = "https://api.go1.com/v2/enrollments",
    FirstResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [scroll = "true"],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),
    ScrollId = try FirstResponse[_scroll_id] otherwise null,
    FirstHits = try FirstResponse[hits] otherwise {},
    GetScroll = (sid as text) =>
        try
            Json.Document(
                Web.Contents(
                    BaseUrl,
                    [
                        Query = [
                            scroll = "true",
                            scrollid = sid
                        ],
                        Headers = [
                            #"api-version" = "2022-07-01",
                            Authorization = AccessToken
                        ]
                    ]
                )
            )
        otherwise null,
    Pages =
        List.Generate(
            () => [
                Sid = ScrollId,
                Result = FirstHits,
                More = true
            ],
            each [More] = true and [Sid] <> null,
            each
                let
                    next = GetScroll([Sid]),
                    hits =
                        if next <> null
                        then try next[hits] otherwise {}
                        else {},
                    newSid =
                        if next <> null
                        then try next[_scroll_id] otherwise null
                        else null
                in
                    [
                        Sid = newSid,
                        Result = hits,
                        More = List.Count(hits) > 0 and newSid <> null
                    ],
            each [Result]
        ),
    Combined =
        List.Combine(Pages),
ResultTable =
    Table.FromRecords(
        Combined,
        null,
        MissingField.UseNull
    )
in
    ResultTable

 Below is the headers the query returns:

Total rows : 789,157 (Checked in the portal this is correct)

id

type

created_time

updated_time

lo_id

parent_enrollment_id

parent_lo_id

pass

status

user_id

utm_source

utm_content

utm_medium

utm_campaign

end_date

due_date

[v-prasare]

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

---

[v-prasare]

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

[Rabi]

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,

[v-prasare]

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

[Ritaf1983]

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.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly