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
Please see attached file
Here are the steps
First Group the Columns like this from the Query Editor
Then you can add a custom column to extract the single row from these tables
I tried your idea Zubair_Muhammad but i have a problem,
The idea is to keep the max (date/time) for each day and for each 'id', not the max value of the whole column.
Maybe i didn't explain well... this is my table :
for Id=1 i have three values taken the 01/08/2018 and i should only keep the last one (01/08/2018 8:35:12), same rule for every id each day (in case i have more than one value for each id)
- Zubair_Muhammad
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
Hi, I just found this as i'm trying to do the same thing so I opened your file - but I can see, that you still have three values for ID 1.
For my data, I want to only keep the ID 1 with the most recent date, and the other rows should not be present.
So in your example, at the end, I would only want to keep the highlighted rows.
lemontreeHelper I
Anonymous i have the same issue. the suggested solution does not really seem work. did you manage to find a way to get the latest date/time for each row?
- lemontree
i 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?