MS Power Query Looping

Question

This is a different looping question from Power Query Looping.

I'm using Power Query to pull data from api.automatic.com; specifically, a list of trips. I'm able to pull the first set of information, but I can't figure out how to loop to get all information.

This is what I have so far:

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"

The JSON that is output returns a value under _metadata.next that's the URL to get the next set of data. How to I have the PQ grab that value, repull using that URL, and continue doing so until that next value is null or blank?

Answer 1

Use List.Generate to pull the data until the next token is null.

List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list

Code with explanations (untested as I can't access the 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

Understanding what the each keyword does helps:

The each keyword is used to easily create simple functions. “each ...” is syntactic sugar for a function signature that takes the parameter “() => ...”

Each is useful when combined with the lookup operator, which is applied by default to For example, each [CustomerID] is the same as each _[CustomerID], which is the same as (_) => _[CustomerID]