Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 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.

 

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!

 

 

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.

10 Replies

  • rafaelmpsantos's avatar
    rafaelmpsantos
    Responsive Resident
    7 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
      7 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
        Super User
        7 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.