How to merge tables based on largest date smaller than date

[th081]

Hi,

I have to tables one with a name, date and some other records. The second table has name, date and some other records, however in the second table there will be several records with the same name.

I want all the records in table one matching name and date in table 2, but the date in table 2 will not be a exact match. I want the date in table 2 to match to the largest date less than the date in table 1.

Table 1

James, 01/02/23, RecA, RecB, RecC

Table 2

James, 15/12/22, RecD, RecE
James, 12/01/23, RecF, Reg G
James, 29/01/23, RecH, RecI
James, 03/02/23, RecJ, RecK

The merge will bring back the third row in table 2 as that is the highest date less or same as the date in table 1

Can anyone help

 

[smozgur]

I would do something like the following.
1- Merge two tables on the Name field and get all matching Table2 rows
2- Select the rows less than or equal to the corresponding Table1 Date field value
3- Group on Date field and get the row with the max Date value
4- There are only the Name and Date fields left. Merge this table with the original Table2 on these two fields
5- Expand the missing columns from the nested table

Open the PQ editor, create a new blank query, click on Advanced Editor, copy and paste the following code, and click Done. Make sure to adapt the table names in the code below - [Name="Table1"] and [Name="Table2"] might need to be changed according to your table names.

let
    // Source for Table1
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // Source for Table2
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    // Merge two tables
    Merge = Table.NestedJoin(Table2, {"Name"}, Table1, {"Name"}, "Table1", JoinKind.LeftOuter),
    // Filter Table2 according to Date
    FilterRows = Table.SelectRows(Merge, each [Date] <= [Table1]{0}[Date]),
    // Find the earliest date rows by grouping the table
    Highest = Table.Group(FilterRows, {"Name"}, {{"Date", each List.Max([Date]), type datetime}}),
    // Merge on Table2 to get the missing columns after grouping 
    MergeAgain = Table.NestedJoin(Highest, {"Name", "Date"}, Table2, {"Name", "Date"}, "Table2"),
    // Expand the missing columns
    Result = Table.ExpandTableColumn(MergeAgain, "Table2", List.RemoveFirstN(Table.ColumnNames(Table2), 2) )
in
    Result

 

[th081]

Thank you smozgur,

your code seems to only to only bring back 1 record from table 2.

My Table 1 is as below:

Name	Date	Misc1	Misc2
James	01/02/2023​	1​	4​
Bond	02/02/2023​	2​	5​
James	03/02/2023​	3​	7​
James	04/02/2023​	4​	8​
James	04/10/2023​	4.1​	8.2​

Table 2 as below:

Name	Date	Misc3	Misc4
James	15/12/2022​	9​	13​
James	12/01/2023​	10​	14​
James	29/01/2023​	11​	15​
James	04/02/2023​	12​	16​

copying your code i was expecting the bwlow:

James	01/02/2023​	1​	4​	James	29/01/2023​	11​	15​		the record in table 2 dated 29/01/23 is the higest date less than or equal to 01/02/23 for James
Bond	02/02/2023​	2​	5​	null	null	null	null		no match
James	03/02/2023​	3​	7​	James	29/01/2023​	11​	15​		the record in table 2 dated 29/01/23 is the higest date less than or equal to 03/02/23 for James
James	04/02/2023​	4​	8​	James	04/02/2023​	12​	16​		04/02/23 is a exact match
James	04/10/2023​	4.1​	8.2​	James	04/02/2023​	12​	16​		the record in table 2 dated 04/02/23 is the higest date less than or equal to 04/10/23 for James

however the code only returns:

Name	Date	Misc3	Misc4
James	29/01/2023 00:00​	11​	15​

 

[smozgur]

From the first sample data, I thought Table1 to Table2 was a one-to-many relationship, but it was apparently many-to-many.

Here is the modified code for the new sample data and the expected result.
Note: Your initial description says "I want all the records in table one matching name and date in table 2", however, the desired result contains Bond while it is not matching the data in Table2. If the initial description is correct, then remove or [Date] is null from the code.

let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Merge = Table.NestedJoin(Table1, {"Name"}, Table2, {"Name"}, "Table2", JoinKind.FullOuter),

    Highest = Table.AddColumn(Merge, "Highest",
            (r) =>
                let
                    Result = Table.SelectRows(r[Table2], each [Date] <= r[Date] or [Date] is null)
                in
                    Table.First(Table.Sort(Result, {"Date", Order.Descending}))
            ),
    Expand = Table.ExpandRecordColumn(Highest, "Highest", {"Name", "Date", "Misc3", "Misc4"}, {"H.Name", "H.Date", "Misc3", "Misc4"}),
    Result = Table.RemoveColumns(Expand,{"Table2"})
in
    Result

Source data and result below (the data format is m/d/y in my data but it will work correctly with your d/m/y format in your computer)
Table1:

Name	Date	Misc1	Misc2
James	2/1/2023	1	4
Bond	2/2/2023	2	5
James	2/3/2023	3	7
James	2/4/2023	4	8
James	10/4/2023	4.1	8.2

Table2:

Name	Date	Misc3	Misc4
James	12/15/2022	9	13
James	1/12/2023	10	14
James	1/29/2023	11	15
James	2/4/2023	12	16

Result:

Name	Date	Misc1	Misc2	H.Name	H.Date	Misc3	Misc4
James	2/1/2023	1	4	James	1/29/2023	11	15
James	2/3/2023	3	7	James	1/29/2023	11	15
James	2/4/2023	4	8	James	2/4/2023	12	16
James	10/4/2023	4.1	8.2	James	2/4/2023	12	16
Bond	2/2/2023	2	5

 

[th081]

thank you this is perfect.

 

[smozgur]

thank you this is perfect.

You're welcome. Glad to hear it works.

 

[smozgur]

The following

Table.First(Table.Sort(Result, {"Date", Order.Descending}))

could be changed by using a single function - Table.Max() (and most proper one for the case actually, no need to sort and get the top row. Apparently, I didn't know that we have a Table.Max function. Good learning.)

Table.Max(Result, "Date")