Forum Discussion
Power query - remove row based on condition and if value exists
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
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