Transform table to show minimum time

[cottrera]

Hi I have atable like the example below. I would like to perform a tranformation the diplays only the minimum start time for each operative# for each date.

Operative#	Activity Date	Activity Type	Start Time
3016	03/10/2022	TRAVEL	07:51:00
3016	03/10/2022	TRAVEL	09:14:03
3016	03/10/2022	WORKING	09:40:58
3016	03/10/2022	TRAVEL	11:38:27
3016	04/10/2022	TRAVEL	07:39:00
3016	04/10/2022	WORKING	09:01:00
3016	04/10/2022	COLLECTING	10:46:00
3016	04/10/2022	TRAVEL	11:41:28
3016	04/10/2022	TRAVEL	14:08:24
3016	04/10/2022	WORKING	14:23:33
3016	05/10/2022	WORKING	09:03:00
3016	05/10/2022	TRAVEL	10:04:44
3016	05/10/2022	WORKING	10:29:22
3016	05/10/2022	WORKING	11:26:11
403487	06/10/2022	TRAVEL	10:20:02
403487	06/10/2022	WORKING	10:58:06
403487	06/10/2022	TRAVEL	12:31:53
403487	06/10/2022	WORKING	13:12:46
403487	07/10/2022	TRAINING	08:00:00
403487	07/10/2022	TRAVEL	10:11:18
403487	07/10/2022	WORKING	10:45:15
403487	07/10/2022	WORKING	13:07:34
403487	07/10/2022	TRAVEL	13:07:49
403487	10/10/2022	TRAVEL	07:03:26
403487	10/10/2022	WORKING	08:00:07
403487	10/10/2022	TRAVEL	08:22:09

I would like the end result to look like this

Operative#	Activity Date	Activity Type	Start Time
3016	03/10/2022	TRAVEL	07:51:00
3016	04/10/2022	TRAVEL	07:39:00
3016	05/10/2022	WORKING	09:03:00
403487	06/10/2022	TRAVEL	10:20:02
403487	07/10/2022	TRAINING	08:00:00
403487	10/10/2022	TRAVEL	07:03:26

regards

Richard

[cottrera]

Thank you for your quick response the code works fine👍

[BA_Pete · Accepted solution]

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