Union table with distinct values from another table where value doesn't already exist

Hi ns89 , 

You could try below M code to see whether it work or not 

Table A and Table B

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiOlWJ1opSQwS0fJGMxLBrNgcolQOROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", Int64.Type}, {"amount", Int64.Type}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlPSUTJUitWJVkoEsozArGQgy1QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [NAME = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"amount", Int64.Type}})
in
    #"Changed Type"

 Then the new table C

let
    Source = Table.NestedJoin(A, {"name"}, B, {"NAME"}, "B", JoinKind.RightAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"name", "id", "amount"}),
    #"Expanded B" = Table.ExpandTableColumn(#"Removed Columns", "B", {"NAME", "amount"}, {"NAME", "amount"}),
    #"Appended Query" = Table.Combine({#"Expanded B", Table.FromColumns({A[name],A[amount]},{"NAME","amount"})})
in
    #"Appended Query"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.