Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.