Forum Discussion
REST API pagination with a "nextToken" - how to iterate through pages
Hello,
I am an beginner/intermediate-level Power User, and am new to accessing APIs through Power Query. I am feeling pretty confident today - I have successfully fetched the bearer token for my API and made several successful API calls for various data. I am stuck on the next part of the puzzle.
The API I am accessing limits results to 100 per page, so I will need to fetch multiple pages of results and then merge them together into a single query. The tricky part is that *if* there is a next page, the API returns a token called "nextToken" to indicate that there is a next page, and that token must be fed back to the API in order to fetch the next page. Hence, there is no way of knowing how many pages of results, you just have to wait until a response without a "nextToken" is received.
I would like to build something akin to a While() loop in other programming languages that works as follows:
nextTokenExists = FALSE
(fetch first page of results - got this done already)
check for nextToken
While (nextToken = TRUE):
fetch next page of results using nextToken value
merge these results to first page of results
check for nextToken again and update value
Ultimately this loop should end when the "nextToken" is not found in the API response, and I would like to end up with a single query that is a merger of all of the results from within the loop.
I don't have a clue where to get started on this in Power Query, and I don't think the sample data is really relevant, but the API documentation in question for the data I am fetching can be found here: https://spytec.readme.io/reference/get-alerts
Please let me know if more information is needed. Thanks in advance!
5 Replies
- AnonymousNot applicableI have also same issue , i can get 100 records only and for next 100 i need to use NextToken from first result please help us
- ppm1
Solution Sage
This article has an example you can likely adapt (the datalist step of the 2nd approach).
Updated – Get SharePoint List Data … Fast – Hoosier BI
Pat
- RicardoAGdSilvaFrequent Visitor
Anyone got an idea on this issue? I got the same problem
- AnonymousNot applicable
Hi, sellersmd Did you find a solution to this issue? I'm also researching it.
- JillianENew Member
Does anyone have a sample for how this could work? I used to loop through StartAt and now that api endpoint has been discontinued and I don't understand how to use nextpagetoken. Can anyone help with this?