Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |