How to get paginated data from API in Power BI

Question

Let's say we have this endpoint https://reqres.in/api/users.

The response is

{
    "page": 1,
    "per_page": 3,
    "total": 12,
    "total_pages": 4,
    "data": [
        {
            "id": 1,
            "first_name": "George",
            "last_name": "Bluth",
            "avatar": "https://s3.amazonaws.com/uifaces/faces/twitter/calebogden/128.jpg"
        },
        {
            "id": 2,
            "first_name": "Janet",
            "last_name": "Weaver",
            "avatar": "https://s3.amazonaws.com/uifaces/faces/twitter/josephstein/128.jpg"
        },
        {
            "id": 3,
            "first_name": "Emma",
            "last_name": "Wong",
            "avatar": "https://s3.amazonaws.com/uifaces/faces/twitter/olegpogodaev/128.jpg"
        }
    ]
}

Here is the result in PowerBI

So my questions are :

• How can I play with the content of data, instead of showing the string "[List]" only?
• Can PowerBI handle the pagination? Can it change the page param? ?page=X

Answer 1

How can I play with the content of data, instead of showing the string "[List]" only?

Power BI actually provides a user-friendly UI to navigate and construct the query, so you can just click on the links/buttons to expand and drill down the query and get the data you want:

Click List:

Convert to table:

Expand the column:

Results:

Which is equivalent to the following M / Power Query (Query -> Advanced Editor):

let
    Source = Json.Document(Web.Contents("https://reqres.in/api/users")),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "first_name", "last_name", "avatar"}, {"id", "first_name", "last_name", "avatar"})
in
    #"Expanded Column1"

---

Can PowerBI handle the pagination? Can it change the page param? ?page=X

Yes. You can actually convert the above query to a function, and pass it the page number to get the data for each page.

First you can get the total_pages from the above query:

Right click total_pages, Add as New query:

You'll see the following query in the Query Editor:

let
    Source = Json.Document(Web.Contents("https://reqres.in/api/users")),
    total_pages1 = Source[total_pages]
in
    total_pages1

Change the last line to generate a list of numbers:

let
    Source = Json.Document(Web.Contents("https://reqres.in/api/users")),
    List = {1..Source[total_pages]}
in
    List

Convert it to table:

Now for the original query, you can add () => before the query to convert it to a function, and pass it the parameter (The API endpoint needs to be changed as well for pagination):

(page as text) =>
let
    Source = Json.Document(Web.Contents("https://reqres.in/api/users?page=" & page)),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "first_name", "last_name", "avatar"}, {"id", "first_name", "last_name", "avatar"})
in
    #"Expanded Column1"

Rename the function to getPage for better understanding:

Now back to the total_pages table. Change the Column1 to text so that it can be passed to getPage later:

Then Invoke Custom Function and call the getPage with Column1:

You'll see a list of table alongside:

Expand it and you'll see all pages of data in one table:

Hope it helps.