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 

 

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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'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"