Help needed with Power Query to get all records from a Rest API using a loop function

[Hwielen]

Hi there!

I'm just starting out with Power BI and I need to extract data from our online ticketing application to create some reports. I managed to create a query that returns some records with ticket data from selected fields of the past month.

My problem is that the API limits the results to 500 records. So I created a function with a loop to retrieve the rest of the records and combine these in one list. Apart from the 500 records the API returns an additional record with the number of returned records and two URLs. One URL for the previous block of 500 records and one URL to retrieve the next block of 500 records. If there are no more records the second URL returns NULL.

Link to the documentation of the Autotask REST API

I created a function that I call from the query. I did a query count and know there must be 916 records. I'm sure the URL for the next block is passed to the function correctly and I don't get any errors. But the resulting table still shows only 500 records.

I hope someone can tell me what am I doing wrong.

[Hwielen · Accepted solution]

I have got it working! 😀

This is the code i'm using now. It's also much cleaner.

Query

[Anonymous]

Hi @Hwielen ,

Is there any documentation about this Api?

You need to check in the Dotdigital API documentation how to specify the offset when you request your data.
Solved: Api limit of 1000 records - getting the next 1000 ... - Microsoft Power BI Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Hwielen]

I have added a link to the documentation of the Autotask REST API for your information