json - MS Power查询循环

这是与Power Query Looping不同的循环问题。

我正在使用Power Query从api.automatic.com中提取数据；具体来说就是旅行清单。我能够提取第一组信息，但是我不知道如何循环获取所有信息。

这是我到目前为止的内容：

let
    Source = Web.Contents("https://api.automatic.com/trip/",[Headers=[#"Authorization"="Bearer XXX"]]),
    #"Imported JSON" = Json.Document(Source),
    results = #"Imported JSON"[results],
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"url", "id", "driver", "user", "started_at", "ended_at", "distance_m", "duration_s", "vehicle", "start_location", "start_address", "end_location", "end_address", "path", "fuel_cost_usd", "fuel_volume_l", "average_kmpl", "average_from_epa_kmpl", "score_events", "score_speeding", "hard_brakes", "hard_accels", "duration_over_70_s", "duration_over_75_s", "duration_over_80_s", "vehicle_events", "start_timezone", "end_timezone", "city_fraction", "highway_fraction", "night_driving_fraction", "idling_time_s", "tags"}, {"url", "id", "driver", "user", "started_at", "ended_at", "distance_m", "duration_s", "vehicle", "start_location", "start_address", "end_location", "end_address", "path", "fuel_cost_usd", "fuel_volume_l", "average_kmpl", "average_from_epa_kmpl", "score_events", "score_speeding", "hard_brakes", "hard_accels", "duration_over_70_s", "duration_over_75_s", "duration_over_80_s", "vehicle_events", "start_timezone", "end_timezone", "city_fraction", "highway_fraction", "night_driving_fraction", "idling_time_s", "tags"})
in
    #"Expanded Column1"

输出的JSON返回_metadata.next下的值，该值是获取下一组数据的URL。如何让PQ抓取该值，使用该URL重新拖动，然后继续执行直到next值为null或空白？

最佳答案

使用List.Generate拉数据，直到下一个标记为空。


List.Generate（初始为函数，条件为函数，下一个为函数，可选选择器为可为空的函数）作为列表


带有说明的代码（未经测试，请尝试一下，因为我无法访问API）：

let
    // Helper function to get results and next token.
    get = (url as any, headers as any) as record =>
        let
            source = Json.Document(Web.Contents(url, headers)),
            results = try source[results] otherwise null,
            next = try source[_metadata][next] otherwise null,
            return = [results=results, next=next]
        in
            return,

    url = "https://api.automatic.com/trip/",
    headers = [Headers=[#"Authorization"="Bearer XXX"]],

    // Returns a list of lists of records.
    return =
        List.Generate(
            // Initial value.
            ()=> get(url, headers),
            // If condition is true select (make a list) the result.
            each [results] <> null,
            // Generate the next list if condition is true.
            each get([next], headers),
            // Return (select) only [results].
            each [results])
in
    return

了解each关键字的作用有助于：


每个关键字用于轻松创建简单函数。 “每个...”是带有参数“（）=> ...”的函数签名的语法糖。

当与lookup运算符结合使用时，每个参数都非常有用，默认情况下将其应用于each [CustomerID]，与each _[CustomerID]相同，与(_) => _[CustomerID]相同。

关于json - MS Power查询循环，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35730469/