Excel and Power Query: Merge Using Most Recent Date

Question

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:

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:

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?

Answer 1

Assuming you start with 2 tables, which look something like this:

Owners:

Transactions:

You can use a function to filter owners based on transaction date, apply a partiton to owner type, and return the latest owner, for each transaction row:

let
    fnLatestOwners = (MyTable as table, MyDate as date) =>
    let
        #"Filtered Date" = Table.SelectRows(MyTable, each [Owner Change Date] <= MyDate),
        #"Partitioned Owners" = Table.Group(#"Filtered Date", {"Primary / Secondary Owner"}, {{"Partition", each Table.FirstN(Table.Sort(_,{{"Owner Change Date", Order.Descending}}),1), type table}}),
        #"Combined Partitions" = Table.Combine(#"Partitioned Owners"[Partition]),
        #"Removed Columns" = Table.RemoveColumns(#"Combined Partitions",{"Owner Change Date"})
    in
        #"Removed Columns",

    Source = Transactions,
    #"Merged Queries" = Table.NestedJoin(Source,{"Account"},Owners,{"Account"},"Owners",JoinKind.LeftOuter),
    #"Added Latest Owners" = Table.AddColumn(#"Merged Queries", "Latest Owners", each fnLatestOwners([Owners],[Trans Date]), type table),
    #"Expanded Latest Owners" = Table.ExpandTableColumn(#"Added Latest Owners", "Latest Owners", {"Primary / Secondary Owner", "Owner Name"}, {"Primary / Secondary Owner", "Owner Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Latest Owners",{"Owners"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Primary / Secondary Owner", type text}, {"Owner Name", type text}})
in
    #"Changed Type"

This returns: