Excel or Power Query: Merge/Lookup Closest Previous Date-CodePudding

everyone:

I have a table with these dates:

I have another table with a list of dates and owners:

I need to merge/join these two tables so that the transaction dates are matched up with the closest previous start date so that it looks like this:

I tried using XLOOKUP with "-1" but that was a problem because XLOOKUP retrieved the first record for transaction date 4/16/2017 (which has 2 matches) and I couldn't merge on Power Query since there are no exact date matches. Is there another way?

Thank you.

CodePudding user response：

Assuming this table is named Table4, you first need to group because of your duplicate dates for e and f.

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type text}, {"Owner", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Start Date", type date}}, "en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Start Date"}, {{"All", each _, type table [Start Date=nullable date, Owner=nullable text]}})
in
    #"Grouped Rows"

Assuming this table is named Table3, you can then add a custom column as described

The code for this table is

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Dates", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Transaction Dates", type date}}, "en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", (i) => Table.Last( Table.Sort( Table.SelectRows(Table4,each [Start Date] <= i[Transaction Dates]),{"Start Date", Order.Ascending}))),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Start Date", "All"}, {"Custom.Start Date", "Custom.All"}),
    #"Expanded Custom.All" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.All", {"Owner"}, {"Owner"})
in
    #"Expanded Custom.All"