cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Community Support
Community Support

Re: 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.

 

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

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

You want something like:

 

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

 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ns89
New Member

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

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!

 

Dobby_Libr3 Resolver I
Resolver I

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

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

 

 

Super User IV
Super User IV

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

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)


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support
Community Support

Re: 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.

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors