Forum Discussion

powerbitotheppl's avatar
powerbitotheppl
Advocate I
4 years ago

API Pagination in JSON Body - How to Access with Power Query in Custom Connector?

Hello,   I have built a custom connector to connect to the Vimeo API via OAuth2. Everything is working well, but it appears I need to come up with a solution to deal with pagination, as I am only g...
Need Help
powerbitotheppl's avatar
powerbitotheppl
Advocate I
4 years ago

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.

lbendlin's avatar
lbendlin
Super User
4 years ago

Vimeo may be throttling your requests. Have you gotten a 429 yet?

 

Table.GenerateByPage is syntax sugar for List.Generate.

  • powerbitotheppl's avatar
    powerbitotheppl
    Advocate I
    4 years ago

    No 429, but I think the payload from this API was particularly large. I was able to filter the fields I want in the query itself, and speed has improved greatly.

     

    Thank you for all of your help with this. I ended up implementing the custom column strategy you mentioned and it works very well. You've been very helpful!