Forum Discussion

Brite's avatar
Brite
Icon for Helper I rankHelper I
4 years ago
Solved

Iterate Over Dynamic API Pages With Power Query

I am currently having trouble iterating over dynamic API pages with Power Query. Each page only has 100 rows but I need to request all rows through API. To get all of the data I need to retrieve all IDs (next_batch_after_version) numbers (displays one ID per request). I have looked through https://datachant.com/2016/06/27/cursor-based-pagination-power-query/ but am a little confused on how to do this with a parameter instead of a URL.  The API uses a parameter ID called "next_batch_after_version" which is what I need to loop to get the next page(s). The URL looks like this: https://waysact.me/api/v4/pledges/next_batch?campaign_id=2732&schema_id=115&next_batch_after_version=[THIS_IS_WHAT_I_NEED_TO_LOOP] 

 

I attached the code below...any help is greatly appreciated!

 

This is the code I have now:

 

 

let
iterations = 10, // Number of iterations
Source = Json.Document(Web.Contents("https://waysact.me",

[RelativePath ="/api/v4/pledges/next_batch?campaign_id=2732&schema_id=115&next_batch_after_version="&(next_batch_after_version),
Headers=[Accept="application/json", Authorization="Bearer TOKEN*****GOES****HERE"]])),






FnGetOnePage =
(Source) as record =>
let
Source = Json.Document(Web.Contents(Source)),
data = try Source[data] otherwise null,
next = try Source[paging][next] otherwise null,
res = [Data=data, Next=next]
in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(Source)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data])
in
GeneratedList

 

 

 

6 Replies

  •  

    RelativePath ="/api/v4/pledges/next_batch?campaign_id=2732&schema_id=115&next_batch_after_version="

     Something's missing. Where's the page parameter?

    • Brite's avatar
      Brite
      Icon for Helper I rankHelper I

      I updated my post. The page parameter is next_batch_after_version=[ID number Generated]. next_batch_after_version is the number needed to loop back into the URL. 

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User

        Generally you don't want to schlep the entirety of your data through List.Generate.  Instead

         

        - use List.Generate to harvest the next page IDs

        - Create a table from the list of URLs

        - use Table.AddColumn to add the content of each chunk to the list(*)

        - Expand that column in one fell swoop

         

        *) yes, this technically means you load each page twice. However, most of the time your local cache is buffering this without any network penalty.

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support

    Hi Brite ,

    In this case the ID number is unknown that you need to define so you can refer this viedo which introduces it in detals that create a custom function with number as parameter and use List.Generate to loop it:

    How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate 

     

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