Keeping only unique values from two (or more) tables

[Anonymous]

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 tables, lets call them "BigTable1" and "BigTable2".

Right now I am using queries to create two smaller tables (lets call them "SmallTable1" and "SmallTable2") which contain data from a specific column in those tables (the big tables share some column names, lets call the column "SomeData").

So right now I am at this point (data inside the tables is for demonstration only).

Left column represents "SmallTable1".

Right column represents "SmallTable2".

(I don't know how to format it here to have two distinct tables next to each other, sorry).

SomeData	SomeData
7	6
21	42
13	7
6	2
15	13
	21

At first I wanted to only have one table (remember that right now I have two distinct tables!) but I didn't get that to work.

Important to note: The table should still have two columns! I did not try to have one table with one column but rather one table with two columns "SomeData" kind of like the above table (which, as I mentioned, represents two unique tables) would suggest.

So if anyone can offer some help it would be greatly appreciated.

Anyway, after creating the two tables I tried to use queries (and other built-in Excel functions) to compare the two tables and remove all data from them which are not unique. The following tables are showing what I want to achieve.

SomeData	SomeData
15	42
	2

I tried things like remove duplicates but this obviously keeps the instance of the data and only removes its duplicate(s).

I also tried using things like Table.Distinct, Table.RemoveRows, Table.RemoveMatchingRows, etc. but was unable to achieve my goal.

A few things to keep in mind:

The tables "SmallTable1" and "SmallTable2" are on the same sheet.

They get their data from bigger tables ("BigTable1" and "BigTable2") which each are on their own sheet.

"SmallTable1" and "SmallTable2" do not have a fixed number of data.

This means that "SmallTable1" could potentially not contain any data if the column "SomeData" of "BigTable1" has no data.

At the same time "SmallTable2" could contain a lot of data depending on the number of rows in the "SomeData" column of "BigTable2".

It does not matter if the end result uses two tables with one row, like my example above does ("SmallTable1" and "SmallTable2") or one table with two columns ("SomeData1" and "SomeData2" or whatever).

It does not matter if I can solve this problem with queries or some other tool that Excel provides, but queries would be prefered.

Has anyone solved a similar problem or knows an elegant solution to this problem?

Thank you,

LPLA

[Jakinta]

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.

[watkinnc · Accepted solution]

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

[Anonymous]

This seems to work just fine.

Thank you very much! 👍