Paging REST API results

[mrgou]

I realize that this question has been posted a thousand times, but I just couldn't find an answer to my specific case nonetheless.

So I'm querying a REST API, which returns paged results. I thus need to iterate in this way: based on the size of the last page (which is a field in the JSON response), increment a REST query parameter to get the next page, until the next page contains no records. In pseudo-code:

content ← True
i ← 1
While content
    response ← get(url, http_parameters=['start' = i])
    results ← results + response['records']
    i ← i + response['size']
    content ← size(response['records']) > 0
Endwhile

Can anyone explain how this can be realized in Power Query? I understand that there's a Table.GenerateByPage function, but Microsoft's documentation isn't great when learning from scratch.

Base code for the time being:

let
    rooturl = "https://my.apiservice.com/",
    Source =
        Web.Contents(
            rooturl,
            [
                Headers = [
                    #"Content-Type" = "application/json"
                ],
                RelativePath = "api/rest/endpoint"
            ]
        )
in
    Source

Any hint to the right track will be much appreciated! Thanks in advance!

[v-lionel-msft]

Hi @mrgou ,

Maybe you can try to do like this.

Or try this.

Best regards,
Lionel Chen

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