Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

View solution in original post

10 REPLIES 10

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.

 

Anonymous
Not applicable

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

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

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

 

 

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.