Transform table to show minimum time

Hi cottrera ,

Paste this into a new blank query to see the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZPLisJAEEX/JWvBevUjdycig0xQENGF+P+/YWlmhnTGjr1Mc7h96nblduuUOHarjnTNtBYS8Y/zaXPZDc/ThMAg6u6rT2QPNpBWyevx9L0/fI2oEUL+HMoMzZBUkFYR1X4uarXr6d9MU3R7HIbd9jzSTLC4RE9ljSG5gfSifCxrkHVUFFrWGqpz6dw0vL2fQAazhlBHpYcfNqA+fATzCzVSy+kJx4qBuIQssIVDyKDYFCxQRtC2YIXzNg9OZfD+8NOvO9Bvv1X6bz6vg/MCW8xnARzaYH9i33Vrsnix1pesg+9/IN8eiQvsZNXGKlJTsG+6gFzi/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Operative#" = _t, #"Activity Date" = _t, #"Activity Type" = _t, #"Start Time" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Operative#", Int64.Type}, {"Activity Date", type date}, {"Activity Type", type text}, {"Start Time", type time}}),
    groupAllRows = Table.Group(chgTypes, {"Operative#", "Activity Date"}, {{"data", each _, type table [#"Operative#"=nullable number, Activity Date=nullable date, Activity Type=nullable text, Start Time=nullable time]}}),
    addMinStart = Table.AddColumn(groupAllRows, "Custom", each Table.Min([data], "Start Time")),
    expandMinStartRecord = Table.ExpandRecordColumn(addMinStart, "Custom", {"Activity Type", "Start Time"}, {"Activity Type", "Start Time"}),
    remOthCols = Table.SelectColumns(expandMinStartRecord,{"Operative#", "Activity Date", "Activity Type", "Start Time"})
in
    remOthCols

Summary:

1) groupAllRows = Group table on [Operative] and [Activity Date], and use the 'All Rows' operator for the aggregation column.

2) addMinStart = Add a new column using Table.Min to pick out the rows from the nested tables that have the lowest [Start Time].

3) expandMinStartRecord = Expand the new record column to reinstate the the columns you want to the main table.

Output:

Pete