如何从 Power BI 中的 API 获取分页数据 [英] How to get paginated data from API in Power BI
问题描述
假设我们有这个端点 https://reqres.in/api/users
.
响应是
<代码>{页面":1,每页":3,总计":12,"total_pages": 4,数据": [{身份证":1,"first_name": "乔治","last_name": "布鲁斯",头像":https://s3.amazonaws.com/uifaces/faces/twitter/calebogden/128.jpg"},{身份证":2,"first_name": "珍妮特","last_name": "织布工",头像":https://s3.amazonaws.com/uifaces/faces/twitter/josephstein/128.jpg"},{身份证":3,"first_name": "艾玛","last_name": "Wong",头像":https://s3.amazonaws.com/uifaces/faces/twitter/olegpogodaev/128.jpg"}]}
这是 PowerBI 中的结果
所以我的问题是:
- 如何处理
data
的内容,而不是只显示字符串"[List]"
? - PowerBI 可以处理分页吗?它可以更改
page
参数吗??page=X
如何处理数据的内容,而不是显示字符串仅[列表]"?
Power BI 实际上提供了一个用户友好的 UI 来导航和构建查询,因此您只需单击链接/按钮即可展开和深入查询并获取您想要的数据:
点击List
:
转换为表格:
展开列:
结果:
相当于下面的M/Power Query(Query -> Advanced Editor
):
letSource = Json.Document(Web.Contents("https://reqres.in/api/users")),数据 = 源[数据],#"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", "头像"})在#"扩展列1"
<小时><块引用>
PowerBI 可以处理分页吗?它可以更改页面参数吗??page=X
是的.您实际上可以将上述查询转换为函数,并将页码传递给它以获取每个页面的数据.
首先你可以从上面的查询中得到total_pages
:
右键单击total_pages
,添加为新查询
:
您将在 Query Editor
中看到以下查询:
letSource = Json.Document(Web.Contents("https://reqres.in/api/users")),total_pages1 = 来源[total_pages]在total_pages1
更改最后一行以生成数字列表:
letSource = Json.Document(Web.Contents("https://reqres.in/api/users")),列表 = {1..Source[total_pages]}在列表
将其转换为表格:
现在对于原始查询,您可以在查询之前添加 () =>
将其转换为函数,并将其传递给它的参数(API 端点也需要更改为分页):
(页面作为文本)=>让Source = Json.Document(Web.Contents("https://reqres.in/api/users?page=" & page)),数据 = 源[数据],#"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", "头像"})在#"扩展列1"
将函数重命名为 getPage 以便更好地理解:
现在回到 total_pages
表.将 Column1
更改为 text,以便稍后可以将其传递给 getPage
:
然后调用自定义函数
并使用Column1
调用getPage
:
您会看到旁边的表格列表:
展开它,您将在一个表格中看到所有数据页:
希望有帮助.
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 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.
这篇关于如何从 Power BI 中的 API 获取分页数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!