Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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.  ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

rafaelmpsantos's avatar
rafaelmpsantos
Icon for Responsive Resident rankResponsive Resident
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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 
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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!

      • jthomson's avatar
        jthomson
        Icon for Solution Sage rankSolution Sage
        8 years ago

        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's avatar
    Anonymous
    Not applicable
    8 years ago

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