我是使用 Power Query for REST API 的新手，并且有一個 Power BI 報告需要修改以引入更多資料。我開發了一個查詢和自定義函式來回傳資料，它適用于單個 STATUS 請求引數值。這是在以下宣告中；
Source = Json.Document(Web.Contents("https://api.clockify.me/api/v1" & "/workspaces/"& Workspace&"/approval-requests?page="&Page&"&page-size="&PageSize&"&status="&Status,
[Headers=[#"X-API-Key"=#"X-API-Key",
#"Content-Type"="JSON"]]
)
),
但是，我需要在我的查詢中回傳所有四個狀態代碼（APPROVED、PENDING、REJECTED、APPROVAL_WITHDRAWN"）的值。Clockify 建議此 API 不支持多個引數值，我需要分別為每個狀態拉一個請求。不幸的是，我不知道如何重組我的查詢來做到這一點，所以非常感謝任何建議。
謝謝
完整的查詢和自定義功能如下圖所示；
let
Source = "",
Custom1 = List.Numbers(1 as number, 50 as number) as list,
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each get_page_fx([Column1])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Column1"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Errors",{"Column1"}),
#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Errors1", "Data", {"id", "workspaceId", "dateRange.start", "dateRange.end", "owner.userId", "owner.userName", "owner.timezone", "owner.startOfWeek", "status.state", "status.updatedBy", "status.updatedByUserName", "status.updatedAt", "status.note"}, {"Data.id", "Data.workspaceId", "Data.dateRange.start", "Data.dateRange.end", "Data.owner.userId", "Data.owner.userName", "Data.owner.timezone", "Data.owner.startOfWeek", "Data.status.state", "Data.status.updatedBy", "Data.status.updatedByUserName", "Data.status.updatedAt", "Data.status.note"})
in
#"Expanded Data
"
(Page as text) =>
let
Source = Json.Document(
Web.Contents("https://api.clockify.me/api/v1" & "/workspaces/"& Workspace&"/approval-requests?page="&Page&"&page-size="&PageSize&"&status="&Status,
[Headers=[#"X-API-Key"=#"X-API-Key",
#"Content-Type"="JSON"]]
)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "workspaceId", "dateRange", "owner", "status"}, {"id", "workspaceId", "dateRange", "owner", "status"}),
#"Expanded dateRange" = Table.ExpandRecordColumn(#"Expanded Column1", "dateRange", {"start", "end"}, {"dateRange.start", "dateRange.end"}),
#"Expanded owner" = Table.ExpandRecordColumn(#"Expanded dateRange", "owner", {"userId", "userName", "timezone", "startOfWeek"}, {"owner.userId", "owner.userName", "owner.timezone", "owner.startOfWeek"}),
#"Expanded status" = Table.ExpandRecordColumn(#"Expanded owner", "status", {"state", "updatedBy", "updatedByUserName", "updatedAt", "note"}, {"status.state", "status.updatedBy", "status.updatedByUserName", "status.updatedAt", "status.note"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded status",{{"id", type text}, {"workspaceId", type text}, {"dateRange.start", type datetime}, {"dateRange.end", type datetime}, {"owner.userId", type text}, {"owner.userName", type text}, {"owner.timezone", type text}, {"owner.startOfWeek", type text}, {"status.state", type text}, {"status.updatedBy", type text}, {"status.updatedByUserName", type text}, {"status.updatedAt", type datetime}, {"status.note", type any}})
in
#"Changed Type"
uj5u.com熱心網友回復：
硬編碼狀態到函式中。將該函式再復制 3 次，每個查詢名稱具有不同的狀態和不同的查詢名稱。在主查詢中，為 get_page_fx([Column1])) 添加一列。再一一添加三列，每列呼叫三個新函式之一：get_page_fx2([Column1])) get_page_fx3([Column1])) 和 get_page_fx4([Column1]))。這就是所有的資料。如果需要，您可以將列與附加組合
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/441683.html
標籤：休息 电源查询 powerbi-桌面 时钟化