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

[Danny2020]

Hello,

I have the following sample tables:

1:

ITEM	Date	Previous Date
AA	12/31/2025	12/29/2025
AA	12/27/2025	12/26/2025
AA	12/26/2025	12/25/2025
BB	10/23/2025	10/20/2025
BB	10/21/2024	null

2

ITEM	Date
AA	12/31/2025
AA	12/30/2025
AA	12/29/2025
AA	12/28/2025
AA	12/27/2025
AA	12/26/2025
AA	12/25/2025
BB	10/24/2025
BB	10/23/2025

The original tables contains hundreds of different items, and time range for almost 3 years.

How to calculate in power query the "Previous Date" column in table 1, so it returns the most recent date per item, based on table 2?

Example: for Item AA in 12/27/25, the previous date available based on table 2 is 12/26/2025. Can you please help?

[AshokKunwar · Accepted solution]

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!

[Danny2020]

Hi @AshokKunwar , thank you so much for the detailed steps.

I applied them sorting table 2 by descending date and it initially worked in a first run using dataflow.

Unfortunately in a second run the process keeps running after 10 hours. The result table would have more than 4 million rows.

Is there a way to increase the performance by adding an indexing column to the code, or applying other method?

Thank you.

[AshokKunwar]

@Danny2020 

Your welcome 🤗, 

If you have any other question free to ask me.

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

[cengizhanarslan · Accepted solution]

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

[Danny2020]

Thank you @cengizhanarslan! , this method worked as the fastest solution for millions of rows

[Kedar_Pande]

@Danny2020 

Power Query M code for Table1 (Add Column → Custom Column):

let
Table2Dates = Table2[Date],
CurrentItem = [ITEM],
CurrentDate = [Date],
FilteredDates = List.Select(Table2Dates, each
[ITEM] = CurrentItem and _ < CurrentDate
),
PreviousDate = if List.IsEmpty(FilteredDates)
then null
else List.Max(FilteredDates)
in
PreviousDate

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Ashish_Mathur]

Hi,

I think your calculations would be faster if you create a calculated column in the Data model view

=CALCULATE(MAX(Table2[Date]),FILTER(Table2,Table2[ITEM]=EARLIER(Table1[ITEM])&&Table2[Date]<EARLIER(Table1[Date])))

Hope this helps.

[AshokKunwar · Accepted solution]

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!