Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Removing duplicates by comparing multiple columns with condition

Hi there,    I have this data where I want to remove duplicates. Essentially there are 3 service categories, GO, SVOD and SOTT.  I would like to check for duplicates by comparing all the headers...
Tips and Tricks
mussaenda's avatar
mussaenda
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous ,

 

For clarification, it is done in Power Query.

Advanced editor is where you can see all the steps you do in Power Query.

 

Have you tested the file?

I suggest that you go though each applied step so you'll have the idea of each trasnformation.

 

Basically, I group by title, listed all the services, then created a filter that if there are all the services and the row is SOTT then I will hide it.

 

Hope this helps

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Mia, 

Thank you for your help. It worked now. I swapped the order of SOTT, Go, SVOD and it worked.

#"Added Custom1" = Table.AddColumn(#"Expanded Count", "Filter", each if Text.Contains([Service GRP], "GO")
and Text.Contains([Service GRP], "SVOD")
and Text.Contains([Service GRP], "SOTT")
and [Service] = "SOTT"

then null
else "Show"),