API calls with pagination (non odata)

[Yodaswhiskers]

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!

[dax]

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]

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