none
Removing Consecutive Duplicates RRS feed

  • Question

  • I have a set of data that is arranged like this:

    ##Fruit
    
    Apple
    Apple
    Apple
    Banana
    Banana
    Apple
    Apple
    Banana
    Apple

    I want to transform the data so the duplicates consecutive duplicates will be removed, so it would end up like this

    ##Fruit
    
    Apple
    Banana
    Apple
    Banana
    Apple

    Any ideas?

    Tuesday, August 15, 2017 1:25 PM

Answers

  • You can group on Fruit (e.g. with the default count) and the adjust then generated code to use GroupKind.Local, which takes into account consecutive values, e.g.:

    let
        Source = Table1,
        #"Grouped Rows" = Table.Group(Source, {"Fruit"}, {}, GroupKind.Local)
    in
        #"Grouped Rows"

    Tuesday, August 15, 2017 3:31 PM

All replies

  • You can group on Fruit (e.g. with the default count) and the adjust then generated code to use GroupKind.Local, which takes into account consecutive values, e.g.:

    let
        Source = Table1,
        #"Grouped Rows" = Table.Group(Source, {"Fruit"}, {}, GroupKind.Local)
    in
        #"Grouped Rows"

    Tuesday, August 15, 2017 3:31 PM
  • You can add an index column and a custom column that checks for duplicates, much like you'd add a helper column in Excel. Then, once you've determined which are duplicates, you can filter those out with Table.SelectRows, and remove the helper columns:

    let
        Source = <<SOURCE DATA>>,
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        #"Added Helper Column" = Table.AddColumn(#"Added Index", "Discard?", 
    each if [Index] = 0 then "Keep"
    else if #"Added Index"{[Index] - 1}[Fruit] = [Fruit] then "Discard"
    else "Keep"), #"Filtered Discard" = Table.SelectRows(#"Added Helper Column", each [#"Discard?"] <> "Discard"), #"Removed Helper Columns" = Table.RemoveColumns(#"Filtered Discard",{"Index", "Discard?"}) in #"Removed Helper Columns"


    • Edited by Chris Dutch Tuesday, August 15, 2017 5:51 PM for clarity
    Tuesday, August 15, 2017 5:48 PM
  • I actually like Marcel's solution better than mine. :)
    • Edited by Chris Dutch Tuesday, August 15, 2017 7:53 PM
    Tuesday, August 15, 2017 6:09 PM