Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ns89
New Member

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

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

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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.

 

View solution in original post

5 REPLIES 5
dax
Community Support
Community Support

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.

 

Anonymous
Not applicable

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 helpsappend queries.PNGAppend.PNG

 

Dobby Libr3

 

 

Greg_Deckler
Super User
Super User

You want something like:

 

Table C = 
  DISTINCT(
    UNION(
      'Table A'[Product ID],
      'Table B'[Poduct [ID]
    )
  )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.