Table of Contents
We can use Power Query to pull records from APIs where our data resides, but most of the time each API we connect to will work in different ways:
- Some APIs might bring all records on the same page
- Some APIs will limit the number of records you can retrieve
- Some APIs will pull data into pages so you can only bring X amount of records at a time
In this blog, we will cover the scenario where we need to paginate to extract all available records based on the data we requested from the API. If you’re working with Microsoft Power Platform consulting services, understanding API pagination is crucial for optimizing data retrieval and integration processes.
Solution description
Let’s suppose we want to extract the list of invoices for the current from our accounting system, when we send this request to the API we get the following:
The total invoices we get in the result are 917 but the current page only has 100 records, that means we need to pull 10 different pages to extract the full set of records available.
To achieve this in Power Query we will use Power Query’s List.Generate() function. This function is the closest feature we have in M language to loop through a set.
The way we use List.Generate depends on the type of results the API returns. We will cover 2 scenarios in this blog:
- API gives us information about how many pages the results will have
- API only returns a list of records and we need to “skip” records to find the next available batch
As a team deeply entrenched in Microsoft Power Platform consulting services, we’re uniquely positioned to guide businesses through the complexities of API pagination with Power Query. Our expertise ensures that organizations can optimize their data retrieval processes, allowing them to focus more on strategic initiatives rather than getting bogged down by technical hurdles.
Scenario 1. API uses page numbers to paginate
Some APIs will include in the results attributes we can use to calculate the number of pages we need. In the below example we can use the number of records and page size to calculate how many times we need to paginate.
Note: Some other APIs will give us directly the number of pages available so we can loop them directly.
API GET request looks like this:
https://abc.abc.com/api/Invoices?page_no=1&page_size=100
Power Query to extract pages for this API would be as follows:
Scenario 2. API uses offset records to paginate
Some APIs will not return the number of available records, it will only give us back the current page:
When this is the case, usually APIs will have an “offset” attribute that we can use to extract all available pages. This “offset” parameter will “skip” X number of records to extract the next page’s result.
For example, if we know our API will bring 100 records at a time, we will need to “skip” these 100 records each time until we find less than 100 records. On the first page, our offset will be 0 because we don’t need to skip any record, but once we reach the second page, we need to offset the first 100 records to bring records from 101 to 200 and so on.
API GET request looks like this:
https://abc.abc.se/abc -webapi/v1/projects?company=SE&limit=100&offset=200
Power Query to extract pages for this API would be as follows:
Conclusion
APIs will work in different ways but Power Query’s List.Generate() function is amazing and can adjust to multiple scenarios and we can use it to pull data from APIs.
At Power GI, we understand the challenges of integrating data from multiple sources. Power Query’s capabilities enable you to automate and simplify the data extraction process, making it a valuable tool for business intelligence and reporting tasks. Whether you’re working with Power BI, Excel, or another platform, Power Query ensures efficient and reliable API data handling.
