Forum Discussion

PshemekFLK's avatar
PshemekFLK
Helper IV
5 years ago
Solved

Append 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 a...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

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