Paging through APIs in Power BI

Written by Sumesh Dayaram

 

A source system is generally considered to be the piece of software your business interacts with daily. This system’s main function is to capture information about your business and record it.

 

Most source systems in a business have ways of extracting data from it. One of the most common ways of extracting data is through APIs. API’s implement paging to moderate the amount of data that can be accessed when using it. Due to this not all the data in your source system is directly available on the first call of the API. This is done to manage the strain that can be placed on the source system.

 

Power BI is a powerful tool that can do many things, from reporting and visualising data in great ways, transforming and relating data as well as shaping and extracting data. With regards to the latter, Power BI can extract data from many sources which means data will be presented in different forms and shapes.

 

Paging through API’s can be tricky at times, especially with limits to the number of records you can access on a page. Even tools such as Azure Data Factory need to implement complex solutions to solve this. In Power BI there is a method to solve this and gather all available data.

 

The API you’re calling will have to provide you with metadata to use the method described below:

 

 

In this list we can see :

  1. The number of records per page
  2. The number of pages of records
  3. The number of entries in total
  4. The next page in the sequence
  5. The previous page in the sequence
  6. The current page
  7. The URLs of the next, current, and previous calls.

 

From here you can follow the steps outlined in this thread “powerbi – How to get paginated data from API in Power BI – Stack Overflow” but there is one trick missing. Using this method will allow you to gather all the data but only refresh the data locally and not in the Power BI service. To do this, you will need to use the “RelativePath” Power Query function. This will have to wrap around the “page” and “headers” portion of your API call. The purpose of the “RelativePath” function is that it allows you to add extra text to the base URL. An example is as per the screenshot below:

 

 

In the service you can then tick the “skip the connection test” box in the online service.

This allows you to bring in all data from an API in Power BI and can save you a lot of time and effort in gathering data and surfacing it.