Iterate Over Dynamic API Pages With Power Query

[Brite]

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...] 

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

[v-yingjl · Accepted solution]

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.

[lbendlin]

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]

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]

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.

[Brite]

Are there any examples of harvesting data and generating a list with List.Generate? I can only find List.Generate looping increments of 1 (unless null) for pages.

[lbendlin]

Each of these APIs is slightly different, there isn't really one single example that covers all the variations. The Curbal video that @v-yingjl shared is a good start.  Realistically any more help would require access to that particular API.