Forum Discussion

gigotomo's avatar
gigotomo
Frequent Visitor
5 years ago
Solved

How to remove duplicates based on latest dates

I am trying to remove the duplicate "INC_NUM" and only the latest date (Submit) should remain alongwith its summary.   How do i do it.  
Need Help
  • thedatahiker's avatar
    thedatahiker
    5 years ago

    gigotomo 
    Here is one way to do this

    1. Open Power Query Editor
    2. Duplicate this table in PowerQuery
    3. delete summary column from new table
    4. Right click on INC_Num and select Group by
    5. In Group By pop up box give New Column Name the value "Submit", set operation to "MAX", and select Submit under Column. Click OK
    6. Duplicate INC_Num & Submit columns
    7. Duplicate INC_num column & Submit column in current Table
    8. select both "Inc_num - Copy" & "Submit - Copy", right click, and select Merge Columns. 
    9. Click OK in Merge Column popup box
    10. Return to the original table and repeat steps 6 - 8
    11. Now you have a shared Key you can Merge Queries together. From the Original Table click on "Merge Queries" in the top Home Ribbon
    12. The top query will be your Original Table and you need to select the new aggregated table as the second table. Then click on the merged column in each table as your join column. Under Join Kind select "Inner (Only matching rows)
    13. That's it. This will have only grabbed the INC_Num with the last date.  
thedatahiker's avatar
thedatahiker
Microsoft Employee
5 years ago

gigotomo 
Here is one way to do this

  1. Open Power Query Editor
  2. Duplicate this table in PowerQuery
  3. delete summary column from new table
  4. Right click on INC_Num and select Group by
  5. In Group By pop up box give New Column Name the value "Submit", set operation to "MAX", and select Submit under Column. Click OK
  6. Duplicate INC_Num & Submit columns
  7. Duplicate INC_num column & Submit column in current Table
  8. select both "Inc_num - Copy" & "Submit - Copy", right click, and select Merge Columns. 
  9. Click OK in Merge Column popup box
  10. Return to the original table and repeat steps 6 - 8
  11. Now you have a shared Key you can Merge Queries together. From the Original Table click on "Merge Queries" in the top Home Ribbon
  12. The top query will be your Original Table and you need to select the new aggregated table as the second table. Then click on the merged column in each table as your join column. Under Join Kind select "Inner (Only matching rows)
  13. That's it. This will have only grabbed the INC_Num with the last date.  
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks for the post

     

    Took me a while to get my head around it, but it once I got it, it is actually real simple and effective.

     

    I found it helpful to convert the dates to numbers.  My other challenge was that I had two date columns.  A start date but a termination date that was blank so I had to create a new custom column and give the blank date todays date in order to merge the start and termination date to get the biggest value to group by.

     

    Cheers