Excel and Power Query: Merge Using Most Recent Date

%3CLINGO-SUB%20id%3D%22lingo-sub-3376090%22%20slang%3D%22en-US%22%3EExcel%20and%20Power%20Query%3A%20Merge%20Using%20Most%20Recent%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376090%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20everyone%3A%3C%2FP%3E%3CP%3EI%20was%20able%20to%20merge(inner%20join)%20a%20transaction%20table%20with%20an%20owner%20info%20table%20using%20account%20number%20as%20my%20key%20to%20get%20the%20following%20results%3A%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22xJYzz.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371707iB9CB5588D0617E2F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22xJYzz.png%22%20alt%3D%22xJYzz.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20the%20transactions%20to%20be%20linked%20to%20the%20current%20owner(s)%20of%20the%20account.%20But%20as%20you%20can%20see%2C%20for%20the%209%2F16%2F2016%20transaction%2C%20it%20is%20also%20linked%20to%20an%20owner%20who%20did%20not%20own%20the%20account%20until%20much%20later.%20Similarly%2C%20the%2011%2F27%2F2020%20transaction%20needs%20to%20be%20linked%20to%20the%20newer%20owner%2C%20so%20I%20am%20looking%20for%20something%20like%20this%3A%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%228VXRU.png%22%20style%3D%22width%3A%20812px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371708i273DDB4E9648F916%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%228VXRU.png%22%20alt%3D%228VXRU.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20the%20secondary%20owner%20does%20not%20change%2C%20Mary%20applies%20to%20both%20transactions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20other%20accounts%2C%20it%20is%20also%20possible%20for%20the%20primary%20owner%20to%20remain%20the%20same%20while%20the%20secondary%20owner%20has%20changed.%20There%20are%20also%20accounts%20in%20which%20there%20are%20up%20to%204%20secondary%20owners.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20in%20short%2C%20I%20need%20the%20transaction%20dates%20to%20match%20up%20with%20the%20previous%2C%20most%20recent%20Owner%20Change%20Date%20for%20both%20primary%20and%20secondary%20owner(s).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20Power%20Query%2C%20so%20I%20do%20not%20know%20whether%20this%20is%20better%20done%20using%20PQ%20or%20simply%20Excel%20functions%2Fformulas.%20Or%20maybe%20there%20are%20additional%20data%20manipulation%2Ftransformation%20steps%20I%20need%20to%20take%20before%20this...%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20input%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3376090%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376339%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20and%20Power%20Query%3A%20Merge%20Using%20Most%20Recent%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1138699%22%20target%3D%22_blank%22%3E%40Cindysc1218%3C%2FA%3E%26nbsp%3BDifficult%20to%20be%20thoroughly%20test%20on%20such%20a%20small%20data%20set%2C%20but%20the%20attached%20file%20seems%20to%20achieve%20what%20you%20describe.%20Please%20see%20if%20this%20would%20work%20on%20your%20real%20data%20as%20well.%20If%20not%2C%20come%20back%20with%20a%20more%20realistic%20example%20of%20your%20data.%20Perhaps%20you%20can%20share%20a%20link%20to%20your%20workbook%20(Onedrive%2C%20Dropbox%20or%20similar).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3377508%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20and%20Power%20Query%3A%20Merge%20Using%20Most%20Recent%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3377508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BHi%2C%20Riny!%26nbsp%3B%20Thank%20you%20for%20answering%20my%20question.%26nbsp%3B%20It%20was%20good%20to%20know%20I%20was%20on%20the%20right%20track%20and%20I%20was%20able%20to%20take%20action%20that%20appears%20to%20be%20working%20the%20way%20I%20need%20it%20to%20(I've%20spent%20much%20time%20examining%20the%20results).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20done%20something%20similar%20to%20your%20method%2C%20but%20I%20had%20to%20make%20some%20adjustments%20that%20I%20am%20not%20sure%20why%3A%3C%2FP%3E%3COL%3E%3CLI%3EWhy%20is%20ChangeDate%20sorted%20in%20descending%20order%3F%3C%2FLI%3E%3CLI%3EWhen%20removing%20duplicates%2C%20why%20choose%20to%20remove%20rows%20based%20on%20Date%2C%20Amt%2C%20and%20OwnerType%20instead%20of%20the%20entire%20row%3F%26nbsp%3B%20How%20is%20that%20able%20to%20remove%20John%20from%20the%2011%2F27%2F2020%20transaction%3F%3C%2FLI%3E%3C%2FOL%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3ECindy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3378048%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20and%20Power%20Query%3A%20Merge%20Using%20Most%20Recent%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3378048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1138699%22%20target%3D%22_blank%22%3E%40Cindysc1218%3C%2FA%3E%26nbsp%3BMy%20understanding%20was%20that%20you%20can%20only%20have%201%20same%20type%20of%20owner%20for%20each%20account%201%20Primary%2C%201%20Secondary1%2C%201%20Secondary2%20etc.%20But%20never%202%20Primaries%20or%202%20Secondary1's%20etc%2C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sample%20data%20contained%20instances%20where%20there%20were%202%20%26nbsp%3BPrimary%20owners%20of%20which%20you%20want%20to%20keep%20the%20one%20that%20changed%20most%20recently.%20That's%20why%20I%20sorted%20by%20ChangeDate%20in%20descending%20order.%20That%20becomes%20important%20in%20the%20next%20step%20of%20removing%20duplicates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20why%20use%20those%20three%20columns%20to%20remove%20the%20duplicates%3F%20I%20figured%20that%20the%20combination%20of%20these%20three%20were%20the%20key%20to%20identifying%20same%20transactions%3CU%3E%20by%20owner%20type%3C%2FU%3E.%20The%20%22remove%20duplicates%22%20step%20%22joins%22%20all%20of%20these%20three%20columns%20and%20keeps%20the%20first%20one%20(i.e.%20the%20one%20with%20then%20most%20recent%20change%20date)%20when%20it%20finds%20duplicates.%20So%20if%20you%20have%20it%20like%20below%20(%3CSTRONG%3Ekey%20fields%2C%3C%2FSTRONG%3E%26nbsp%3B%3CFONT%20color%3D%22%23DF0000%22%3Echange%20date%3CFONT%20color%3D%22%23000000%22%3E%20and%20name%3C%2FFONT%3E%3C%2FFONT%3E)%2C%20sorted%20by%20Change%20Date%20in%20descending%20order%2C%20John%20will%20be%20removed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENov-27%2C%201000%2C%3C%2FSTRONG%3E%20%3CFONT%20color%3D%22%23DF0000%22%3EJul-8%202020%3C%2FFONT%3E%2C%20%3CSTRONG%3EP%2C%3C%2FSTRONG%3E%20James%3C%2FP%3E%3CP%3E%3CSTRONG%3ENov-27%2C%201000%2C%3C%2FSTRONG%3E%26nbsp%3B%3CFONT%20color%3D%22%23DF0000%22%3ESep-1%202015%3C%2FFONT%3E%2C%20%3CSTRONG%3EP%3C%2FSTRONG%3E%2C%20John%20%26lt%3B%26lt%3B%26lt%3B%26lt%3B%26lt%3B%20duplicate%20that%20will%20be%20removed%3C%2FP%3E%3CP%3E%3CSTRONG%3ENov-27%2C%201000%2C%3C%2FSTRONG%3E%26nbsp%3B%3CFONT%20color%3D%22%23DF0000%22%3ESep-1%202015%3C%2FFONT%3E%2C%20%3CSTRONG%3ES1%3C%2FSTRONG%3E%2C%20Mary%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, everyone:

I was able to merge(inner join) a transaction table with an owner info table using account number as my key to get the following results:

 
 

xJYzz.png

 

I need the transactions to be linked to the current owner(s) of the account. But as you can see, for the 9/16/2016 transaction, it is also linked to an owner who did not own the account until much later. Similarly, the 11/27/2020 transaction needs to be linked to the newer owner, so I am looking for something like this:

 

8VXRU.png

 

Since the secondary owner does not change, Mary applies to both transactions.

 

For other accounts, it is also possible for the primary owner to remain the same while the secondary owner has changed. There are also accounts in which there are up to 4 secondary owners.

 

So, in short, I need the transaction dates to match up with the previous, most recent Owner Change Date for both primary and secondary owner(s).

 

I am new to Power Query, so I do not know whether this is better done using PQ or simply Excel functions/formulas. Or maybe there are additional data manipulation/transformation steps I need to take before this...?

 

Any input would be greatly appreciated.

Thank you.

3 Replies

@Cindysc1218 Difficult to be thoroughly test on such a small data set, but the attached file seems to achieve what you describe. Please see if this would work on your real data as well. If not, come back with a more realistic example of your data. Perhaps you can share a link to your workbook (Onedrive, Dropbox or similar).

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

  1. Why is ChangeDate sorted in descending order?
  2. 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