Power BI: Handling Paginated API responses

This article assumes you have worked through our first article about Power BI
https://learnamp.atlassian.net/wiki/spaces/KB/pages/201000313

API Requests are always paginated

The Learn Amp API always returns results in paginated form. For example, if you have 1000 activity records matching your request parameters, page 1 of results will be returned which only contains the first 10 results by default.

The API response contains the following headers that tell you what other pages are available:

API Response Header

Example Value

Description

 

API Response Header

Example Value

Description

 

page

1

This is the current page number of paginated results

 

per-page

10

This is how many results are being returned per page. You can request more results per page, by adding perPage=50 for example, to your API request. 50 is the maximum.

 

total-pages

 

62

This is the total number of pages of results available for your request parameters.

 

Power BI needs to know how to fetch other pages

If you follow the instructions on this pagehttps://learnamp.atlassian.net/wiki/spaces/KB/pages/201000313, Power BI will ONLY fetch the first page of results. Most likely, you will want Power BI to fetch ALL the results for your request. This takes a bit of scripting. Let’s create a new Query in Power BI, and go step by step.

Step 1: Create a new Blank Query

Under “Get Data”, select “Blank Query”

 

Step 2: Create a function called “getActivity”

For this example, we are going to assume that you want to fetch your paginated activity data.

Give the query a name property of “getActivity”

In the fx input box, paste the following script:

= (page as text) => let Source = Json.Document(Web.Contents("https://api.learnamp.com/v1/activities" & "?filters[date][from]=2022-03-01&perPage=50&page=" & page, [Headers=[Authorization="Bearer YOUR-ACCESS-TOKEN"]])) in Source

What is this doing?

  1. It defines a JSON Web Source, to pull data from. It uses Learn Amp’s activities endpoint documented here.

  2. It adds parameters to the query to only return activity since March 1st 2022: filters[date][from]=2022-03-01

  3. It adds parameters to request 50 results per page: perPage=50

  4. It adds a DYNAMIC page parameter, which will use a variable called page: page=" & page`

  5. It adds the Authorization header: [Headers=[Authorization="Bearer YOUR-ACCESS-TOKEN"]]
    How to get an access token is described in https://learnamp.atlassian.net/wiki/spaces/KB/pages/201000313

IMPORTANT Do not forget to generate a new access token, and change “YOUR-ACCESS-TOKEN” for this new one, whenever you need to refresh the data in Power BI.

6. It places the above logic inside a function call, which takes the variable page as an arguement. Now this function can be called multiple times, each time with a different page variable, in order to fetch all the pages you need.

Step 3: Create a list up to the total number of pages of results

In your query, paste the following:

= {1..20}

Here 20 is the total pages of results from the total-pagesheader (see table above). If you have more pages, enter that number instead of 20 here.

Step 4: Convert the List to a Table

Now click “To Table - Convert” to convert this list of pages into a table.

Step 5: Convert type to Text

Click the “ ABC 123” button in the table header, to specify what Data Type the value should be. Select “Text”

 

Step 6: Invoke the getActivity custom function

Under the “Add Column” menu, click the “Invoke Custom Function” button. Select the getActivitycustom function you created in step 2, and select your page number column.

Step 7: Expand the data

You are almost there! This will have called the API once, per page of results. These pages now show as clickable lists on each row. Click the icon in the header row to expand the data. Click “Expand to new rows”.

Now expand a second time, by clicking the same expand button in the new column called Data.activities

This should expand the remaining data:

Congratulations - you have now build a pagination aware query.

Whenever you want to refresh data simply generate a new access token, edit the getActivity custom function, and refresh the data source.