Merge two tables with multiple join conditions in Power Query

[Anonymous]

Hi Team,

I have 2 tables as below (Table1 and Table2). I have to join these 2 tables with multiple joining criteria:

1. Join on Transaction_ID from both the tables and
2. Also, join on Table1.Transaction_Dt should be between Table2.Sale_From_Dt and Table2.Sale_To_Dt).

However, I'm able to do it in SQL (mentioned below). But then not able to do it using Power Query.

SELECT A.* FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.Transaction_ID = B.Transaction_ID
     AND A.Transaction_Dt>=B.Sale_From_Dt AND A.Transaction_Dt<=B.Sale_To_Dt

Table1

Transaction_ID	Item_Name	Transaction_Dt
1001	Pen	21-May-22
1002	Laptop	6-Aug-22
1003	Phone	1-Nov-22
1004	Case	25-Apr-22
1005	Wire	19-Aug-22
1006	Cake	15-Mar-22
1007	Icecream	16-Mar-22
1008	Book	5-Nov-22

Table2

Transaction_ID	Amount	Sale_From_Dt	Sale_To_Dt
1005	500	1-Aug-22	30-Aug-22
1008	120	1-Oct-22	30-Oct-22
1007	345	1-Mar-22	30-Mar-22
1009	643	1-Feb-22	28-Feb-22
1002	900	1-Oct-22	30-Oct-22
1004	3498	1-Apr-22	30-Apr-22
1001	34	1-Mar-22	30-Mar-22
1003	456	1-Nov-22	30-Nov-22
1005	345	1-Jul-22	30-Jul-22
1008	345	1-Nov-22	30-Nov-22
1007	908	1-May-22	30-May-22
1009	125	1-Jan-22	30-Jan-22
1002	3787	1-Aug-22	30-Aug-22
1004	987	1-Dec-22	30-Dec-22
1001	7634	1-May-22	30-May-22
1003	760	1-Nov-22	30-Nov-22

 Can someone please help me with the same?

@amitchandak - Any help here is much appriciated.

Thanks!!

[v-chenwuz-msft · Accepted solution]

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.

[v-chenwuz-msft · Accepted solution]

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.

[mb769]

I have been implementing a very similar problem, and your solution helps enormously, but it is unfortunately not entirely correct.

I use a LeftOuterJoin condition, which means I want to keep all records from Table 1.

The problem arises in this situation:

- there is a (at least 1) related record in Table 2

- all related records fail on the second condition ( Transaction_Dt between Sale_From_Dt and Sale_To_Dt)

In this situation we lose the original record from Table1, though I want to keep it with null values in the Table2 columns.

Any solution for this?

[Anonymous · Accepted solution]

Thanks @v-chenwuz-msft - That helps!