Getting Paginated Data from REST API

[DBATTIN]

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

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).

The possible values for the link relations are; next (the link relation for the immediate next page of results), last (the link relation for the last page of results), first (the link relation for the first page of results), and prev (the link relation for the immediate previous page of results).

You can optionally specify a limit URL parameter to change how many results are served per page (hard limits will apply), and you can navigate through paginated results using the offset URL parameter which lets you specify which result should be returned first, e.g. ?limit=10&offset=30 (this would return 10 results starting with record 30).

[v-yingjl]

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
    Table

Best 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.

[mahoneypat]

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

[DBATTIN]

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