Forum Discussion
Solved
Remove duplicates and keep lowest (min) time
I have the data set below and need to find the earliest time and the associated activity for each date. I attempted a Group By with Min as an aggregate but wasn't successful.
Preferably, the results would look like the following. The status could either be Ready or Ringing depending on what time comes first on each date.
| Employee | Status | Date | Time |
| John Smith | Ready_111231 | 12/9 | 7:13 AM |
| John Smith | Ready_111231 | 12/10 | 7:06 AM |
| Margaret Cho | Ready_111231 | 12/9 | 11:30 AM |
| Margaret Cho | Ready_111231 | 12/10 | 11:30 AM |
| Maddy Knock | Ready_111231 | 12/12 | 4:28 PM |
| Maddy Knock | Ready_111231 | 12/13 | 10:49 AM |
The table formatting above works in the editing window but looks broken when posted. Sorry about that!
You can try the following code as your Table.Group
= Table.Group(previousStep, {"Employee", "Date"}, {{"all", each Table.SelectRows(_, (r)=> r[Time] = List.Min([Time])), type table [Employee=nullable text, Status=nullable text, Date=nullable date, Time=nullable time]}})Then expand the resulting table with the Time and Status columns.
1 Reply
You can try the following code as your Table.Group
= Table.Group(previousStep, {"Employee", "Date"}, {{"all", each Table.SelectRows(_, (r)=> r[Time] = List.Min([Time])), type table [Employee=nullable text, Status=nullable text, Date=nullable date, Time=nullable time]}})Then expand the resulting table with the Time and Status columns.