Merge records based on dates that are N years apart

[CharC]

Hi, I have a problem where I need to merge records based on dates but the dates are not exact match. 

Below are the two tables where I've identified the records to be merged (same colours).

I want to find, in table 2, the records from at least one year later. Note for ID=125 or 126, the records I want to merge are 2 years after the ref period in table 1. And in my real data, sometimes I have to merge the records from 3 years or 4 years later because those are the only records after the ref period in table 1. 

[ronrsnfld]

Here's one method that seems pretty quick:

let 
    Table1 = Table.FromColumns(
        {List.Transform({"Mar-19", "Jun-19", "Mar-20","Mar-21"}, each Date.FromText(_,[Format="MMM-yy"])) }
         & {{123,126,124,125}}, type table[Ref period=date, ID=Int64.Type]),
    Table2 = Table.FromColumns(
        {List.Transform(
            {"Mar-19","Mar-20","Mar-21", "Mar-19", "Mar-20", "Mar-21", "Mar-19","Mar-20","Mar-23","Mar-19","Jun-19","Mar-20","Jun-21"},
                each Date.FromText(_,[Format="MMM-yy"]))} &
        {{123,123,123,124,124,124,125,125,125,126,126,126,126}},type table[Ref period=date, ID=Int64.Type]),
    
    Joined = Table.NestedJoin(Table1,"ID", Table2,"ID", "Joined", JoinKind.LeftOuter),
    
    #"Added Custom" = Table.AddColumn(Joined, "Next Date", 
        (r)=> List.Min(List.Select(r[Joined][Ref period], each _ >=Date.AddYears(r[Ref period],1))), type date),
    
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Joined"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"ID", Order.Ascending}})
in
    #"Sorted Rows"

Results from your data:

[MFelix]

Hi @CharC ,

To what I can see your rule is to be latest record table 2 is that correct?

You can simple use this simple option in the merge based on ID and then on expand select the maximum date:

However your image shows that 123 as a different period select is this an error on the image or is it exactly like this? If the image result is what you want then how do you define that is March 20 instead of March 2021 for ID 123?

[CharC]

---

@MFelix wrote:

Hi @CharC ,

To what I can see your rule is to be latest record table 2 is that correct?

You can simple use this simple option in the merge based on ID and then on expand select the maximum date:

However your image shows that 123 as a different period select is this an error on the image or is it exactly like this? If the image result is what you want then how do you define that is March 20 instead of March 2021 for ID 123?

---

Hi @MFelix, thank you for your repky. It is not always the latest record.

For 123, Mar 20 is selected because it is the immediate 'next record' so to speak. I don't want the record from the same year (mar19) and don't want mar21 (the latest record in table 2) because it is not the immediate next record.  

124: Mar 21 is selected as it is immediate next (one year after), but also happens to be the latest record.

125: the 'immediate next' record is mar 25 (there is no mar23 record, otherwise it would have been mar23 to be merged), also happens to be the latest record.

• 126: Happens to be the lastest record, and two years after ref period in table 1. I dont't want the mar20 record because it is less than 1 year after the ref period in table 2 (jun19). Has to be at least 1 year

[MFelix · Accepted solution]

Hi @CharC,

Thank you for the explanation what you can do is the following:

• Do the merge of the two table based on ID
• Add a new custom column with the following code:

let 
currentFilter = Date.AddYears( [Ref Period],1) 
in 
Table.FirstN(
    Table.SelectRows([Table2], each [Ref Period] >= currentFilter),
     1)

• Expand the new column
• Delete the merged column

This code adds a custom filter that will get the date from the table 1 gets the date after 1 year and then filters the merge table based on all the values greater or equal to the date and then get the 1st row:

Please see the full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNzIwtFTSUTI0MlaK1YGKGBmARUyQRAzBIqYQETOELjOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ref Period" = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref Period", type date}, {"ID", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, Table2, {"ID"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each let 
currentFilter = Date.AddYears( [Ref Period],1) 
in 
Table.FirstN(
    Table.SelectRows([Table2], each [Ref Period] >= currentFilter),
     1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ref Period"}, {"Period Table 2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Table2"})
in
    #"Removed Columns"

If you need to change the way the filter works you need to change the currentdate part of the code