Forum Discussion
API Pagination in JSON Body - How to Access with Power Query in Custom Connector?
There are two option to approach this
- iterating through all pages, harvesting/accumulating the data, and repeating until no nextpage is found
- iterating through all pages, ignoring the data, only harvesting the URLs. Then add a cstom column that fetches the content for each URL and combines them in one step
The second approach seems to be wasteful as you seemingly fetch each URL twice. In reality this is most likely covered by the browser engine cache so there is no performance penalty. The benefit is that you don't have to lug the data around during the iteration, so performance is ultimately even better.
Thank you lbendlin,
Do you think that using the second way might help me get around API limits? The API I am working with case a limit of 250 calls a minutes and I think the way I did it (the first option in your list) is making me hit that limit.
Thanks!
And do you happen to have any examples or documentation for the second approach? It sounds very intriguing, and I am noticing performance issues doing it the first approach.
Read about Function.InvokeAfter
Search for API pagination, like here Solved: PowerQuery Rest API Pagination - Microsoft Power BI Community
Thank you, lbendlin.
I've tried pagination a few different ways now:
1) MS documented Table.GenerateByPage function
2) Custom columns to generate API calls
3) Using List.Generate to create a list of API calls and stitching them together
Regarding query speed, I'm having the most luck with #3. But still, it takes a long time to fetch the data and I am often left hanging on "Waiting....api.vimeo.com".
It takes about 5-6 minutes to fetch 1000 videos (coming in as Records containing a plethora of data). Is this a normal length of time to expect? I'm querying at 100 per_page. Postman takes about 10 seconds to get a 100 item response.
I have turned off my Type Detection and Background Data loading in an attempt to speed things up, with no luck.
I think I should open up another thread, since this has gone off topic. But I was wondering if you had any other suggestions.
Thank you for your expertise.