Forum Discussion
Finding the most recent date/time for each row
My bad....Sorry i missed this point
Try this. See the attached file as well
Step 1: First Add a custom column to get the dates only without time.
=Int64.From([Dates])
Convert above integer to date format to get the date portion
Step 2: Group by ID and this DateOnly Column with Max of DateTime Column and All other rows
as follows
Step # 3: Now we use Table.Max function to get the other rows
Then expand table and remove unnecessary columns
Community Champion
My bad....Sorry i missed this point
Try this. See the attached file as well
Step 1: First Add a custom column to get the dates only without time.
=Int64.From([Dates])
Convert above integer to date format to get the date portion
Step 2: Group by ID and this DateOnly Column with Max of DateTime Column and All other rows
as follows
Step # 3: Now we use Table.Max function to get the other rows
Then expand table and remove unnecessary columns
Helper Ii spot two issues with the solution. 1. the switch of the date / time to date is not working as planned with the custom column in case you have multiple times within a date and some of them are near the end or start of date. the custom one will result to different dates from the original ones which does not make sense. why not to just duplicate the date/time col and then change the data type to just date?
then, even if we proceed with the other steps, at the end the result is the same like the initial state ie we have multiple rows for each date that there are more date/time entries..how are we supposed to filter out the earliest time slots and keep the latest only? i think there is something missing from this solution. any ideas?