Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey guys,
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.
Hi...
In power query Sort yout Date Column Descending then in Home menu click on "Queep Rows">"Keep Top Rows" will open a dialogue box in numbers of rows type 1 then OK.
Done.
Hi @rafaelmpsantos,
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).
receivedTime | serialNumber | |
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 |
You can try grouby feature and see if it could help.
Hi,
In the Query Editor, right click on the serialNumber column and see the images below. CLick on OK to get the result (see second image below)
Hope this helps.
@Ashish_Mathur & @Anonymous,
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!
You obviously did not clarify this earlier. This is another condition that you have just put in. How does one define a week? Write the week numbers against each date.
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.
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.
Hi,
I can help you with resolving the problem. As asked in my previous message, show the week column in your dataset and also show the expected result there.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |