How To Do Pagination In Power Query

The following post will show you how to implement REST API pagination in Power Query.

The example is based on a fictional OData REST API which supports the $count , $top and $skip query parameters and returns JSON data.

The Task:

  • Get all entities from the API via pagination and convert them into a single table (for analysing the data in Excel or Power BI).

The Algorithm:

  1. Send a request with the following query parameters: $count=true and $top=0. This request determines how many entities we will have to fetch (but does not actually fetch any).
  2. Based on how many entities exist and how many entities we want to request per page, we can calculate how many pages we need to fetch (if we have 1650 entities, and we want to fetch 1000 entities per page, we will have to fetch 2 pages, for example).
  3. We can then create a list of the page indices we want to fetch (if we want to fetch 2 pages, the indices are { 0, 1 }, for example) and map the indices to the actual pages by sending the requests for each page with the $skip and $top query parameters (for example: GetPage(0) --> skip = 0 * 1000 = 0, top = 1000 [skip 0 entities, return the first 1000], GetPage(1) --> skip = 1 * 1000 = 1000, top = 1000 [skip the first 1000 entities, return the next 1000], and so on).
  4. Now we have a list of pages (with each page containing a list of entities). To create a single list of all entities, we have to join the list of pages. Finally, we can simply convert the list into a table.

The Execution: