Forum Discussion

Yodaswhiskers's avatar
Yodaswhiskers
Frequent Visitor
6 years ago

API calls with pagination (non odata)

Hi,

 

I am really new to API's and am struggling to get some looping code to work to ensure all records are pulled, currently it is capped at 1000. 

 

I have used the example from https://community.powerbi.com/t5/Desktop/REST-API-Pagination-converting-records-to-text/td-p/681809 but am getting the following error:

 

Expression.Error: We cannot apply field access to the type List.
Details:
Value=[List]

 

The code is as follows:

 

let
BaseUrl = "https://api.rotacloud.com/v1/shifts?start=1569888000&end=1755875832",
Token = "SecretToken",
EntitiesPerPage = 1000,
Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
Url = BaseUrl,

GetJson = (Url) =>
let
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in
Json,

GetTotalCount = () =>
let
Json = GetJson(Url),
Entities = Json[object.count]
in
Entities,

EntityCount = GetTotalCount(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndex = { 1 .. PageCount},

GetPage = (PageIndex) =>
let
PageUrl = BaseUrl & "page=" & Text.From(PageIndex) & Options,
Json = GetJson(PageUrl),
Value = Json[data]
in
Value,

GetUrl = (PageIndex) =>
let
PageNum = "page=" & Text.From(PageIndex),
PageUrl = BaseUrl & PageNum
in
PageUrl,

Urls = List.Transform(PageIndex, each GetUrl(_)),
Pages = List.Transform(PageIndex, each GetPage(_)),
DataList = List.Union(Pages)
in
DataList

 

 

The below is from the API documentation, not sure if it helps or not:

 

In order to keep our servers happy, requests which return multiple items may be paginated. 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' parameter to change how many results are served per page (hard limits will apply), and you can navigate through paginated results using the 'offset' 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).

 

Any help would be grately appreciated, thanks in advance!

2 Replies

  • dax's avatar
    dax
    Community Support

    Hi Yodaswhiskers , 

    It seems to be related to the type, you could try too convert List value to table to see whether it work or not. Add this part to see whetehr it work or not.

    DataList = List.Union(Pages),
    
        TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        TableFromList

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Yodaswhiskers's avatar
      Yodaswhiskers
      Frequent Visitor

      Hi Zoe,

       

      Thank you so much for your suggestion. I have amended it as perscribed:

      let 
          Source = Json.Document(Web.Contents("https://api.rotacloud.com/v1/shifts?start=1569888000&end=1755875832", [Headers=[Authorization="SecretToken"]])),
          BaseUrl = "https://api.rotacloud.com/v1/shifts?start=1569888000&end=1755875832",
          Token = "SecretToken",
          EntitiesPerPage = 1000,
          Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
          Url = BaseUrl,
      
          GetJson = (Url) =>
              let
                  RawData = Web.Contents(Url, Options),
                  Json = Json.Document(RawData)
              in
                  Json,
      
          GetTotalCount = () =>
              let
                  Json = GetJson(Url),
                  Entities = Json[object.count]
              in
                  Entities,
      
          EntityCount = GetTotalCount(),
          PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
          PageIndex = { 1 .. PageCount},
      
          GetPage = (PageIndex) =>
              let
                  PageUrl = BaseUrl & "page=" & Text.From(PageIndex) & Options,
                  Json = GetJson(PageUrl),
                  Value = Json[data]
              in
                  Value,
      
          GetUrl = (PageIndex) =>
              let
                  PageNum = "page=" & Text.From(PageIndex),
                  PageUrl = BaseUrl & PageNum
              in
                  PageUrl,
      
          Urls = List.Transform(PageIndex, each GetUrl(_)),
          Pages = List.Transform(PageIndex, each GetPage(_)),
          DataList = List.Union(Pages),
      
          TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
      in
          TableFromList

       

      It has removed the error which is good but is still only pulling 1k records across. There should be around 80k. Any ideas?

       

      Thanks again for your help it's greatly appreciated