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

Keep latest time value

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!

 

PowerBI help question.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
rkandathil Frequent Visitor
Frequent Visitor

Re: Keep latest time value

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.

10 REPLIES 10
rafaelmpsantos Established Member
Established Member

Re: Keep latest time value

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.

 

rkandathil Frequent Visitor
Frequent Visitor

Re: Keep latest time value

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

 

 

receivedTimeserialNumber 
1/1/2018 10:00:00 AMabcd1234 delete
1/1/2018 11:00:00 AMabcd1234 delete
1/1/2018 12:00:00 PMabcd1234delete 
1/1/2018 1:00:00 PMabcd1234delete 
1/1/2018 2:00:00 PMabcd1234KEEP
1/1/2018 3:00:00 PMefgh5678delete 
1/1/2018 4:00:00 PMefgh5678delete 
1/1/2018 5:00:00 PMefgh5678delete 
1/1/2018 6:00:00 PMefgh5678delete 
1/1/2018 7:00:00 PMefgh5678KEEP
1/1/2018 9:00:00 AMijkl9012delete 
1/1/2018 10:00:00 AMijkl9012delete 
1/1/2018 11:00:00 AMijkl9012delete 
1/1/2018 12:00:00 PMijkl9012delete 
1/1/2018 1:00:00 PMijkl9012KEEP
1/1/2018 7:00:00 AMmnop3456delete 
1/1/2018 9:00:00 AMmnop3456delete 
1/1/2018 11:00:00 AMmnop3456delete 
1/1/2018 1:00:00 PMmnop3456delete 
1/1/2018 3:00:00 PMmnop3456KEEP
… so on and so forth 
Super User
Super User

Re: Keep latest time value

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.Untitled.pngUntitled1.png

Jessica_Seiya Established Member
Established Member

Re: Keep latest time value

You can try grouby feature and see if it could help.

2018-10-19_11-03-50.png

 

 

 

Highlighted
rkandathil Frequent Visitor
Frequent Visitor

Re: Keep latest time value

@Ashish_Mathur & @Jessica_Seiya,

 

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!

Super User
Super User

Re: Keep latest time value

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

Super User
Super User

Re: Keep latest time value

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.

rkandathil Frequent Visitor
Frequent Visitor

Re: Keep latest time value

@jthomson,

 

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!

 

@Ashish_Mathur,

 

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.

Super User
Super User

Re: Keep latest time value

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.