RESTful API fetched data sort

[crossover]

Hi and sorry for the vague title,

I'm taking my first steps with connecting PowerQuery to RESTful API web service, so please bear with me. The connection is established and I can fetch data. But I (like seemingly many others) run into the problem of only grabbing 100 rows of data (seemingly JSON string limitation).

It's a logistics service and I'm getting data starting from the very end (November 2018). While the 100-row limitation is something I need to overcome at some point, is there a way to adjust the query to fetch the newest data, instead of oldest?

I guess my question boils down to this - is this (fetching oldest or newest records first) something that should be adjusted in the M code of my PQ query or is this something I should ask the API owner / developer to modify?

Thanks!

[crossover · Accepted solution]

Ended up being a super simple solution, but adding it here in case another rookie stumbles on this. The sorting was easily adjusted by using a slightly different URL to connect to API ("?ordering=-created_at" added to the end in my case).

I'm still struggling with pagination as getting max 100 records is not sufficient. Might add a separate post on this unless I manage to crack it.

[PhilipTreacy]

Hi @crossover 

The API should provide some way for you to page through the data so you can request a particular set of records.  This is often done by specifying a parameter in the query to say what date range you want data for or what page of records to return. Something like this

http://www.api.website.com/?start-date=xxxxx&end-date=yyyyyy

You need to check your API documentation on how to do this and then write the M code to get what you want.

If you can post a link to the API documentation I'll have a look for you.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[crossover]

Hi @PhilipTreacy 

Thanks for the feedback. I'm trying to contact the development side of the API in question, but looks like it takes some extra time. I'll be back. There is some public documentation available but doesn't look like it is detailed enough.