休息阿皮分页环..。Power查询M语言

Question

我想知道是否有人能帮我做api分页..。我正在尝试从外部api获取所有记录，但它限制了我最多只能获得10条记录。大约有40k条记录。api也没有显示"no.of页面“(下面的响应)。因此，我想不出解决办法。也不支持“跳过”、“计数”或“顶”。我是stuck...and，在获取所有记录之前，我不知道如何用M语言创建一个循环。有人能帮我写一段代码吗？

下面是我的密码。

let
  Source = Json.Document(
    Web.Contents(
              "https://api.somedummy.com/api/v2/Account",
  
               [ 

                    RelativePath ="Search",
                    Headers =
                      [
  
                          ApiKey = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx",
                          Authorization = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                          #"Content-Type" = "application/json"
                      ],

                    Content=
                      Json.FromValue(

                        [key="status", operator="EqualTo", value="Active", resultType="Full"]
                      )
                          

              ]
     
                          )
                )
in
Source

及以下是输出

"data": {
        "totalCount": 6705,
        "page": 1,
        "pageSize": 10,
        "list":[

Answer 1

这也许能帮你的忙。当我在寻找与Jira类似的信息时，我发现一些有用的信息来自亚特兰西安社区网站上的两个人。下面是我在他们的帖子帮助下开发的一个查询的相关片段。(要明确的是，这段代码是我在查询中使用的代码。)虽然我在下面提供更多的查询(其中的部分也由它们的代码组成)，但我认为与您的特定问题相关的关键部分是这个部分。

  yourJiraInstance = "https://site.atlassian.net/rest/api/2/search",
  Source = Json.Document(Web.Contents(yourJiraInstance, [Query=[maxResults="100",startAt="0"]])),
  totalIssuesCount = Source[total],
  // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
  startAtList = List.Generate(()=>0, each _ < totalIssuesCount, each _ +100),
  urlList = List.Transform(startAtList, each Json.Document(Web.Contents(yourJiraInstance, [Query=[maxResults="100",startAt=Text.From(_)]]))),
  // ===== Consolidate records into a single list ======
  // so we have all the records in data, but it is in a bunch of lists each 100 records
  // long. The issues will be more useful to us if they're consolidated into one long list

我在想，也许你可以试着用pageSize代替maxResults，用totalCount代替totalIssuesCount。我不知道startAt的事。一定有类似的东西提供给你。谁知道呢？可能是startAt。我相信你的pageSize是10，你会把你的startAt增加10而不是100。

这是尼克和蒂亚戈在这条线上的帖子。我想唯一真正的区别可能是我缓冲了一张桌子。(已经有一段时间了，我没有深入研究他们的思路，并将其作为答案进行比较。)

let
  // I must credit the first part of this code -- the part between the ********** lines -- as being from Nick Cerneaz (and Tiago Machado) from their posts on this thread:
  // https://community.atlassian.com/t5/Marketplace-Apps-Integrations/All-data-not-displayed-in-Power-BI-from-Jira/qaq-p/723117.
  // **********
  yourJiraInstance = "https://site.atlassian.net/rest/api/2/search",
  Source = Json.Document(Web.Contents(yourJiraInstance, [Query=[maxResults="100",startAt="0"]])),
  totalIssuesCount = Source[total],
  // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
  startAtList = List.Generate(()=>0, each _ < totalIssuesCount, each _ +100),
  urlList = List.Transform(startAtList, each Json.Document(Web.Contents(yourJiraInstance, [Query=[maxResults="100",startAt=Text.From(_)]]))),
  // ===== Consolidate records into a single list ======
  // so we have all the records in data, but it is in a bunch of lists each 100 records
  // long. The issues will be more useful to us if they're consolidated into one long list
  // 
  // In essence we need extract the separate lists of issues in each data{i}[issues] for 0<=i<#"total"
  // and concatenate those into single list of issues .. from which then we can analyse
  // 
  // to figure this out I found this post particulary helpful (thanks Vitaly!):
  // https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/
  // 
  // so first create a single list that has as its members each sub-list of the issues,
  // 100 in each except for the last one that will have just the residual list.
  // So iLL is a List of Lists (of issues):
  iLL = List.Generate(
     () => [i=-1, iL={} ],
     each [i] < List.Count(urlList),
     each [
         i = [i]+1,
         iL = urlList{i}[issues]
     ],
     each [iL]
 ),
  // and finally, collapse that list of lists into just a single list (of issues)
  issues = List.Combine(iLL),
  // Convert the list of issues records into a table
  #"Converted to table" = Table.Buffer(Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
  // **********