Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Hopefully a simple question, I am a beginner to Power BI but have Qlik experience:
I have two data source tables (A and B), both with a Product ID column on each. (Table B has more product IDs than Table A).
1. I would like to create a new table (C) which has all the unique Product IDs from Table A.
2. I would then like to add/union all of the 'unique Product IDs in Table B which have not yet been added to Table C' to my newly created Table C.
Any help would be great! - (In Qlik you could easily do a 'concatenate where not exists' on a table.)
Thanks
Solved! Go to Solution.
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.
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.
Hi @ns89
There is a similar function to SQL UNION in query Editor called "Append Queries" its in the top right of the home ribbon in the query editor.
if you click append quaries as new and select the tables it will create your new table with columns matching. There is this youtube video which explained it really well and let me understand it. https://www.youtube.com/watch?v=qXH4WjCykLc
I would reccomend cleaning up the tables you are appending before you append because it will try and match on every column, where it doesn't match it will create "null" values.
Hope this helps
Dobby Libr3
You 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!
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)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |