PowerQuery - Remove Rows with Identical Column Data

[gruntz13]

Hi all,

New to the forum. Have an issue I just can't seem to find a fix.

I'm trying to combine 3 identical lists to find all possible permutations. Easy enough, I combine the tables using PowerQuery. Where I get hung up is that if a value is used in one column, it cannot be used in either of the other two. Example screenshot below. The value Headline1 is eligible to appear in any of the three columns, however, if it appears in one it cannot be in either of the other two.

Is there a way that I can easily delete values like this?

Happy to provide more detail if needed. Appreciate any help.


:

 

[Misca]

Welcome to the board!

I can come up with a couple of ways to do this. With just three columns to compare I'd go with a helper column with a something like

=[Column1]<>[Column.1.1] and [Column1]<>[Column.1.2] and [Column1.1]<>[Column.1.2]

 

[alansidman]

create a Custom Column

if([Column1]=[Column1.1] or [Column1]=[Column1.2] or [Column1.1]=[Column1.2]) then 1 else 0

Then filter on the custom column to delete any 1's
Then delete the custom column

 

[JGordon11]

another way

= Table.SelectRows(YourTableName, each [Column1]<>[Column.1.1] and [Column1]<>[Column.1.2] and [Column1.1]<>[Column.1.2])

 

[gruntz13]

thank you all for the solutions. This is exactly what I was trying to do.