remove duplicates from one column that are also in a column in a different table

[Anonymous]

They say there are no silly questions, but I'm sure this probably is.

I have 2 tables.

One has a column with unique numbers in it. I also have the same column in Table 2, with possibly matching data.

Table 1                                           Table 2

Column1                                        Column1

1111111                                         1111113 

1111112                                         1111115   

1111113                                         etc etc 

1111114

1111115

etc etc

What I'd like to do is be able to remove or filter out from Table1 Column1 any items that are in Table2 Column1 without having to create a new column.

Possible? 

[v-jiascu-msft]

Hi @Anonymous,

You don't want to create a new column, you can try to create a visualization Table. 

1. Create a relationship.

2. Create a visualization: Table and fitler Table2[Column1] as "is not blank".

3. You can export the data if you want.

Best Regards!

Dale

[Anonymous]

Unfortuantely this doesnt provide me with what I need. Dont think I was very clear if I'm honest.

Table1-Column1 holds 5000 different numbers.

1111111

1111112

1111113

1111114

1111115

etc etc

Table2-Column1 holds only a few items, but they are also in Table1-Column1

1111112

1111115

I have created a relationship between the two.

I use the data in Table1-Column1 in various visualisations, but this data is not entirely correct because the duplications have not been removed.

How do I connect the 2 table columns to allow for the removal of duplicates without having to create a new column.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

You want to remove some values without adding a new column. Maybe you can do it in the Query Editor.

1. Open Query Editor, merge two tables with "Left Outer Join".

2. Expand the table. (click the sign in the blue rectangle)

3. Filter the new column, keep nulls only.

4. Remove the new column.

Best Regards!

Dale

[mattbrice]

Look at EXCEPT function:

EXCEPT ( VALUES( Table1[Col1] ) , VALUES ( Table2[Col1] )  )