Rest API - call the next page URL without knowing total rows or total pages

[Anonymous]

Hi Everyone, 

This is my first time working with a REST API in Power BI and I'm running into an issue with pagination...

I have an initial GET that calls out to a REST service which returns 100 results and a link for the next 100 results. This link is the same GET call but with an additional parameter page_token. So I need PowerBI to basically capture the results and then continue to loop through the rest of the results using this parameter and combine all the results together. 

I know there is quite a bit of documentation on pagination, like this Community post and this article by the BI Accountant, but all the solutions I can find require either knowing the total records or total pages or have the next page url listed explicitely in the inital record.

In my case, when the API is called, it returns this initial record. 

But you have to click into Pagination in order to grab the next URL:

Based on my research, I was able to piece together this List.Generate function, but I'm having trouble understanding how this would all come together -- how do I tie the next record from above into this List.Generate? 

Thanks in advance for your thoughts!

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

The next page url is stored in Result[_pagination][next] so try this

let
    Source = List.Generate( () =>

        [ URL = "https://api2.frontapp.com/" , Result = Json.Document(Web.Contents(URL)) ],

        each [URL] <> null,

        each [ URL = [Result][_pagination][next] , Result = Json.Document(Web.Contents([URL])) ]

    )
in
    Source

This code will end assuming that [_pagination][next] is null when there are no more records to retrieve.

Regards

Phil

[Anonymous]

Thank you Philip! That [_pagination][next] was the piece I was missing!