Forum Discussion
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 in the table. However here comes the complicated part.
When one title have all three services, GO, SVOD and SOTT, I would like to remove the SOTT row. As it is a double count/entry for my data. Can this be done in query? Appreciate any help. Thanks.
| Display Category | Title | EpisodeTitle | Episode No. | License Period Start Date | License Period End Date | Service |
| Choose | Detective | Is There A Detective on Board? | 1 | 13-Oct-21 | 9-Nov-21 | SVOD |
| Choose | Detective | Is There A Detective on Board? | 1 | 13-Oct-21 | 9-Nov-21 | SOTT |
| Choose | Detective | I Still Remember | 2 | 19-Oct-21 | 15-Nov-21 | SVOD |
| Choose | Detective | I Still Remember | 2 | 19-Oct-21 | 15-Nov-21 | SOTT |
| Choose | Detective | That's Yui-nya | 3 | 19-Oct-21 | 15-Nov-21 | SVOD |
| Choose | Detective | That's Yui-nya | 3 | 19-Oct-21 | 15-Nov-21 | SOTT |
| Outdoor | Mission Survive | Highlights 6 | 6 | 1-Oct-21 | 29-Dec-21 | SVOD |
| Outdoor | Mission Survive | Highlights 6 | 6 | 1-Oct-21 | 29-Dec-21 | SOTT |
| Outdoor | Mission Survive | Highlights 6 | 6 | 1-Oct-21 | 29-Dec-21 | GO |
| Outdoor | Big Adventures | Party in Pensacola | 12 | 1-Oct-21 | 30-Nov-21 | SVOD |
| Outdoor | Big Adventures | Party in Pensacola | 12 | 1-Oct-21 | 30-Nov-21 | SOTT |
| Outdoor | Big Adventures | Party in Pensacola | 12 | 1-Oct-21 | 30-Nov-21 | GO |
| Outdoor | Big Adventures | The Redfish | 13 | 1-Oct-21 | 30-Nov-21 | SVOD |
| Outdoor | Big Adventures | The Redfish | 13 | 1-Oct-21 | 30-Nov-21 | SOTT |
| Outdoor | Big Adventures | The Redfish | 13 | 1-Oct-21 | 30-Nov-21 | GO |
| Planet | Wildlife | 24 | 0 | 5-Oct-21 | 3-Nov-21 | SVOD |
| Planet | Wildlife | 24 | 0 | 5-Oct-21 | 3-Nov-21 | SOTT |
| Planet | Wildlife | 24 | 0 | 5-Oct-21 | 3-Nov-21 | GO |
| Planet | Crikey! | Komodo | 9 | 5-Oct-21 | 3-Nov-21 | SVOD |
| Planet | Crikey! | Komodo | 9 | 5-Oct-21 | 3-Nov-21 | SOTT |
| Planet | Crikey! | Komodo | 9 | 5-Oct-21 | 3-Nov-21 | GO |
| Planet | Lone Star | Massacre | 11 | 5-Oct-21 | 3-Nov-21 | SVOD |
| Planet | Lone Star | Massacre | 11 | 5-Oct-21 | 3-Nov-21 | SOTT |
| Planet | Lone Star | Massacre | 11 | 5-Oct-21 | 3-Nov-21 | GO |
Cheers
Darren
Hi Anonymous
I attached the pbix file for your reference.
It is not the finest query but you can get idea from it.
Thank you.
Regards,
Mia
7 Replies
Hi Anonymous
I attached the pbix file for your reference.
It is not the finest query but you can get idea from it.
Thank you.
Regards,
Mia
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