Forum Discussion
how to create a query that paginates?
Hello there
I've a Dovico web API that I need to access to pull down all records. For clarity, I'm calling the Time Entries function.
The API lists request restritcions as:
...limited to 5 calls per second and 1000 results returned per call. This means 5000 records per second are returned for GET calls...When the page results are returned they will include the Previous Page URI and Next Page URI. If the Next Page URI returns the value of “N/A” then that’s the last page
When I use the following Web.Contents call (tokens obscured for security) I get a neat result set of 1,000 rows:
= Web.Contents("https://api.dovico.com/TimeEntries/?version=5",[Headers=[#"Authorization"="WRAP access_token=""client=XXX&user_token=YYY"""]])
In other words I'm sending only one request. If I run = Xml.Tables(<source above>,null,1252) I can clearly see both PrevPageURI and a NextPageURI data.
I've spent a huge amount of time googling my heart out, and trying all sorts of things, but thus far I'm a numpty.
Has anyone advice for me on how I can exploit the NextPageURI returned by the query to then repeat the query until I hit N/A?
I appreciate your support!
With List.Generate you should be able to "harvest" the list of URL's - you can even try to "harvest" the content at the same time. (But watch out: There's an error in the documentation: Result of last example should be {2, 4, 6, 8})
See Chris' article on how List.Generate works in general: http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/
14 Replies
tempranello - What does the next page URL look like. Ideally, could you post the next page URL and then the next page URL after that?
Sorry, I should have included that from the beginning.
I've generated these by running the initial query with the Web.Contents url as:
https://api.dovico.com/TimeEntries/?next=&version=5
...and then manually copying and pasting the next values and re-running the query.
https://api.dovico.com/TimeEntries/?next=M9286&version=5
...and again with the next value
https://api.dovico.com/TimeEntries/?next=M10703&version=5
...and again with the next value
https://api.dovico.com/TimeEntries/?next=M10801&version=5
They appear to be randomly generated and returned in each result set.
With List.Generate you should be able to "harvest" the list of URL's - you can even try to "harvest" the content at the same time. (But watch out: There's an error in the documentation: Result of last example should be {2, 4, 6, 8})
See Chris' article on how List.Generate works in general: http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/
