How to replace second and more instances of duplicate values with other value

[Harshch]

Hello community,

I am having data as multiple duplicate records as mentioned in A column and want the output as 1st instance as same and other instances as _duplicate as mentioned in column B

Please provide a solution for the same

Thanks In Advance

[Harshch]

@Greg_Deckler Thanks for this solution, it worked
Would be really helpful if you can post logical understanding of this code

Thanks alot

[Greg_Deckler]

@Harshch Sure, so the fist thing is to add an index column so that we can figure out "first" from "not first". We then group by the value column (A) and as aggregates we have 2 columns. One is the Min of the Index column, the second is all rows. We can then expand the all rows column retrieving just the Index value for the rows. Now, we can add a column that essentially just compares the Index column to our aggregated Min column. If the two are equal, then that is the first row. If they are not equal, then it is not the first row. We then just clean up our temporary Min and expanded Index columns.

Let me know if that is a sufficient explanation.

[Greg_Deckler · Accepted solution]

@Harshch Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZ1QyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Column1"}, {{"Min", each List.Min([Index]), type number}, {"Rows", each _, type table [Column1=nullable text, Index=number]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Column2", each if [Min] = [Index] then [Column1] else [Column1] & "_DUPLICATE"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Min", "Index"})
in
    #"Removed Columns"