Power BI: Handling Paginated API responses

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:

Power BI needs to know how to fetch other pages

If you follow the instructions on this pageConnecting Power BI to Learn Amp API, 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”

Open

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:

Open

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 Connecting Power BI to Learn Amp API

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:

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:

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