Join Tables by Key and Date Comparison

Here is one way you can do this.
Adjust your td_Milestone query code to this...

let
    Quelle = 
    Excel.CurrentWorkbook(){[Name="td_Milestone"]}[Content],
    Add_Date = 
    Table.AddColumn(
        Quelle, 
        "Date", 
        each 
            if [Mon] = 2 
                then #date([Yea], [Mon], 28) 
                else 
            if List.Contains({4, 6, 9, 11}, [Mon]) 
                then #date([Yea], [Mon], 30) 
                else #date([Yea], [Mon], 31)
    ),
    DataType = 
    Table.TransformColumnTypes(
        Add_Date,
        {
            {"Project", Int64.Type}, {"Yea", Int64.Type}, {"Mon", Int64.Type}, {"Date", type date}
        }
    ),
    merge_md_by_project = 
    Table.NestedJoin(
        DataType, 
        {"Project"}, 
        md_Milestone, 
        {"Project"}, 
        "md_Milestone", 
        JoinKind.LeftOuter
    ),
    add_dx_column = 
    Table.AddColumn(
        merge_md_by_project, 
        "DX", 
        each 
            let 
                key = [Date], 
                dxList = 
                    List.Transform(
                        [md_Milestone][Dx Date], 
                        each Number.From(_ - key)
                    ), 
                pos = 
                    List.PositionOf(
                        dxList, 
                        List.Min(
                            List.Select(
                                dxList, 
                                each _ > 0
                            )
                        ), 
                        Occurrence.First
                    ) 
            in 
                try [md_Milestone][Dx]{pos} 
                otherwise null, 
        type text
    ),
    remove_md_table_column = 
    Table.RemoveColumns(
        add_dx_column,
        {"md_Milestone"}
    )
in
    remove_md_table_column

Hi Goodkat​,

What you are describing is essentially an as-of join.

The join condition is:

same Project ID

and milestone Date <= transaction Date

then, from those matching milestone rows, keep the latest milestone date.

Power Query's normal Merge operation is based on equality between selected key columns, so I would first merge on Project ID only. Microsoft's Merge queries guidance explains that the merge produces a nested table containing the matching rows from the second table.

Then, for each transaction row, filter that nested milestone table so that only milestone dates less than or equal to the transaction date remain, sort those rows descending by milestone date, and take the first row.

Conceptually:

Project 123, Date = D1 -> latest milestone <= D1 -> none

Project 123, Date = D2 -> latest milestone <= D2 -> D2

Project 123, Date = D4 -> latest milestone <= D4 -> D3

Microsoft's Table.SelectRows documentation supports the conditional filtering part of this pattern.

One thing I would be careful about is relying on a sort performed earlier in the query. Microsoft notes that sort order is not guaranteed to survive operations such as merges and grouping, so I would sort the nested milestone table immediately before taking the first matching row.

That also makes the logic much easier to verify.

So my approach would be:

• merge on Project ID
• filter matching milestones where MilestoneDate <= transaction Date
• sort MilestoneDate descending
• take the first row
• expand the selected milestone fields

For larger datasets, I would also watch performance because filtering a nested table row by row can become expensive. If the source supports doing the as-of logic in SQL before Power Query, that can scale better.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi Goodkat​,

Echoing jgeddes​: the key point is that this is not an equality join, so a normal Merge will not do it. You need, for each row, the earliest milestone for the same project whose date is on or after that row's month end. Their code builds the month-end date and then looks that milestone up. I would add two refinements: let Power Query calculate the month end for you, and keep the lookup readable as a small per-row filter.

Month end without manual day logic

The manual rule (28 for February, 30 for some months, 31 for the rest) breaks on leap years. Date.EndOfMonth handles it for every month.

Pattern: earliest milestone on or after the month end

let
    Transactions = td_Milestone,   // columns: ID, Year, Month
    Milestones   = Table.Buffer(md_Milestone),   // columns: ID, Milestone, DxDate

    AddMonthEnd = Table.AddColumn(
        Transactions, "MonthEnd",
        each Date.EndOfMonth(#date([Year], [Month], 1)), type date),

    AddMilestone = Table.AddColumn(
        AddMonthEnd, "Milestone",
        (r) =>
            let
                Candidates = Table.SelectRows(
                    Milestones,
                    each [ID] = r[ID] and [DxDate] >= r[MonthEnd]),
                Sorted = Table.Sort(Candidates, {{"DxDate", Order.Ascending}})
            in
                if Table.IsEmpty(Sorted) then null else Sorted{0}[Milestone],
        type text)
in
    AddMilestone

This assumes columns named ID, Year, Month, Milestone and DxDate. Adjust the names and the comparison (greater than or equal versus greater than) to match your rule for rows that fall exactly on a milestone date. Rows later than the last milestone return null, which is also how you can spot data that needs a final milestone.

Choosing an approach

For two project IDs by month the per-row version is more than fast enough. If the data grows, switch to the merge-and-group pattern.

So my overall approach would be:

1. Build MonthEnd with Date.EndOfMonth
2. For each row, take the earliest milestone for the same ID with DxDate >= MonthEnd
3. Buffer the milestone table
4. Expect null after the last milestone and treat it as a data check

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.