Power Query and pagination

[PatrickArrastia]

Hello,

I am trying to paginate through an unknown number of pages. There is a value in the table called "end_of_stream", which I think could be used for this. Once there are no more pages the "end_of_stream" value = true.
I believe there is a way to use List.Generate to achieve this just unsure of how to get there.

In this run the only code I have so far is below, screenshot highlighting the table with the "end_of_stream" value below that.

let
Source = Json.Document(Web.Contents("insert subdomain.zendesk.com here")
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"

 

Ideally, PQ will loop through the pages until it sees the "end_of_stream" value = true.

This particular api uses cursor-based pagination as opposed to offset with a max of 100 per page

Many thanks!

[PhilipTreacy · Accepted solution]

Hi @PatrickArrastia 

Your problem is very similar to this one

Solved: Need Help with Stripe REST API Pagination in Power... - Microsoft Power BI Community

You can try this code which of course needs a little modification to include your URL

let
    APICall = 

        List.Generate
        (  
            () => [ APIData = Json.Document(Web.Contents("insert subdomain.zendesk.com here")) , EndData = APIData[end_of_stream] ],

            each [EndData] = "FALSE",
           
            each [ APIData = Json.Document(Web.Contents("insert subdomain.zendesk.com here")) , EndData = [APIData][end_of_stream] ],

            each [APIData]
        )
in
    APICall

Regards

Phil

[v-yalanwu-msft]

Hi, @PatrickArrastia ;

If you use pagination, consider using Power BI Report Builder;

Power BI Report Builder - Power BI | Microsoft Learn
According to my understanding, you mean that when "end_of_stream" value = true, the later data will not be displayed, only the previous data will be displayed, right?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PatrickArrastia]

Hello!

Thank you for your reply, your understanding is correct. Is there a way to achieve this by only using Power Query in Excel?