This is my dataset:
ID Work Date Clock In
| 27615 | 8/14/2022 | 8/14/2022 9:00 |
| 27615 | 8/14/2022 | 8/14/2022 15:30 |
| 27615 | 8/14/2022 | 8/15/2022 0:00 |
| 27615 | 8/17/2022 | 8/17/2022 0:00 |
| 27615 | 8/17/2022 | 8/17/2022 6:30 |
| 27615 | 8/18/2022 | 8/18/2022 0:06 |
| 27615 | 8/18/2022 | 8/18/2022 5:48 |
| 27615 | 8/19/2022 | 8/19/2022 0:06 |
| 27615 | 8/20/2022 | 8/20/2022 0:06 |
| 27615 | 8/20/2022 | 8/20/2022 6:36 |
and I need to create an Order column that outputs this:
ID Work Date Clock In Order
| 27615 | 8/14/2022 | 8/14/2022 9:00 | 1 |
| 27615 | 8/14/2022 | 8/14/202215:30 | 2 |
| 27615 | 8/14/2022 | 8/15/2022 0:00 | 3 |
| 27615 | 8/17/2022 | 8/17/2022 0:00 | 1 |
| 27615 | 8/17/2022 | 8/17/2022 6:30 | 2 |
| 27615 | 8/18/2022 | 8/18/2022 0:06 | 1 |
| 27615 | 8/18/2022 | 8/18/2022 5:48 | 2 |
| 27615 | 8/19/2022 | 8/19/2022 0:06 | 1 |
| 27615 | 8/20/2022 | 8/20/2022 0:06 | 1 |
| 27615 | 8/20/2022 | 8/20/2022 6:36 | 2 |
at the moment i am grouping and creating an indexing column, but it gives me this:
ID Clock In Order
| 27615 | 8/14/2022 9:00:00 AM | 1 |
| 27615 | 8/14/2022 3:30:00 PM | 2 |
| 27615 | 8/15/2022 12:00:00 AM | 3 |
| 27615 | 8/17/2022 12:00:00 AM | 4 |
| 27615 | 8/17/2022 6:30:00 AM | 5 |
| 27615 | 8/18/2022 12:06:00 AM | 6 |
| 27615 | 8/18/2022 5:48:00 AM | 7 |
| 27615 | 8/19/2022 12:06:00 AM | 8 |
| 27615 | 8/20/2022 12:06:00 AM | 9 |
| 27615 | 8/20/2022 6:36:00 AM | 10 |
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"ID"}, {{"Count", each _, type table [ID=text, Work Date=nullable date, Clock In=nullable datetime]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Clock In", "Index"}, {"Clock In", "Index"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Index", "Order"}})
in
#"Renamed Columns2"
