Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Keeping only unique values from two (or more) tables

Hello,   I am using Excel (Office 365) and I'm currently struggling with a problem that actually has multiple parts which I will try to describe.   The first thing is that I have two bigger table...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Interesting scenario!

     

    Here's how you do it:

     

    = let columnNames = {"SomeData"}, addCount = Table.Group(Source, columnNames, {{"Count", Table.RowCount, type number}}), selectUniques = Table.SelectRows(addCount, each [Count] = 1), removeCount = Table.RemoveColumns(selectUniques, "Count") in Table.Join(Source, columnNames, removeCount, columnNames, JoinKind.Inner)

     

    That's it!

     

    --Nate

Jakinta's avatar
Jakinta
Solution Sage
5 years ago

Maybe this can help as well.

Create 3 blank queries, rename and code as below to see how it works in Join query.

First 2 are sample queries.

TableA

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMleK1YlWMjIEU4bGYMoMwjFVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Some Data" = _t])
in
    Source

TableB

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlOK1YlWMjECU+ZgEsI2NIZwDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Some Data" = _t])
in
    Source

Join

let
    AU = List.Difference(List.Distinct(TableA[Some Data]),List.Distinct(TableB[Some Data])),
    BU = List.Difference(List.Distinct(TableB[Some Data]),List.Distinct(TableA[Some Data])),
    FINAL = Table.FromRows(List.Zip({AU,BU}), {"TableA","TableB"})
in
    FINAL

 I added List.Distinct in case you have repeating values in columns themselves.