CODEWITHSUNDEEP
sqljoinmergepowerbipowerquery
Akhil
Akhil

Reputation: 41

Merge two tables with multiple join conditions in Power Query

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

  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-2022
1002 Laptop 6-Aug-2022
1003 Phone 1-Nov-2022
1004 Case 25-Apr-2022
1005 Wire 19-Aug-2022
1006 Cake 15-Mar-2022
1007 Ice cream 16-Mar-2022
1008 Book 5-Nov-2022

Table2

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

Can someone please help me with the same?

Thanks!!

Upvotes: 0

Views: 2514

Answers (1)

horseyride
horseyride

Reputation: 21298

If you want to do pull the matching rows Table2:

Add column, custom column with formula

(i)=>(Table.SelectRows(Table2, each [Transaction_ID]=i[Transaction_ID] and i[Transaction_Dt]>=[Sale_From_Dt] and i[Transaction_Dt]<=[Sale_To_Dt]))

If you want to get at the Amount field for the match, you can use this instead:

(i)=>(Table.SelectRows(Table2, each [Transaction_ID]=i[Transaction_ID] and i[Transaction_Dt]>=[Sale_From_Dt] and i[Transaction_Dt]<=[Sale_To_Dt] ) [Amount]{0})

I'd suggest you buffer Table2 first, if the data is large, as in sample code below:

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction_ID", Int64.Type}, {"Item_Name", type text}, {"Transaction_Dt", type date}}),
Table2Buffer=Table.Buffer(Table2),
#"Added Custom" =  Table.AddColumn(#"Changed Type", "Custom",  (i)=>(Table.SelectRows(Table2Buffer, each [Transaction_ID]=i[Transaction_ID] and i[Transaction_Dt]>=[Sale_From_Dt] and i[Transaction_Dt]<=[Sale_To_Dt] ) [Amount]{0}),type number)
in  #"Added Custom"

Upvotes: 1

Related Questions