Forum Discussion
Excel and Power Query: Merge Using Most Recent Date
Riny_van_Eekelen Hi, Riny! Thank you for answering my question. It was good to know I was on the right track and I was able to take action that appears to be working the way I need it to (I've spent much time examining the results).
I had done something similar to your method, but I had to make some adjustments that I am not sure why:
- Why is ChangeDate sorted in descending order?
- When removing duplicates, why choose to remove rows based on Date, Amt, and OwnerType instead of the entire row? How is that able to remove John from the 11/27/2020 transaction?
Thank you,
Cindy
Cindysc1218 My understanding was that you can only have 1 same type of owner for each account 1 Primary, 1 Secondary1, 1 Secondary2 etc. But never 2 Primaries or 2 Secondary1's etc,.
The sample data contained instances where there were 2 Primary owners of which you want to keep the one that changed most recently. That's why I sorted by ChangeDate in descending order. That becomes important in the next step of removing duplicates.
Now, why use those three columns to remove the duplicates? I figured that the combination of these three were the key to identifying same transactions by owner type. The "remove duplicates" step "joins" all of these three columns and keeps the first one (i.e. the one with then most recent change date) when it finds duplicates. So if you have it like below (key fields, change date and name), sorted by Change Date in descending order, John will be removed.
Nov-27, 1000, Jul-8 2020, P, James
Nov-27, 1000, Sep-1 2015, P, John <<<<< duplicate that will be removed
Nov-27, 1000, Sep-1 2015, S1, Mary