Forum Discussion

PatrickArrastia's avatar
PatrickArrastia
Regular Visitor
3 years ago
Solved

Power Query and pagination

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!

Need Help

3 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    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's avatar
      PatrickArrastia
      Regular Visitor
      3 years ago

      Hello!

       

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

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    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