Power query - remove row based on condition and if value exists

[lbell]

the data is similar to the example below

Field1  Field2

A       123456

A        125467

B        123456

B        3214

the value in Field2 must only occur once in the table, and the record with Field1 = A must be kept.  Must end up with:

Field1  Field2

A       123456

A        125467

B        3214

[lbell]

Found a solution:

Create table1 with Field2 values where Field1 = A

Create table2 with Field1 and Field2 values where Field1 = B

Create new table3 using merge and do right antijoin of table1 and table2, resulting in table3 having only table2 records where Field2 does not exist in table1

remove extra columns from table3

append table3 to table1

this seems to work, but it would be good to know if there is a more elegant method for more complex datasets

[jthomson · Accepted solution]

First step I'd do would be to make a column that highlights whether your Field2 is duplicated, this explains it better than I can:

https://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/

Once you've got that, you can concatenate it with Field1, and filter out anything that has a value B1, B2 etc