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