Custom column power query: Find most recent date per item, based on a column from another table

Hii Danny2020 

Instead of a complex merge, you can use a single Custom Column formula. This approach is efficient for finding the "nearest" record in a separate table.

​The Step-by-Step Fix:

1. ​Open Table 1 in the Power Query Editor.
2. ​Go to the Add Column tab and select Custom Column.
3. ​Paste the following M-code formula:

​<!-- end list -->

let
    currentDate = [Date],
    currentItem = [ITEM],
    // Filter Table 2 for the same item and earlier dates
    FilteredTable = Table.SelectRows(#"Table 2", each ([ITEM] = currentItem and [Date] < currentDate)),
    // Extract the list of dates and find the maximum (most recent)
    PreviousDate = List.Max(FilteredTable[Date])
in
    PreviousDate

[Note: Ensure #"Table 2" matches the actual name of your second table in Power Query].

​Why this works:

• ​Contextual Filtering: For every row in Table 1, the formula creates a temporary "virtual" version of Table 2 containing only the dates that qualify (same item, earlier date).
• ​List.Max: This function retrieves only the highest (most recent) date from that filtered list.
• ​Null Handling: If no earlier date exists in Table 2 for that item, the formula naturally returns null, which matches your sample data requirements.

​Performance Tip

​If your tables have thousands of rows, this calculation may slow down. To speed it up, ensure you have sorted Table 2 by Date (Descending) before running this custom column.

​Summary for the Community

​Using Table.SelectRows combined with List.Max is the standard way to perform "As-of Date" lookups in Power Query without creating massive merged tables.

​If this solution helps you retrieve the correct Previous Dates, please mark this as the "Accepted Solution" to help others!

1) Power Query steps (M)

let
    T1 = Table.TransformColumnTypes(Table1, {{"ITEM", type text}, {"Date", type date}}),
    T2 = Table.TransformColumnTypes(Table2, {{"ITEM", type text}, {"Date", type date}}),

    DatesByItem =
        Table.Group(
            T2,
            {"ITEM"},
            {{"Dates", each List.Sort([Date]), type list}}
        ),

    Joined =
        Table.NestedJoin(T1, {"ITEM"}, DatesByItem, {"ITEM"}, "Lkp", JoinKind.LeftOuter),

    Expanded =
        Table.ExpandTableColumn(Joined, "Lkp", {"Dates"}, {"Dates"}),

    AddPrev =
        Table.AddColumn(
            Expanded,
            "Previous Date",
            each
                let
                    d = [Date],
                    lst = [Dates],
                    prev = if lst = null then null else List.Max( List.Select(lst, (x) => x < d) )
                in
                    prev,
            type date
        ),

    Final = Table.RemoveColumns(AddPrev, {"Dates"})
in
    Final

2) Merge then filter (simpler but slower)

You can merge Table1 and Table2 on ITEM, then filter Table2 dates < Table1[Date] and take List.Max