Forum Discussion
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionYou want something like:
Table C =
DISTINCT(
UNION(
'Table A'[Product ID],
'Table B'[Poduct [ID]
)
)
Hi Greg_Deckler ,
Thanks for this. Sorry I didn't specify but what I would also need are other columns from Table A (such as the 'Order' Column).
For example:
Product A Order A --- From Table A
Product B Order A --- From Table A
Product C (blank) --- From Table B (As Product C is not in Table A, we have unioned it on here)
Is this possible? Thanks again!
- Greg_Deckler
Not sure what Order is (text, numeric, ?). Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
In theory, if it is a number, use ADDCOLUMNS around your UNION and do SUM of a FILTER of Table A where ProductID in table A equals ProductID. (Might have to use EARLIER)
