如何处理数据的内容，而不是显示字符串仅[列表]"?

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.