Merge two tables with multiple join conditions in Power Query

Hi Anonymous ,

You can join these tables firstly by merge queris.

And join kind with Left outer join.

expand all the columns expcet id column in the table2.

Add a custom column to identify if the date between table2's date range.

At last, filter all the row with TRUE and null. It means remove the rows which do not match conditions.

The M code:

let
    Source = Table.NestedJoin(Table1, {"Transaction_ID"}, Table2, {"Transaction_ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Amount", "Sale_From_Dt", "Sale_To_Dt"}, {"Table2.Amount", "Table2.Sale_From_Dt", "Table2.Sale_To_Dt"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "if between", each [Transaction_Dt]>=[Table2.Sale_From_Dt] and
[Transaction_Dt]<=[Table2.Sale_To_Dt]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([if between] <> false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"if between"})
in
    #"Removed Columns"

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks v-chenwuz-msft - That helps!