Retaining the latest values according to Timestamp

Hope all fellows will be fine.

In the attached file, I have the student group attendance record which they submit at the online Attendance form themselves.

Many-times they submit multiple attendance each day. I want to retain the latest record submitted for that day. It will be also helpful if I could able to find differences before removal of the records.
Group Attendance Sheet (Before Mid).xlsx (15.0 KB)

@Tanzeel,

Always good to hear from you – hope you’re doing well.

I’ve taken a look at your dataset, but if you could please provide a mockup of the output you’re looking for, that would be extremely helpful in developing a solution.

Thanks.

  • Brian

Thanks @BrianJ.
Attached is the Excel File with highlighted rows for sample.
Group Attendance Sheet (Before Mid).xlsx (19.6 KB)
1- In ‘Red Color’, There is no change in the data record but only repetition. but I want to retain the row with most latest Timestamp.Attendance-1

2- In 'Yellow Color, I want also retain the latest record (row 28) but, before removal of duplicates, want to store the change in physical record.
Attendance-2

For instance, at 28 row, there is change of status of second group member for the same day. At row 26, when the form submitted, the second group member was absent but later on at row 28, his status changes.

Hi @Tanzeel,

I’m sure @BrianJ will offer you an amazing DAX solution but to me this sound like a job for Power Query. So I Grouped the rows in the table and Sorted them Descending, added an Index column and Expanded all rows again. So you now have all records you started with but can Identify all the “Latest records” by the Index number 1.

I hope this is helpful. Here is the PBIX, if you update the FileLocation parameter the query will be restored. eDNA - Retaining the latest values according to Timestamp.pbix (68.8 KB)

1 Like

Nicely done, @Melissa!

F8A5C601-17EB-4231-9654-3EAD4C378763

  • Brian

Hi @Tanzeel, we’ve noticed that no response has been received from you since the 20th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

@Melissa and @BrianJ

Thank you one again.