M语言对总结果的多个json查询

Question

我有一个rest查询，它最多返回500个记录。我还可以通过api获得记录的总数。

因此，我需要合并几个查询，以获得我的所有记录。此外，我需要该查询是动态的，并始终与总记录的最新。

这是最大1000条记录的示例代码：

let
    Source = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deal:(id,title,value,currency)?api_token=12345&limit=500&start=0")),
    Source2 = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deal:(id,title,value,currency)?api_token=12345&limit=500&start=500")),
    data = Source[data],
    data2 = Source2[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Converted to Table 2" = Table.FromList(data2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "currency"}, {"Column1.id", "Column1.title", "Column1.value", "Column1.currency"}),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "currency"}, {"Column1.id", "Column1.title", "Column1.value", "Column1.currency"}),
    #"Appended Query" = Table.Combine({#"Expanded Column1", #"Expanded Column2"})
in
    #"Expanded Column1"

我的总记录大约是11000张，而且每周都在变化。

我可以使用data.additional_data.total_records获得记录的总数。

我是否可以在变量上使用这个值，并使用某种时间来进行(总计记录/ 500)的查询？

有什么想法吗？

Answer 1

您可以将查询参数化并将其转换为函数，而不是一个接一个地获取数据和合并查询。

让我们假设您的原始查询如下：

let
    Source = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deal:(id,title,value,currency)?api_token=12345&limit=500&start=0")),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "currency"}, {"Column1.id", "Column1.title", "Column1.value", "Column1.currency"})
in
    #"Expanded Column1"

可以将start参数参数化，并将查询更改为GetDeal函数，如下所示：

(start as text) =>
let
    Source = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deal:(id,title,value,currency)?api_token=12345&limit=500&start=" & start)),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "currency"}, {"Column1.id", "Column1.title", "Column1.value", "Column1.currency"})
in
    #"Expanded Column1"

​

​

假设您将total_records存储为来自另一个查询的值，方法是导航到data.additional_data.total_records

​

​

现在您可以创建一个新的查询，并从0生成一个从total_records到total_records的列表，并增加500

let
    Source = List.Generate(() => 0, each _ <= (total_records), each _ + 500)
in
    Source

​

​

将其转换为表并将列更改为text

let
    Source = List.Generate(() => 0, each _ <= (total_records), each _ + 500),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "start"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"start", type text}})
in
    #"Changed Type"

​

​

现在我们可以使用列GetDeal调用自定义函数start，如下所示：

​

​

您应该有一个表的列表作为一个新的列，并可以从那里工作。您可以删除start列，因为它不再需要了。