Removing duplicates by comparing multiple columns with condition

[Anonymous]

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

[mussaenda · Accepted solution]

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

[Anonymous]

just to clarify, everything is done in the advance editor? sorry, quite a newbie here. thanks.

So it groups all the columns and createa list and remove the duplicates there if it detects all 3 services? 

[mussaenda]

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]

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"),

[Anonymous]

Hi there, 

I have tried step by step but in the end all my results are showing "Show", there is nothing in "null". I screwed up somewhere I think.  Here is the what from the advanced editor: 

let
    Source = Sql.Databases(""),
    CMS_DataWarehouse = Source{[Name="CMS_DataWarehouse"]}[Data],
    dbo_cms_ondemandtitles = CMS_DataWarehouse{[Schema="dbo",Item="cms_ondemandtitles"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_cms_ondemandtitles,{{"LicensePeriodStartDate", type date}, {"LicensePeriodEndDate", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"DisplayCategory", "Channels"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Duration", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Service] <> "Linear")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Channels", "Title", "AltTitle", "EpisodeTitle", "EpisodeNumber", "SeasonNo", "Duration", "LicensePeriodStartDate", "LicensePeriodEndDate", "YearMonth"}, {{"Count", each _, type table [Channels=nullable text, Title=nullable text, AltTitle=nullable text,EpisodeTitle=nullable text,  EpisodeNumber=nullable text, SeasonNo=nullable text, #"Duration"=nullable number, LicensePeriodStartDate=nullable date, LicensePeriodEndDate=nullable date, YearMonth=nullable text, Service=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Service GRP", each let GetList =
Table.Column([Count], "Service"),

ExtractList = 
Text.Combine(List.Transform(GetList, Text.From), "#(lf)")

in 
ExtractList),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Service"}, {"Service"}),
    #"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"),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Filter] <> ""))
in
    #"Filtered Rows1"

[mussaenda]

hi @Anonymous,

before your last applied step,

dropdown the filter column, what are the values showing?

[Anonymous]

hi @mussaenda, it was only showing Show. There was nothing else. So I had to change to this and i can see show and null entries. 

#"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"),

thanks.