Remove duplicates from other columns

[Anonymous]

Hi,

I have 5 columns of email addresses and I want to remove any entries from columns 1 and 2 that also appear in columns 3, 4 and 5.

i.e.

check if any entries in column 1 are repeated in column 3, 4 or 5. If so, remove them from column 1.

check if any entries in column 2 are repeated in column 3, 4 or 5. If so, remove them from column 2.

Is this possible?

Thanks! 

[Anonymous]

Yes this is possible.

The easiest way (in my opinion) is open up the Advanced Editor in the Power Query Editor, then add in a Replace Value step at the bottom of all your steps. e.g.

#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers",each [Column3],"",Replacer.ReplaceValue,{"Column1"})

#"Replaced Value" is the name of your step - change this to #"Replaced Value1" etc. for all the extra steps you need to add

#"Promoted Headers: is the name of the step before this one in my code - change this name to the last step name in your code.

[Column3] is your lookup column

"Column1" is the column you are replacing the value of

The above replaces Column1 value to "" (or blank) if Column3 has the same value as Column1