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,
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
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
ResultTableBelow 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 |
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,
did you get a chance to look into above suggestion provided?
Thanks,
Prashanth