cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rostromarre
Frequent Visitor

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:

 

1.PNGa

 

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

 

2.PNG

 

thanks in advance for your help

1 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

datime.png

Step # 3: Now we use Table.Max function to get the other rows
 Then expand table and remove unnecessary columns


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

@rostromarre

 

Please see attached file

 

Here are the steps

First Group the Columns like this from the Query Editor

 

grooping.png

 

Then you can add a custom column to extract the single row from these tables

 

custom.png


Regards
Zubair

Please try my custom visuals

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 : 3.PNG

 

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)

 

4.PNG

@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

datime.png

Step # 3: Now we use Table.Max function to get the other rows
 Then expand table and remove unnecessary columns


Regards
Zubair

Please try my custom visuals

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
Not applicable

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

 

 Sample.PNG

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors