I am looking for a way to keep the latest time value
Quite simply: I have a multiple time records per unit, everyday but only want the last time reported for each unit, everyday.
I am trying to make the changes in Power Query, using M (or whatever method would be best).
Thank you very much, in advance, for your help with this!
Solved! Go to Solution.
Update to this post: The only solution I found was to write a SQL script, into the advanced options window (get data window), which selects the last date of the week and the highest value of the day.
Thanks for your reply! However there are more than one serial numbers (unit), with multiple data/time values. In fact, the time has 40+million rows.
So, I'm trying to reduce the number of rows by only keeping one data/time value per day per serial number (unit).
|1/1/2018 10:00:00 AM||abcd1234||delete|
|1/1/2018 11:00:00 AM||abcd1234||delete|
|1/1/2018 12:00:00 PM||abcd1234||delete|
|1/1/2018 1:00:00 PM||abcd1234||delete|
|1/1/2018 2:00:00 PM||abcd1234||KEEP|
|1/1/2018 3:00:00 PM||efgh5678||delete|
|1/1/2018 4:00:00 PM||efgh5678||delete|
|1/1/2018 5:00:00 PM||efgh5678||delete|
|1/1/2018 6:00:00 PM||efgh5678||delete|
|1/1/2018 7:00:00 PM||efgh5678||KEEP|
|1/1/2018 9:00:00 AM||ijkl9012||delete|
|1/1/2018 10:00:00 AM||ijkl9012||delete|
|1/1/2018 11:00:00 AM||ijkl9012||delete|
|1/1/2018 12:00:00 PM||ijkl9012||delete|
|1/1/2018 1:00:00 PM||ijkl9012||KEEP|
|1/1/2018 7:00:00 AM||mnop3456||delete|
|1/1/2018 9:00:00 AM||mnop3456||delete|
|1/1/2018 11:00:00 AM||mnop3456||delete|
|1/1/2018 1:00:00 PM||mnop3456||delete|
|1/1/2018 3:00:00 PM||mnop3456||KEEP|
|… so on and so forth|
Thank you for your sugguestions ... they worked as you said, but I want to keep the last received time value from each week for each unit (serialNumber); not just last (max) time for the entire year.
So, if a unit has 70 records/ week (10 time records/ day * 7 days), I want to keep the last time recorded in the week.
I hope this makes sense, and thanks again for your suggestions!
It'd be a similar principle, the easiest way I can think of doing this is to create a new column using whatever logic your week runs from and to to give each entry a number for which week it belongs to, then group by a combination of the serial number and the week - I've not really used grouping that much so I'm not sure if it's got the ability to group by two fields, but if not, making another column joining your serial number and the week number then grouping by that would have a similar result
The unfortunate thing about grouping is that it removes any other columns in that table. Really appreciate your post though; it helped me figure something else out!
You're right, I should have clarified this before ... that's my fault. Unfortunately, this still removes other columns in the table, which I still need.