文章/答案/技术大牛

发布

社区首页 >问答首页 >电力查询分页

问电力查询分页
EN

Stack Overflow用户

提问于 2022-09-23 19:39:24

回答 1查看 114关注 0票数 0

我对电源查询还不熟悉。我希望为我的业务案例创建一个迭代分页脚本。

请注意，我不能提供我正在使用的API键或端点，所以您将无法在您的端测试它，我将不得不在我的端测试代码。我正在寻找一些建议的代码，我将在我这边进行测试。

下面显示了一个页面的工作请求代码(请注意所有的Expand...内容，这使我搞不清如何分页处理这个请求)。其结果是与电子邮件相关的上下文数据：

// Working for 1 page
let
    // Define some variables
    _url = "https://www.randomURLNOTWORKING.com/events?before=9999999999",
    _headers = [authorization="Bearer XXXXXXXXXXXXXXXXX"],

    // Request the JSON response
    Source = Json.Document(Web.Contents(_url, [Headers=_headers])),
    
    // Unpack the response
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded _pagination" = Table.ExpandRecordColumn(#"Converted to Table", "_pagination", {"next"}, {"_pagination.next"}),
    #"Expanded _links" = Table.ExpandRecordColumn(#"Expanded _pagination", "_links", {"self"}, {"_links.self"}),
    #"Expanded _results" = Table.ExpandListColumn(#"Expanded _links", "_results"),
    #"Expanded _results1" = Table.ExpandRecordColumn(#"Expanded _results", "_results", {"_links", "id", "type", "emitted_at", "conversation", "source", "target"}, {"_results._links", "_results.id", "_results.type", "_results.emitted_at", "_results.conversation", "_results.source", "_results.target"}),
    #"Expanded _results._links" = Table.ExpandRecordColumn(#"Expanded _results1", "_results._links", {"self"}, {"_results._links.self"}),
    #"Expanded _results.conversation" = Table.ExpandRecordColumn(#"Expanded _results._links", "_results.conversation", {"_links", "id", "subject", "status", "assignee", "recipient", "tags", "links", "created_at", "is_private", "scheduled_reminders", "metadata"}, {"_results.conversation._links", "_results.conversation.id", "_results.conversation.subject", "_results.conversation.status", "_results.conversation.assignee", "_results.conversation.recipient", "_results.conversation.tags", "_results.conversation.links", "_results.conversation.created_at", "_results.conversation.is_private", "_results.conversation.scheduled_reminders", "_results.conversation.metadata"}),
    #"Expanded _results.conversation._links" = Table.ExpandRecordColumn(#"Expanded _results.conversation", "_results.conversation._links", {"self", "related"}, {"_results.conversation._links.self", "_results.conversation._links.related"}),
    #"Expanded _results.conversation._links.related" = Table.ExpandRecordColumn(#"Expanded _results.conversation._links", "_results.conversation._links.related", {"events", "followers", "messages", "comments", "inboxes", "last_message"}, {"_results.conversation._links.related.events", "_results.conversation._links.related.followers", "_results.conversation._links.related.messages", "_results.conversation._links.related.comments", "_results.conversation._links.related.inboxes", "_results.conversation._links.related.last_message"}),
    #"Expanded _results.conversation.recipient" = Table.ExpandRecordColumn(#"Expanded _results.conversation._links.related", "_results.conversation.recipient", {"_links", "name", "handle", "role"}, {"_results.conversation.recipient._links", "_results.conversation.recipient.name", "_results.conversation.recipient.handle", "_results.conversation.recipient.role"}),
    #"Expanded _results.conversation.recipient._links" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient", "_results.conversation.recipient._links", {"related"}, {"_results.conversation.recipient._links.related"}),
    #"Expanded _results.conversation.recipient._links.related" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient._links", "_results.conversation.recipient._links.related", {"contact"}, {"_results.conversation.recipient._links.related.contact"}),
    #"Expanded _results.conversation.metadata" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient._links.related", "_results.conversation.metadata", {}, {}),
    #"Expanded _results.source" = Table.ExpandRecordColumn(#"Expanded _results.conversation.metadata", "_results.source", {"_meta", "data"}, {"_results.source._meta", "_results.source.data"}),
    #"Expanded _results.source._meta" = Table.ExpandRecordColumn(#"Expanded _results.source", "_results.source._meta", {"type"}, {"_results.source._meta.type"}),
    #"Expanded _results.target" = Table.ExpandRecordColumn(#"Expanded _results.source._meta", "_results.target", {"_meta", "data"}, {"_results.target._meta", "_results.target.data"}),
    #"Expanded _results.target._meta" = Table.ExpandRecordColumn(#"Expanded _results.target", "_results.target._meta", {"type"}, {"_results.target._meta.type"}),
    #"Expanded _results.target.data" = Table.ExpandRecordColumn(#"Expanded _results.target._meta", "_results.target.data", {"_links", "id", "type", "is_inbound", "name", "highlight", "is_private", "is_visible_in_conversation_lists", "updated_at", "created_at", "blurb", "body", "text", "error_type", "version", "subject", "draft_mode", "metadata", "posted_at", "author", "recipients", "attachments", "signature", "is_draft", "email", "username", "first_name", "last_name", "is_admin", "is_available", "is_blocked", "custom_fields"}, {"_results.target.data._links", "_results.target.data.id", "_results.target.data.type", "_results.target.data.is_inbound", "_results.target.data.name", "_results.target.data.highlight", "_results.target.data.is_private", "_results.target.data.is_visible_in_conversation_lists", "_results.target.data.updated_at", "_results.target.data.created_at", "_results.target.data.blurb", "_results.target.data.body", "_results.target.data.text", "_results.target.data.error_type", "_results.target.data.version", "_results.target.data.subject", "_results.target.data.draft_mode", "_results.target.data.metadata", "_results.target.data.posted_at", "_results.target.data.author", "_results.target.data.recipients", "_results.target.data.attachments", "_results.target.data.signature", "_results.target.data.is_draft", "_results.target.data.email", "_results.target.data.username", "_results.target.data.first_name", "_results.target.data.last_name", "_results.target.data.is_admin", "_results.target.data.is_available", "_results.target.data.is_blocked", "_results.target.data.custom_fields"}),
    #"Expanded _results.target.data._links" = Table.ExpandRecordColumn(#"Expanded _results.target.data", "_results.target.data._links", {"self", "related"}, {"_results.target.data._links.self", "_results.target.data._links.related"}),
    #"Expanded _results.target.data._links.related" = Table.ExpandRecordColumn(#"Expanded _results.target.data._links", "_results.target.data._links.related", {"conversation", "message_seen", "mentions", "inboxes", "conversations", "owner", "parent_tag", "children", "message_replied_to"}, {"_results.target.data._links.related.conversation", "_results.target.data._links.related.message_seen", "_results.target.data._links.related.mentions", "_results.target.data._links.related.inboxes", "_results.target.data._links.related.conversations", "_results.target.data._links.related.owner", "_results.target.data._links.related.parent_tag", "_results.target.data._links.related.children", "_results.target.data._links.related.message_replied_to"}),
    #"Expanded _results.target.data.metadata" = Table.ExpandRecordColumn(#"Expanded _results.target.data._links.related", "_results.target.data.metadata", {}, {}),
    #"Expanded _results.target.data.custom_fields" = Table.ExpandRecordColumn(#"Expanded _results.target.data.metadata", "_results.target.data.custom_fields", {}, {}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded _results.target.data.custom_fields",{{"_pagination.next", type text}, {"_links.self", type text}, {"_results._links.self", type text}, {"_results.id", type text}, {"_results.type", type text}, {"_results.emitted_at", type number}, {"_results.conversation._links.self", type text}, {"_results.conversation._links.related.events", type text}, {"_results.conversation._links.related.followers", type text}, {"_results.conversation._links.related.messages", type text}, {"_results.conversation._links.related.comments", type text}, {"_results.conversation._links.related.inboxes", type text}, {"_results.conversation._links.related.last_message", type text}, {"_results.conversation.id", type text}, {"_results.conversation.subject", type text}, {"_results.conversation.status", type text}, {"_results.conversation.assignee", type any}, {"_results.conversation.recipient._links.related.contact", type text}, {"_results.conversation.recipient.name", type text}, {"_results.conversation.recipient.handle", type text}, {"_results.conversation.recipient.role", type text}, {"_results.conversation.tags", type any}, {"_results.conversation.links", type any}, {"_results.conversation.created_at", type number}, {"_results.conversation.is_private", type logical}, {"_results.conversation.scheduled_reminders", type any}, {"_results.source._meta.type", type text}, {"_results.source.data", type any}, {"_results.target._meta.type", type text}, {"_results.target.data._links.self", type text}, {"_results.target.data._links.related.conversation", type text}, {"_results.target.data._links.related.message_seen", type text}, {"_results.target.data._links.related.mentions", type text}, {"_results.target.data._links.related.inboxes", type text}, {"_results.target.data._links.related.conversations", type text}, {"_results.target.data._links.related.owner", type text}, {"_results.target.data._links.related.parent_tag", type any}, {"_results.target.data._links.related.children", type any}, {"_results.target.data._links.related.message_replied_to", type text}, {"_results.target.data.id", type text}, {"_results.target.data.type", type text}, {"_results.target.data.is_inbound", type logical}, {"_results.target.data.name", type text}, {"_results.target.data.highlight", type any}, {"_results.target.data.is_private", type logical}, {"_results.target.data.is_visible_in_conversation_lists", type logical}, {"_results.target.data.updated_at", type number}, {"_results.target.data.created_at", type number}, {"_results.target.data.blurb", type text}, {"_results.target.data.body", type text}, {"_results.target.data.text", type text}, {"_results.target.data.error_type", type any}, {"_results.target.data.version", type any}, {"_results.target.data.subject", type text}, {"_results.target.data.draft_mode", type any}, {"_results.target.data.posted_at", type number}, {"_results.target.data.author", type any}, {"_results.target.data.recipients", type any}, {"_results.target.data.attachments", type any}, {"_results.target.data.signature", type any}, {"_results.target.data.is_draft", type logical}, {"_results.target.data.email", type text}, {"_results.target.data.username", type text}, {"_results.target.data.first_name", type text}, {"_results.target.data.last_name", type text}, {"_results.target.data.is_admin", type logical}, {"_results.target.data.is_available", type logical}, {"_results.target.data.is_blocked", type logical}})
in
    #"Changed Type"

请注意，我已经查看了下面链接的所有文章，但仍然难以获得正确的语法，因为这是我的第一个Power查询项目。我有一个来自这篇文章的mod伪表示代码，如下所示：

// Attempt at pagination
let

    // Define some variables
    _url = "https://www.randomURLNOTWORKING.com/events?before=9999999999",
    _headers = [authorization="Bearer XXXXXXXXXXXXXXXXX"],
    _iterations = 3,

    // Function - Get the results of current page
    FnGetOnePage =
        (_url) as record =>
            let
                // Get the JSON response of the current page
                Source = Json.Document(Web.Contents(_url, [Headers=_headers])),
                ///////////////////////////////// Below is not appropriate for my use case
                //data = Source[data],
                //next = Source[paging][next],

                // Return the "data" (I'm assuming it should be in unpacked form?) and the "next" pagination link
                res = [Data=data, Next=next]
            in
                res,

    // Function - Generate a list of results
    // Not sure if this is what my use case needs either but leaving as pseudo
    GeneratedList =
        List.Generate(
            ()=>[i=0, res = FnGetOnePage(_url)],
            each [i]<_iterations and [res][Data]<>null,
            each [i=[i]+1, res = FnGetOnePage([res][Next])],
            each [res][Data]),

    // Unpack all of the results
    #"Converted to Table" = Table.FromRecords(GeneratedList),
    #"Expanded _pagination" = Table.ExpandRecordColumn(#"Converted to Table", "_pagination", {"next"}, {"_pagination.next"}),
    #"Expanded _links" = Table.ExpandRecordColumn(#"Expanded _pagination", "_links", {"self"}, {"_links.self"}),
    #"Expanded _results" = Table.ExpandListColumn(#"Expanded _links", "_results"),
    #"Expanded _results1" = Table.ExpandRecordColumn(#"Expanded _results", "_results", {"_links", "id", "type", "emitted_at", "conversation", "source", "target"}, {"_results._links", "_results.id", "_results.type", "_results.emitted_at", "_results.conversation", "_results.source", "_results.target"}),
    #"Expanded _results._links" = Table.ExpandRecordColumn(#"Expanded _results1", "_results._links", {"self"}, {"_results._links.self"}),
    #"Expanded _results.conversation" = Table.ExpandRecordColumn(#"Expanded _results._links", "_results.conversation", {"_links", "id", "subject", "status", "assignee", "recipient", "tags", "links", "created_at", "is_private", "scheduled_reminders", "metadata"}, {"_results.conversation._links", "_results.conversation.id", "_results.conversation.subject", "_results.conversation.status", "_results.conversation.assignee", "_results.conversation.recipient", "_results.conversation.tags", "_results.conversation.links", "_results.conversation.created_at", "_results.conversation.is_private", "_results.conversation.scheduled_reminders", "_results.conversation.metadata"}),
    #"Expanded _results.conversation._links" = Table.ExpandRecordColumn(#"Expanded _results.conversation", "_results.conversation._links", {"self", "related"}, {"_results.conversation._links.self", "_results.conversation._links.related"}),
    #"Expanded _results.conversation._links.related" = Table.ExpandRecordColumn(#"Expanded _results.conversation._links", "_results.conversation._links.related", {"events", "followers", "messages", "comments", "inboxes", "last_message"}, {"_results.conversation._links.related.events", "_results.conversation._links.related.followers", "_results.conversation._links.related.messages", "_results.conversation._links.related.comments", "_results.conversation._links.related.inboxes", "_results.conversation._links.related.last_message"}),
    #"Expanded _results.conversation.recipient" = Table.ExpandRecordColumn(#"Expanded _results.conversation._links.related", "_results.conversation.recipient", {"_links", "name", "handle", "role"}, {"_results.conversation.recipient._links", "_results.conversation.recipient.name", "_results.conversation.recipient.handle", "_results.conversation.recipient.role"}),
    #"Expanded _results.conversation.recipient._links" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient", "_results.conversation.recipient._links", {"related"}, {"_results.conversation.recipient._links.related"}),
    #"Expanded _results.conversation.recipient._links.related" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient._links", "_results.conversation.recipient._links.related", {"contact"}, {"_results.conversation.recipient._links.related.contact"}),
    #"Expanded _results.conversation.metadata" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient._links.related", "_results.conversation.metadata", {}, {}),
    #"Expanded _results.source" = Table.ExpandRecordColumn(#"Expanded _results.conversation.metadata", "_results.source", {"_meta", "data"}, {"_results.source._meta", "_results.source.data"}),
    #"Expanded _results.source._meta" = Table.ExpandRecordColumn(#"Expanded _results.source", "_results.source._meta", {"type"}, {"_results.source._meta.type"}),
    #"Expanded _results.target" = Table.ExpandRecordColumn(#"Expanded _results.source._meta", "_results.target", {"_meta", "data"}, {"_results.target._meta", "_results.target.data"}),
    #"Expanded _results.target._meta" = Table.ExpandRecordColumn(#"Expanded _results.target", "_results.target._meta", {"type"}, {"_results.target._meta.type"}),
    #"Expanded _results.target.data" = Table.ExpandRecordColumn(#"Expanded _results.target._meta", "_results.target.data", {"_links", "id", "type", "is_inbound", "name", "highlight", "is_private", "is_visible_in_conversation_lists", "updated_at", "created_at", "blurb", "body", "text", "error_type", "version", "subject", "draft_mode", "metadata", "posted_at", "author", "recipients", "attachments", "signature", "is_draft", "email", "username", "first_name", "last_name", "is_admin", "is_available", "is_blocked", "custom_fields"}, {"_results.target.data._links", "_results.target.data.id", "_results.target.data.type", "_results.target.data.is_inbound", "_results.target.data.name", "_results.target.data.highlight", "_results.target.data.is_private", "_results.target.data.is_visible_in_conversation_lists", "_results.target.data.updated_at", "_results.target.data.created_at", "_results.target.data.blurb", "_results.target.data.body", "_results.target.data.text", "_results.target.data.error_type", "_results.target.data.version", "_results.target.data.subject", "_results.target.data.draft_mode", "_results.target.data.metadata", "_results.target.data.posted_at", "_results.target.data.author", "_results.target.data.recipients", "_results.target.data.attachments", "_results.target.data.signature", "_results.target.data.is_draft", "_results.target.data.email", "_results.target.data.username", "_results.target.data.first_name", "_results.target.data.last_name", "_results.target.data.is_admin", "_results.target.data.is_available", "_results.target.data.is_blocked", "_results.target.data.custom_fields"}),
    #"Expanded _results.target.data._links" = Table.ExpandRecordColumn(#"Expanded _results.target.data", "_results.target.data._links", {"self", "related"}, {"_results.target.data._links.self", "_results.target.data._links.related"}),
    #"Expanded _results.target.data._links.related" = Table.ExpandRecordColumn(#"Expanded _results.target.data._links", "_results.target.data._links.related", {"conversation", "message_seen", "mentions", "inboxes", "conversations", "owner", "parent_tag", "children", "message_replied_to"}, {"_results.target.data._links.related.conversation", "_results.target.data._links.related.message_seen", "_results.target.data._links.related.mentions", "_results.target.data._links.related.inboxes", "_results.target.data._links.related.conversations", "_results.target.data._links.related.owner", "_results.target.data._links.related.parent_tag", "_results.target.data._links.related.children", "_results.target.data._links.related.message_replied_to"}),
    #"Expanded _results.target.data.metadata" = Table.ExpandRecordColumn(#"Expanded _results.target.data._links.related", "_results.target.data.metadata", {}, {}),
    #"Expanded _results.target.data.custom_fields" = Table.ExpandRecordColumn(#"Expanded _results.target.data.metadata", "_results.target.data.custom_fields", {}, {}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded _results.target.data.custom_fields",{{"_pagination.next", type text}, {"_links.self", type text}, {"_results._links.self", type text}, {"_results.id", type text}, {"_results.type", type text}, {"_results.emitted_at", type number}, {"_results.conversation._links.self", type text}, {"_results.conversation._links.related.events", type text}, {"_results.conversation._links.related.followers", type text}, {"_results.conversation._links.related.messages", type text}, {"_results.conversation._links.related.comments", type text}, {"_results.conversation._links.related.inboxes", type text}, {"_results.conversation._links.related.last_message", type text}, {"_results.conversation.id", type text}, {"_results.conversation.subject", type text}, {"_results.conversation.status", type text}, {"_results.conversation.assignee", type any}, {"_results.conversation.recipient._links.related.contact", type text}, {"_results.conversation.recipient.name", type text}, {"_results.conversation.recipient.handle", type text}, {"_results.conversation.recipient.role", type text}, {"_results.conversation.tags", type any}, {"_results.conversation.links", type any}, {"_results.conversation.created_at", type number}, {"_results.conversation.is_private", type logical}, {"_results.conversation.scheduled_reminders", type any}, {"_results.source._meta.type", type text}, {"_results.source.data", type any}, {"_results.target._meta.type", type text}, {"_results.target.data._links.self", type text}, {"_results.target.data._links.related.conversation", type text}, {"_results.target.data._links.related.message_seen", type text}, {"_results.target.data._links.related.mentions", type text}, {"_results.target.data._links.related.inboxes", type text}, {"_results.target.data._links.related.conversations", type text}, {"_results.target.data._links.related.owner", type text}, {"_results.target.data._links.related.parent_tag", type any}, {"_results.target.data._links.related.children", type any}, {"_results.target.data._links.related.message_replied_to", type text}, {"_results.target.data.id", type text}, {"_results.target.data.type", type text}, {"_results.target.data.is_inbound", type logical}, {"_results.target.data.name", type text}, {"_results.target.data.highlight", type any}, {"_results.target.data.is_private", type logical}, {"_results.target.data.is_visible_in_conversation_lists", type logical}, {"_results.target.data.updated_at", type number}, {"_results.target.data.created_at", type number}, {"_results.target.data.blurb", type text}, {"_results.target.data.body", type text}, {"_results.target.data.text", type text}, {"_results.target.data.error_type", type any}, {"_results.target.data.version", type any}, {"_results.target.data.subject", type text}, {"_results.target.data.draft_mode", type any}, {"_results.target.data.posted_at", type number}, {"_results.target.data.author", type any}, {"_results.target.data.recipients", type any}, {"_results.target.data.attachments", type any}, {"_results.target.data.signature", type any}, {"_results.target.data.is_draft", type logical}, {"_results.target.data.email", type text}, {"_results.target.data.username", type text}, {"_results.target.data.first_name", type text}, {"_results.target.data.last_name", type text}, {"_results.target.data.is_admin", type logical}, {"_results.target.data.is_available", type logical}, {"_results.target.data.is_blocked", type logical}})
in
    #"Changed Type"

显然，这段代码不起作用，我认为这已经使我想要做的事情复杂化了。

需要注意的是，对于开发人员来说，下一个请求没有使用“页码”或任何东西，所以我根本不需要在迭代循环中访问i。基本上，流程应该如下所示：

在持续时间内启动for循环直到_iterations
从_url请求内容
使用所有这些#解压响应的细节，以便将其转换为适当的表格式(如果在此阶段需要的话？)
从上一个请求从_pagination.next链接请求内容。
重复步骤3-4，直到迭代完成。
审查表格
利润！

因此，我正在寻找的文章中的代码可能不是最好的方法，而其他文章中的代码似乎是过火了。这些是我到目前为止看过的文章，我对需要发生的事情有一个概念上的理解，但从语法上讲，我只是还没有。

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

https://gist.github.com/MarkTiedemann/f667c75cc3d054b9b2bce25ea08bc631

https://comertechnology.com/cw-manage-getting-started-with-powerbi-the-update/?fbclid=IwAR0ReCaaX7KgXR8CsozWsbIzCH1vzLXM0C3ZQFAbBKoVjGAUEJImNnj9kLo

https://community.powerbi.com/t5/Desktop/Pagination-of-a-REST-API-in-Power-Query-using-M/td-p/1901067

为了帮助进一步描述我在这里寻找的更新，我在这里创建了下面所示的1个API调用的工作示例：

import requests

url = "https://www.randomURLNOTWORKING.com/events?before=9999999999"

payload={}
headers = {
  'authorization': 'Bearer XXXXXXXXXXXXX'
}

response = requests.request("GET", url, headers=headers, data=payload)

print(response.text)

响应

{
  "_pagination": {
    "next": "https://www.randomURLNOTWORKING.com.com/events?before=9999999999&page_token=XXXX22222"
  },
  "_links": {
    "self": "https://www.randomURLNOTWORKING.com.com/events?before=9999999999"
  },
  "_results": [
    {
      "_links": {
        "self": "https://www.randomURLNOTWORKING.com.com/2"
      },
      "id": "evt_2tejxwp8",
      "type": "assign",
      "emitted_at": 1664213539.745,
      "conversation": {
        "_links": {
          "self": "https://www.randomURLNOTWORKING.com.com/3",
          "related": {
            "events": "https://www.randomURLNOTWORKING.com.com/4"
          }
        }
      }
    }
  ]
}

此响应已被截断，并隐藏了任何业务信息。

因此，我们可以看到嵌套的字典/JSON响应，其中response["_pagination"]["next"]值是下一个循环迭代中要使用的链接。因此，Python版本的外观如下所示：

import requests

url = "https://www.randomURLNOTWORKING.com/events?before=9999999999"
_iterations = 10

payload={}
headers = {
  'authorization': 'Bearer XXXXXXXXXX'
}

# Perform the first request
response = requests.request("GET", url, headers=headers, data=payload)
print(response.text)

# Start iterating
for _ in range(_iterations):
    
    next_url = response["_pagination"]["next"]
    response = requests.request("GET", next_url, headers=headers, data=payload)
    print(response.text)

但是，这里缺少的是将所有嵌套的JSON字典“扩展”到它们自己的列中，我已经在OQ中介绍过了，我只是不清楚如何在Power Query中对这个语法进行编程。

我的目标是，一旦在这里找到一个答案，它将帮助其他人使用Power Query执行分页，其中您的“下一步”链接位于嵌套的JSON中。希望知道Python的人能够看到这一点，并将其转换为Power Query，以便我进行测试。

powerquery

pagination

powerbi

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-09-26 21:32:12

我已经搞清楚了，工作代码如下：

// Attempt at pagination
let

    // Define some variables
    _url = "https://www.randomURLNOTWORKING.com/events?before=9999999999",
    _headers = [authorization="Bearer XXXXXXXXX"],
    _iterations = 3,

    // Function - Get the results of current page
    FnGetOnePage =
        (input_url) as record =>
            let
                // Get the JSON response of the current page
                Source = Json.Document(Web.Contents(input_url, [Headers=_headers])),

                data = Source[_results],
                next = Source[_pagination][next],

                // Return the "data"
                res = [Data=data, Next=next]
            in
                res,

    // Function - Generate a list of results
    GeneratedList =
        List.Generate(
            ()=>[i=0, res = FnGetOnePage(_url)],
            each [i]<_iterations and [res][Data]<>null,
            each [i=[i]+1, res = FnGetOnePage([res][Next])],
            each [res][Data]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

大部分的“扩展”将在最后一个in行的上方完成，但是这可以根据需要在Power中可视化地完成。

所以我所做的就是创建一个迭代，在这里我查询端点_iterations时间，在FnGetOnePage函数中，我返回[_pagination][next]链接以及未扩展的_results数据。

在迭代完成之后，所有的解压缩都是从GeneratedList变量中完成的，该变量包含了我需要展开的所有信息。非常基本的我会想到，但谢谢你让我解决这个问题，希望它能帮助别人！

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/73832274

复制

相似问题

问电力查询分页
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问电力查询分页EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问电力查询分页
EN