Forum Discussion
Merge two tables: Right on Left and append all unique rows from Left
Hello,
I have two tables:
"Left" (Data from the previous week's report):
Column1Column2Column3Index
| apple | Adam | 12 | 3 |
| banana | Arnold | 8 | 4 |
| lemon | Sally | 9 | 5 |
| Pear | Linda | 1 | 0 |
| Pear | Eric | 10 | 1 |
| Pear | Cindy | 16 | 2 |
and "Right" (Data from the current week's report):
Column1Column2Column3Index
| apple | Adam | 777 | 2 |
| banana | Michael | 23 | 3 |
| lemon | Sally | 9 | 4 |
| Pear | John | 1 | 0 |
| Pear | 1 |
Task: (1) Display all rows that have had any of their values modified against each Fruit in the Left (i.e. leave out all fully duplicate rows in both tables) and (2) append all unique rows from the Left that are not present in the Right (in the example below it is the "Cindy" row).
Index column is not mandatory (I have added it on while palying around with Merge & Append tables in Power Query)
I have tried the following Merging - it excludes "Sally" as needed, but I do not know how to add a "Cindy" row.
The desired output:
Column1 Column2 Column3 Index
| apple | Adam | 777 | 2 |
| banana | Michael | 23 | 3 |
| Pear | John | 1 | 0 |
| Pear | 1 | ||
| Pear | Cindy | 16 | 2 |
Any help would be greatly appreciated.
Thank you
all from my guess
let Left = Excel.CurrentWorkbook(){[Name="Left"]}[Content], Right = Excel.CurrentWorkbook(){[Name="Right"]}[Content], Custom1 = let col=Table.ColumnNames(Left), LeftAntiGrp=Table.Group(Table.NestedJoin(Left,col,Right,col,"n",JoinKind.LeftAnti),"Column1",{"n",each _}), RightGrp=Table.Group(Right,"Column1",{"n",each _}) in Table.FromRecords( List.TransformMany( Table.ToRecords(LeftAntiGrp), each let a=RightGrp{[Column1=[Column1]]}?[n]? in if a=null then Table.ToRecords([n]) else List.Transform( List.Zip(List.Transform( {[n],a}, Table.ToRecords ) ), each if _{1}=null then _{0} else _{0}&_{1} ), (x,y)=>y ), col ) in Custom1this is truly bananas... 🙂
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFyTEnMBVKGRkqxOtFKSYl5QAgSLsrLz0kBMizA4jmpufl5QF5wYk5OJZC2BIsGpCYWATk+mXkpID2GyIKuRZnJIDEDZEFnoEqQdkMzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), Left = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), Right = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFyTEnMBVLm5uZKsTrRSkmJeUAIFPDNTM5ITM0BsoyMwTI5qbn5eUBucGJOTiWQtgSLBqQmFgE5XvkZIDlDZDEFMI6NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Added Index" = Table.AddIndexColumn(Right, "Index", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Column1", "Column2", "Column3"}, Left, {"Column1", "Column2", "Column3"}, "Appended Query", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.RowCount([Appended Query])), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 0)), #"Appended Query" = Table.Combine({#"Filtered Rows", Table.SelectRows(Left,(k)=> not List.Contains(#"Added Index"[Index],k[Index]))}) in #"Appended Query"
9 Replies
- lbendlin
Super User
what happened to banana/Arnold?
the primary key 'banana' has got its values updated in the RIght and the row has become 'banana/Michael', if that makes sense.
- lbendlin
That is bananas 🙂 . There is an issue with Pear/Eric and Pear/null too. How do you know these two rows are related? By row index?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFyTEnMBVKGRkqxOtFKSYl5QAgSLsrLz0kBMizA4jmpufl5QF5wYk5OJZC2BIsGpCYWATk+mXkpID2GyIKuRZnJIDEDZEFnoEqQdkMzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), Left = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), Right = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFyTEnMBVLm5uZKsTrRSkmJeUAIFPDNTM5ITM0BsoyMwTI5qbn5eUBucGJOTiWQtgSLBqQmFgE5XvkZIDlDZDEFMI6NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Added Index" = Table.AddIndexColumn(Right, "Index", 0, 1, Int64.Type), #"Appended Query" = Table.Combine({#"Added Index", Table.SelectRows(Left,(k)=> not List.Contains(#"Added Index"[Index],k[Index]))}) in #"Appended Query"
- wdx223_Daniel
Community Champion
all from my guess
let Left = Excel.CurrentWorkbook(){[Name="Left"]}[Content], Right = Excel.CurrentWorkbook(){[Name="Right"]}[Content], Custom1 = let col=Table.ColumnNames(Left), LeftAntiGrp=Table.Group(Table.NestedJoin(Left,col,Right,col,"n",JoinKind.LeftAnti),"Column1",{"n",each _}), RightGrp=Table.Group(Right,"Column1",{"n",each _}) in Table.FromRecords( List.TransformMany( Table.ToRecords(LeftAntiGrp), each let a=RightGrp{[Column1=[Column1]]}?[n]? in if a=null then Table.ToRecords([n]) else List.Transform( List.Zip(List.Transform( {[n],a}, Table.ToRecords ) ), each if _{1}=null then _{0} else _{0}&_{1} ), (x,y)=>y ), col ) in Custom1
