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

      • bwhdegroot's avatar
        bwhdegroot
        Frequent Visitor
        6 years ago

        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!