How to get paginated data from API in Power BI
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:
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
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.
David
Updated on June 12, 2022Comments
-
David about 2 years
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
- How can I play with the content of