Finding the most recent date/time for each row

[rostromarre]

Hello all,

I have a table with a date/time column with differents values and i would like to keep only the latest value for each row . Bassicaly i have this:

a

and I would like to have the bellow result, based on the latest DATE/TIME

thanks in advance for your help

[Zubair_Muhammad]

@rostromarre

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

[rostromarre]

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 · Accepted solution]

@rostromarre

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

[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?

[Anonymous]

@Zubair_Muhammad 

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. 

 

[lemontree]

@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?