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