Forum Discussion
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
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.
6 Replies
- lbendlin
Super User
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
Helper 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
Super 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
Community 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.