Forum Discussion
Merge two tables with multiple join conditions in Power Query
Hi Team,
I have 2 tables as below (Table1 and Table2). I have to join these 2 tables with multiple joining criteria:
- Join on Transaction_ID from both the tables and
- 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!!
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!
3 Replies
- v-chenwuz-msft
Community Support
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!
mb769Advocate I
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?