API call to return all records for all pages

[jpt1228]

Hello, I have been looking through all the posts for API calls each one seems to solve a different issue or not be complete. Since there are frequent updates to PBI desktop I didn't know if there was a more simple way since most of the posts are from 2016-2019.

I am using the Web connector with basic authorization calling an API which has a limit of 500 rows. I know there is currently 16,000 rows by manually sending calls until it returns no data. I do not have a way of returning the number of pages via the API call.

The API I am connecting to is https://developers.greenhouse.io/harvest.html#throttling 

Example paging header

Link: <https://harvest.greenhouse.io/v1/candidates?page=2&per_page=2>; rel="next",
<https://harvest.greenhouse.io/v1/candidates?page=474&per_page=2>; rel="last"

I am not sure if there is a way to use this or if I need to create a dynamic page=xx and reference a consecutive list of numbers to continue to return pages. I have researched creating a loop and API calls with pagination but can't seem to pull it all together.

My expected result is to return each page with per_page=500 until all records have been received.

[jpt1228]

@amitchandak This looks like it should work - I will have to test on my API. I will report back if I run into issues, otherwise all is good. Thanks for this link

[amitchandak · Accepted solution]

@jpt1228 , bit different topic but should help

https://www.myonlinetraininghub.com/scrape-data-multiple-web-pages-power-query

[jpt1228]

@amitchandak Your link worked for the most part. Since it doesn't pull in the number of pages or use a next page function I had to create a table with a static number of pages and then once the function runs to pull in the paginated data I needed to click on the data column and remove errors. This enabled me to pull in all the paginated data into my data model.