Forum Discussion

ns89's avatar
ns89
New Member
6 years ago
Solved

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

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    You want something like:

     

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

     

    • ns89's avatar
      ns89
      New Member
      6 years ago

      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!

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

     

     

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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.