Forum Discussion
Getting Paginated Data from REST API
I am attempting to get data from a RST API where the number of records is limited to 1,000 records. The data ia paginated. I have the following script but I still only get 1,000 records
let
Source = Json.Document(Web.Contents("https://api.rotacloud.com/v1/attendance?start=1455875832&end=2767225599&include_deleted=false")),
BaseUrl="https://api.rotacloud.com/v1/attendance?start=1455875832&end=2767225599&include_deleted=false",
EntitiesPerPage = 100,
GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetTotalEntities = () =>
let Json = GetJson(BaseUrl),
Total = Json[total]
in Total,
GetPage = (Index) =>
let Skip = "skip=" & Text.From(Index * EntitiesPerPage),
Url = BaseUrl & "&" & Skip,
Json = GetJson(Url),
Value = Json[data]
in Value,
GetUrl = (Index) =>
let Skip = "skip=" & Text.From(Index * EntitiesPerPage),
Url = BaseUrl & "&" & Skip
in Url,
EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
URLs = List.Transform(PageIndices, each GetUrl(_)),
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "deleted", "approved", "user", "location", "role", "in_time", "in_time_clocked", "out_time", "out_time_clocked", "minutes_break", "minutes_late", "hours", "hours_auto", "hours_is_auto", "notes", "shift", "in_method", "in_location", "in_photo", "in_device", "in_terminal", "out_method", "out_location", "out_photo", "out_device", "out_terminal", "breaks_clocked"}, {"id", "deleted", "approved", "user", "location", "role", "in_time", "in_time_clocked", "out_time", "out_time_clocked", "minutes_break", "minutes_late", "hours", "hours_auto", "hours_is_auto", "notes", "shift", "in_method", "in_location", "in_photo", "in_device", "in_terminal", "out_method", "out_location", "out_photo", "out_device", "out_terminal", "breaks_clocked"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"id", Int64.Type}, {"deleted", type logical}, {"approved", type logical}, {"user", Int64.Type}, {"location", Int64.Type}, {"role", Int64.Type}, {"in_time", Int64.Type}, {"in_time_clocked", Int64.Type}, {"out_time", Int64.Type}, {"out_time_clocked", Int64.Type}, {"minutes_break", Int64.Type}, {"minutes_late", Int64.Type}, {"hours", type number}, {"hours_auto", type number}, {"hours_is_auto", type logical}, {"notes", type any}, {"shift", Int64.Type}, {"in_method", type text}, {"in_location", type any}, {"in_photo", type any}, {"in_device", type any}, {"in_terminal", Int64.Type}, {"out_method", type text}, {"out_location", type any}, {"out_photo", type any}, {"out_device", type any}, {"out_terminal", Int64.Type}, {"breaks_clocked", type any}})
in
#"Changed Type"
I have had the following from the developers of the site but can't see where to put this in to the script or if i need to have a complete rewrite
3 Replies
Please see this video for how to leverage the offset approach in an easier way.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
Whilst this returns data it keeps calling the same data from the API
The site is telling me I need to include the NEXT call in the script to get the next page of data but I am unsure how to incorporate this in to your solution
Their response was - Paginated requests will include two extra response headers; X-Total-Count (the total number of results), and Link (containing one or more hypermedia link relations
Hi DBATTIN ,
You can refer this query from the simliar issue about offset and limit in REST API:
Paginate Rest API via Offset and Limit method
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 TableBest Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
