Forum Discussion
Paginate Rest API via Offset and Limit method
amitchandak Unfortunately I have been going through all these links already, but no success to far.
Also tried this example, adjusted it a bit but also seems not to work properly.
The API documentation shows the following specs of the API:
Param Type Default Required
| include | string | optional | |
| offset | int | 0 | optional |
| limit | int | 50 | optional |
| access_token | string | required |
limit = max 500
https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=XXX
Response
Field Type Description
| status | string | ok or error |
| total_count | int | Total result count |
| jobs | array | Array of jobs |
let
Url = "https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=MYKEY",
EntitiesPerPage = 50,
GetJson = (Url) =>
let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = Url & "$count=true&$top=0",
Json = GetJson(Url),
Count = Json[#"total_count"]
in Count,
GetPage = (Index) =>
let Skip = "$skip=" & Text.From(Index * EntitiesPerPage),
Top = "$top=" & Text.From(EntitiesPerPage),
Url = Url & Skip & "&" & Top,
Json = GetJson(Url),
Value = Json[#"value"]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
HI bwhdegroot,
It seems like you are copying the sample query formula and try to use it with your scenario, right?
I think you need to do some modification on internal functions with their URL query string and returned properties. (your API does not contain 'top, skip, count' parameters)Please take a look at below formula if it meets to your requirement:
let MYKEY="xxxxxx",//token string EntitiesPerPage = 500, Token= "&access_token=" & MYKEY, Limit="&limit=" & Text.From(EntitiesPerPage), Url = "https://api.searchsoftware.nl/v2/jobs?include=categories|contacts" & Limit, GetJson = (Url) => let RawData = Web.Contents(Url & Token), Json = Json.Document(RawData) in Json, GetEntityCount = () => let Url = Url & "&offset=0" & Token, Json = GetJson(Url), Count = Json[#"total_count"] in Count, GetPage = (Index) => let //(option A)offset equal to previous row count offset = "$offset=" & Text.From(Index * EntitiesPerPage), //(option B)offset equal to page numer //offset = "$offset=" & Text.From(Index), Url = Url & offset & Token, Json = GetJson(Url), Value = Json[#"jobs"] in Value, EntityCount = GetEntityCount(), PageCount = Number.RoundUp(EntityCount / EntitiesPerPage), PageIndices = { 0 .. PageCount - 1 }, Pages = List.Transform(PageIndices, each GetPage(_)), Entities = List.Union(Pages), Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in TableNotice: I'm not so sure what type of offset parameter your API used, please choose one of two optional offset steps based on your scenario.
Regards,
Xiaoxin Sheng
Hi Anonymous
Thank you! This is almost what I need and I see it retrieving data. THe only observation is that based on my 836 records and limit per page of 50, I see 17 pages. Nevertheless, these 17 pages all have the same data in there. How can de code be adapted so it loops the offset to show different pages?
Many thanks!
HI bwhdegroot,
After double-check on my code, I think it may be caused by the typo. Your API use '&' character to link query string parameters but I have typing '$' character(at GetPage function) which may not works.
Please try to use the following codes to confirm it they fixed the duplicate page data issue:let MYKEY="xxxxxx",//token string EntitiesPerPage = 500, Token= "&access_token=" & MYKEY, Limit="&limit=" & Text.From(EntitiesPerPage), Url = "https://api.searchsoftware.nl/v2/jobs?include=categories|contacts" & Limit, GetJson = (Url) => let RawData = Web.Contents(Url & Token), Json = Json.Document(RawData) in Json, GetEntityCount = () => let Url = Url & "&offset=0" & Token, Json = GetJson(Url), Count = Json[#"total_count"] in Count, GetPage = (Index) => let //(option A)offset equal to previous row count offset = "&offset=" & Text.From(Index * EntitiesPerPage), //(option B)offset equal to page numer //offset = "&offset=" & Text.From(Index), Url = Url & offset & Token, Json = GetJson(Url), Value = Json[#"jobs"] in Value, EntityCount = GetEntityCount(), PageCount = Number.RoundUp(EntityCount / EntitiesPerPage), PageIndices = { 0 .. PageCount - 1 }, Pages = List.Transform(PageIndices, each GetPage(_)), Entities = List.Union(Pages), Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in TableRegards,
Xiaoxin Sheng