Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
RelativePath ="/api/v4/pledges/next_batch?campaign_id=2732&schema_id=115&next_batch_after_version="
Something's missing. Where's the page parameter?
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.
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.
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.
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.