Forum Discussion
Finding the most recent date/time for each row
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
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
6 Replies
- Zubair_Muhammad
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
