cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nedhlp New Member
New Member

Remove duplicates from other columns

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! 

1 REPLY 1
jd009 Member
Member

Re: Remove duplicates from other columns

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