Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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! 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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