Power BI: Handling Paginated API Responses

Overview

When querying the Learn Amp API, results are returned in paginated form. This guide shows you how to build a Power BI query that fetches all pages of data automatically.

💡 Tip: If you have access to the Advanced Analytics Data Warehouse, the Data Lake connection handles this automatically—no pagination logic required. See Getting Started with Data Lake.

This guide assumes you have already completed the basic setup in Connecting Power BI to Learn Amp API.

Functionality Breakdown

Understanding API Pagination

The Learn Amp API always returns results in paginated form. For example, if you have 1,000 activity records, only the first page (10 results by default) will be returned.

Pagination Headers

API Response Header	Example	Description

API Response Header	Example	Description
`page`	1	Current page number
`per-page`	10	Results per page (default: 10, max: 50)
`total-pages`	62	Total pages available
`total`	620	Total records matching your query

You can request more results per page by adding perPage=50 to your request (maximum 50).

Pre-requisites

Role Requirements

Action	Required Role

Action	Required Role
Access API credentials	Owner
Use existing credentials	Any role (with credentials provided)

Technical Requirements

Completed setup from Connecting Power BI to Learn Amp API
Valid access token
Basic familiarity with Power Query M language

Quick Start Guide

Step 1: Create a Blank Query

In Power BI Desktop, go to Home → Transform data
Click New Source → Blank Query

Step 2: Create a Function Called "getActivity"

This function will be called once per page of results.

Name your query getActivity
Click on the formula bar (fx) and paste:

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

What this does:

Defines a function that accepts a page parameter
Calls the activities endpoint with date filter and pagination
Requests 50 results per page (maximum)
Includes your authorisation header

⚠️ Important: Replace YOUR_ACCESS_TOKEN with your actual token. You'll need to update this whenever you refresh data.

Step 3: Create a List of Page Numbers

Create another new blank query
Name it ActivityData
In the formula bar, enter:

= {1..20}

Replace 20 with your actual total pages (check the total-pages header from a test API call).

💡 Tip: If unsure of your total pages, make a test call to the API and note the total-pages value in the response headers.

Step 4: Convert the List to a Table

With your list selected, click Transform → To Table
Accept the default settings and click OK

Step 5: Convert Type to Text

Click the data type icon in the column header (shows "ABC 123")
Select Text

Step 6: Invoke the Custom Function

Go to Add Column → Invoke Custom Function
Configure:
- New column name: Data
- Function query: getActivity
- page: Select your column (Column1)
Click OK

Step 7: Expand the Data

Click the expand icon (↔) in the new Data column header
Select Expand to New Rows
You'll see a column called Data.activities (or similar)
Click the expand icon again on this column
Select the fields you want to include
Click OK

You now have all your paginated data in a single table!

FAQs

How do I know how many pages I need?

Make a single API request and check the total-pages response header. Use this number in Step 3.

Do I need to update my token each time?

Yes, access tokens expire. When refreshing data, generate a new token and update the getActivity function.

Can I use this for other endpoints?

Yes! Create similar functions for /v1/users, /v1/items, etc. Just adjust the endpoint URL and response structure.

Why is the refresh slow?

Each page requires a separate API call. For large datasets, consider:

Using the Data Lake connection instead (much faster)
Filtering by date to reduce data volume
Implementing incremental refresh

Troubleshooting

Issue	Solution

Issue	Solution
"Expression.Error" when invoking function	Check your token is valid and correctly formatted
Missing data	Ensure your page range covers all pages (check `total-pages`)
Timeout errors	Reduce `perPage` or add date filters to reduce data volume
Cannot expand data	Check the JSON structure matches expected format

Code Reference

Complete Power Query M Script

For reference, here's the full script that combines all steps:

let
    // Define the function to fetch a single page
    getPage = (pageNum as number) =>
        let
            Source = Json.Document(
                Web.Contents(
                    "https://api.learnamp.com/v1/activities",
                    [
                        Query = [
                            #"filters[date][from]" = "2024-01-01",
                            perPage = "50",
                            page = Text.From(pageNum)
                        ],
                        Headers = [Authorization = "Bearer YOUR_ACCESS_TOKEN"]
                    ]
                )
            )
        in
            Source,
    
    // Generate list of pages (adjust 20 to your total pages)
    PageList = {1..20},
    
    // Convert to table and fetch all pages
    PageTable = Table.FromList(PageList, Splitter.SplitByNothing()),
    RenamedColumns = Table.RenameColumns(PageTable, {{"Column1", "PageNumber"}}),
    AddedData = Table.AddColumn(RenamedColumns, "Data", each getPage([PageNumber])),
    
    // Expand the results
    ExpandedActivities = Table.ExpandRecordColumn(AddedData, "Data", {"activities"}, {"activities"}),
    ExpandedRows = Table.ExpandListColumn(ExpandedActivities, "activities")
in
    ExpandedRows

Next Steps

Explore other API endpoints in our API Documentation
Consider Getting Started with Data Lake for simpler, faster data access

Power BI: Handling Paginated API responses