Forum Discussion
Helper IVAppend with only new fields from second table
Hello,
I have two tables which I want to append. Table 1 has products repeated many times due to multiple items under each product. In the second table I have product fields with unique values and no item field.
I would like to append the tables resulting only in new products from table 2 being added to table 1.
Table 1
Product Item
A 1
B 2
B 3
C 4
C 5
Table 2
Product
A
B
C
D
Desired result:
Product Item
A 1
B 2
B 3
C 4
C 5
D null
What I'm getting with an append:
Product Item
A 1
B 2
B 3
C 4
C 5
A null
B null
C null
D null
"Removing Duplicates" will not work of course as it will not only get rid of null items but the relevant items as well. It seems like I'm missing an easy solution here.
Use Anti-join to extract specific records,
let Tab1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozgLGMwyxnIMoGzTJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Item = _t]), Tab2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]), #"LeftAnti Join" = Table.NestedJoin(Tab2, "Product", Tab1, "Product", "t1", JoinKind.LeftAnti), Appended = Tab1 & Table.ExpandTableColumn(#"LeftAnti Join", "t1", {"Item"}, {"Item"}) in Appended
1 Reply
CNENFRNLCommunity Champion
Use Anti-join to extract specific records,
let Tab1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozgLGMwyxnIMoGzTJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Item = _t]), Tab2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]), #"LeftAnti Join" = Table.NestedJoin(Tab2, "Product", Tab1, "Product", "t1", JoinKind.LeftAnti), Appended = Tab1 & Table.ExpandTableColumn(#"LeftAnti Join", "t1", {"Item"}, {"Item"}) in Appended