Forum Discussion

bwhdegroot's avatar
bwhdegroot
Frequent Visitor
6 years ago

Paginate Rest API via Offset and Limit method

Hello all,   I know this has been covered quite a frequent amount of times, but it seems I cannot get it to work with my api. Currently I am trying to connect to the following API: https://api.sea...
Need Help
bwhdegroot's avatar
bwhdegroot
Frequent Visitor
6 years ago

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

includestring optional
offsetint0optional
limitint50optional
access_tokenstring required

limit = max 500

 

https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=XXX

Response

Field Type Description

statusstringok or error
total_countintTotal result count
jobsarrayArray 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

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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
    Table

Notice: 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