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
Community 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 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's avatar
    mussaenda
    Community Champion
    3 years ago

    hi Anonymous,

     

    before your last applied step,

    dropdown the filter column, what are the values showing?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.