Add Suffix to Duplicates with Python Logic

If an animal appears more than once, then that animal’s name should be suffixed with 1, 2, 3… If an animal appears only once, then no suffixing. Hence, if string is “Cheetah, Panther, Cheetah”, then answer would be “Cheetah1, Panther, Cheetah2”

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 134
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Add Suffix to Duplicates with Power Query

Power Query solution 1 for Add Suffix to Duplicates, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Animals"]}[Content], 
  DuplicateAnimals = Table.DuplicateColumn(Source, "Animals", "Animal"), 
  SplitAnimalsByDelimiter = Table.ExpandListColumn(
    Table.TransformColumns(
      DuplicateAnimals, 
      {
        {
          "Animal", 
          Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Animal"
  ), 
  AddOrdering = Table.AddIndexColumn(SplitAnimalsByDelimiter, "Ordering", 1, 1, Int64.Type), 
  GroupByAnimal = Table.Group(
    AddOrdering, 
    {"Animal"}, 
    {{"Count", each Table.RowCount(_)}, {"All", each Table.AddIndexColumn(_, "Index", 1)}}
  ), 
  ExpandAll = Table.ExpandTableColumn(
    GroupByAnimal, 
    "All", 
    {"Animals", "Index", "Ordering"}, 
    {"Animals", "Index", "Ordering"}
  ), 
  FormatIndex = Table.TransformColumnTypes(ExpandAll, {{"Index", type text}}), 
  AddIndexedAnimal = Table.AddColumn(
    FormatIndex, 
    "IndexedAnimal", 
    each if [Count] > 1 then [Animal] & [Index] else [Animal]
  ), 
  OrderBy = Table.Sort(AddIndexedAnimal, {{"Ordering", Order.Ascending}}), 
  Sol = Table.Group(
    OrderBy, 
    {"Animals"}, 
    {{"Answer Expected", each Text.Combine(_[[IndexedAnimal]][IndexedAnimal], ", ")}}
  )[[Answer Expected]]
in
  Sol

Power Query solution 2 for Add Suffix to Duplicates, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Animals"]}[Content], 
  DuplicateAnimals = Table.DuplicateColumn(Source, "Animals", "Animal"), 
  SplitAnimalsByDelimiter = Table.ExpandListColumn(
    Table.TransformColumns(
      DuplicateAnimals, 
      {
        {
          "Animal", 
          Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Animal"
  ), 
  AddOrdering = Table.AddIndexColumn(SplitAnimalsByDelimiter, "Ordering", 1, 1, Int64.Type), 
  GroupByAnimal = Table.Group(
    AddOrdering, 
    {"Animal"}, 
    {{"Count", each Table.RowCount(_)}, {"All", each Table.AddIndexColumn(_, "Index", 1)}}
  ), 
  ExpandAll = Table.ExpandTableColumn(
    GroupByAnimal, 
    "All", 
    {"Animals", "Index", "Ordering"}, 
    {"Animals", "Index", "Ordering"}
  ), 
  FormatIndex = Table.TransformColumnTypes(ExpandAll, {{"Index", type text}}), 
  AddIndexedAnimal = Table.AddColumn(
    FormatIndex, 
    "IndexedAnimal", 
    each if [Count] > 1 then [Animal] & [Index] else [Animal]
  ), 
  OrderBy = Table.Sort(AddIndexedAnimal, {{"Ordering", Order.Ascending}}), 
  Sol = Table.Group(
    OrderBy, 
    {"Animals"}, 
    {{"Answer Expected", each Text.Combine(_[[IndexedAnimal]][IndexedAnimal], ", ")}}
  )[[Answer Expected]]
in
  Sol

Power Query solution 3 for Add Suffix to Duplicates, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Index1 = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  Custom1 = Table.AddColumn(
    Index1, 
    "Custom", 
    each 
      let
        a = Table.FromColumns({Text.Split([Animals], ", ")}), 
        b = Table.AddIndexColumn(a, "Idx", 1, 1), 
        c = Table.Sort(b, {{"Column1", Order.Ascending}}), 
        d = Table.Group(
          c, 
          {"Column1"}, 
          {
            {"Count", each Table.RowCount(_)}, 
            {
              "All", 
              each 
                let
                  a = Table.AddIndexColumn(_, "Idx2", 1, 1), 
                  b = Table.AddColumn(a, "New", each [Column1] & Text.From([Idx2]))
                in
                  b
            }
          }
        )
      in
        d
  )[[Index], [Custom]], 
  Expanded1 = Table.ExpandTableColumn(
    Custom1, 
    "Custom", 
    {"Column1", "Count", "All"}, 
    {"Column1", "Count", "All"}
  ), 
  Expanded2 = Table.ExpandTableColumn(Expanded1, "All", {"Idx", "New"}, {"Idx", "New"}), 
  Custom2 = Table.AddColumn(
    Expanded2, 
    "Answer", 
    each if [Count] = 1 then Text.TrimEnd([New], "1") else [New]
  )[[Index], [Idx], [Answer]], 
  Sorted = Table.Sort(Custom2, {{"Index", Order.Ascending}}), 
  Sol = Table.Group(
    Sorted, 
    {"Index"}, 
    {
      {
        "Answer", 
        each 
          let
            a = Table.Sort(_, {{"Idx", Order.Ascending}})[Answer], 
            b = Text.Combine(a, ", ")
          in
            b
      }
    }
  )[[Answer]]
in
  Sol

Power Query solution 4 for Add Suffix to Duplicates, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  txt = Table.AddColumn(Fonte, "Personalizar", each Text.Split([Animals], ", ")), 
  exp = Table.ExpandListColumn(txt, "Personalizar"), 
  ind = Table.AddIndexColumn(exp, "Índice", 1, 1, Int64.Type), 
  gp = Table.Group(
    ind, 
    {"Personalizar"}, 
    {{"Contagem", each Table.RowCount(_)}, {"tab", each Table.AddIndexColumn(_, "sux", 1, 1)}}
  ), 
  exp2 = Table.ExpandTableColumn(
    gp, 
    "tab", 
    {"Animals", "Índice", "Personalizar", "sux"}, 
    {"Animals", "Índice", "Personalizar.1", "sux"}
  ), 
  sux = Table.AddColumn(
    exp2, 
    "Personalizar.2", 
    each if [Contagem] <> 1 then [Personalizar.1] & Text.From([sux]) else [Personalizar.1]
  ), 
  result = Table.Group(
    sux, 
    {"Animals"}, 
    {
      {
        "Contagem", 
        each Text.Combine(
          List.Transform(Table.Sort(_, {"Índice", Order.Ascending})[Personalizar.2], Text.From), 
          ", "
        )
      }
    }
  )
in
  result

Power Query solution 5 for Add Suffix to Duplicates, proposed by Jan Willem Van Holst:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8snMz9NRgJCuOakFGYl5JRC+UqxOtFJ4fk6ajoJXYnppYpGOAjIPLO2UChL2zc/LTq0EC7ikpgIFECRY0D2/KDMnJ1FHwTsxLz2xKD8fwVKKjQUA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Animals = _t]
  ), 
  Added = Table.AddColumn(Source, "Custom", each Text.Split([Animals], ", ")), 
  Expanded = Table.ExpandListColumn(Added, "Custom"), 
  AddedIndex = Table.AddIndexColumn(Expanded, "original index", 0, 1, Int64.Type), 
  Grouped = Table.Group(
    AddedIndex, 
    {"Animals", "Custom"}, 
    {
      {"Count", each Table.RowCount(_), Int64.Type}, 
      {"all", each Table.AddIndexColumn(_, "index", 1, 1)}
    }
  ), 
  Expandedall = Table.ExpandTableColumn(
    Grouped, 
    "all", 
    {"original index", "index"}, 
    {"original index", "index"}
  ), 
  Addedsuff = Table.AddColumn(
    Expandedall, 
    "suff", 
    each if [Count] > 1 then [Custom] & Text.From([index]) else [Custom]
  )[[Animals], [original index], [suff]], 
  Sorted = Table.Buffer(Table.Sort(Addedsuff, {{"original index", Order.Ascending}})), 
  result = Table.Group(
    Sorted, 
    {"Animals"}, 
    {
      {
        "all", 
        each 
          let
            _list = _[suff]
          in
            Text.Combine(_list, ", ")
      }
    }
  )
in
  result

Power Query solution 6 for Add Suffix to Duplicates, proposed by Sue Bayes:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Duplicate = Table.DuplicateColumn(Source, "Animals", "Animal"),
 Row = Table.AddIndexColumn(Duplicate, "Row", 0, 1, Int64.Type),
 Split = Table.ExpandListColumn(Table.TransformColumns(Row, {{"Animals", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Animals"),
 Trim = Table.TransformColumns(Split,{{"Animals", Text.Trim, type text}}),
 Group = Table.Group(Trim, {"Animal", "Animals"}, {{"Count", each _, type table [Animals=text, Animal=text, Row=number]}}),
 AddCol = Table.AddColumn(Group, "Custom", each 
 let
 Index = Table.AddIndexColumn([Count], "I", 1, 1, Int64.Type),
 Suffix = Table.AddColumn(Index, "Custom", each if Table.RowCount(Index) > 1 then [I] else "", type text),
 Join = Table.AddColumn(Suffix, "Join", each [Animals]&Text.From([Custom]))
 in
 Join),

Solving the challenge of Add Suffix to Duplicates with Excel

Excel solution 1 for Add Suffix to Duplicates, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A6,LAMBDA(a,LET(b,TEXTSPLIT(a,,", "),ARRAYTOTEXT(MAP(b,SEQUENCE(ROWS(b)),LAMBDA(c,n,c&REPT(SUM(N(TAKE(b,n)=c)),SUM(N(b=c))>1)))))))

Excel solution 2 for Add Suffix to Duplicates, proposed by Rick Rothstein:

=MAP(A2:A6,LAMBDA(z,LET(u,UNIQUE(TEXTSPLIT(z,,", ")),REDUCE(z,SEQUENCE(COUNTA(u)),LAMBDA(a,n,LET(i,INDEX(u,n),t,TEXTSPLIT(a,,i),c,COUNTA(t),CONCAT(DROP(TOROW(HSTACK(t,i&IF(c=2,"",SEQUENCE(c-1)))),,-1))))))))

Excel solution 3 for Add Suffix to Duplicates, proposed by Kris Jaganah:

=MAP(A2:A6,LAMBDA(x,LET(a,TEXTSPLIT(x,,", "),b,MAP(SEQUENCE(ROWS(a)),LAMBDA(x,SUM(--(CHOOSEROWS(a,x)=TAKE(a,x))))),c,XLOOKUP(a,a,b,,0,-1),ARRAYTOTEXT(IF(c=1,a,a&b)))))

Excel solution 4 for Add Suffix to Duplicates, proposed by Julian Poeltl:

=MAP(A2:A6,LAMBDA(A,LET(SP,TEXTSPLIT(A,", "),N,TRANSPOSE(MAP(SP,LAMBDA(A,COLUMNS(FILTER(SP,SP=A))))),R,SUBSTITUTE(DROP(REDUCE("",SP,LAMBDA(A,B,VSTACK(A,B&","&IFERROR(ROWS(FILTER(A,TEXTBEFORE(A,",",,,,"")=B))+1,1)))),1),",",""),TEXTJOIN(", ",,IF(N=1,LEFT(R,LEN(R)-1),R)))))

Excel solution 5 for Add Suffix to Duplicates, proposed by Timothée BLIOT:

=MAP(A2:A6,LAMBDA(z,LET(A,TEXTSPLIT(z,","),ARRAYTOTEXT(MAP(SEQUENCE(COLUMNS(A)),LAMBDA(x,INDEX(A,,x)&IF(COUNTA(FILTER(A,A=INDEX(A,,x)))>1,COUNTA(FILTER(TAKE(A,,x),TAKE(A,,x)=INDEX(A,,x))),"")))))))

Excel solution 6 for Add Suffix to Duplicates, proposed by Hussein SATOUR:

=MAP(A2:A6, LAMBDA(z, LET(a, TEXTSPLIT(z, , ", "), b, SEQUENCE(COUNTA(a)), ARRAYTOTEXT(MAP(a, b, LAMBDA(x, y, x & IF(SUM((a = x) * 1) = 1, "", SUM((TAKE(a, y) = x) * 1))))))))

Excel solution 7 for Add Suffix to Duplicates, proposed by Sunny Baggu:

=MAP(A2:A6,LAMBDA(a,LET(_anisplit,TEXTSPLIT(a,,", "),
_anirow,TOROW(UNIQUE(_anisplit)),
_r,SEQUENCE(ROWS(_anisplit)),
_tbl,HSTACK(_r,_anisplit),
ARRAYTOTEXT(DROP(SORT(DROP(REDUCE("",_anirow,LAMBDA(a,v,VSTACK(a,
LET(
_fil,FILTER(_tbl,TAKE(_tbl,,-1)=v),
_rfil,IF(ROWS(_fil)>1,SEQUENCE(ROWS(_fil)),""),
HSTACK(CHOOSECOLS(_fil,1),CHOOSECOLS(_fil,2)&_rfil))))),1)),,1)))))

Excel solution 8 for Add Suffix to Duplicates, proposed by Charles Roldan:

=MAP(A2:A6, LAMBDA(Row, 
LET(Array, TEXTSPLIT(Row, , ", "), n, COUNTA(Array), 
TEXTJOIN(", ", , Array & MAP(Array, SEQUENCE(n), LAMBDA(Item,k, 
LET(f, LAMBDA(i, SUM(--(TAKE(Array, i) = Item))), 
REPT(f(k), f(n) > 1))))))))

Excel solution 9 for Add Suffix to Duplicates, proposed by Guillermo Arroyo:

=MAP(A2:A6,LAMBDA(_a,LET(_s,TEXTSPLIT(_a,,", "),_us,IFERROR(UNIQUE(_s,,1),""),TEXTJOIN(", ",,MAP(SEQUENCE(ROWS(_s)),_s,LAMBDA(_b,_c,IF(OR(_c=_us),_c,_c&SUM(--(TAKE(_s,_b)=_c)))))))))

Solving the challenge of Add Suffix to Duplicates with SQL

SQL solution 1 for Add Suffix to Duplicates, proposed by Zoran Milokanović:

WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
 SELECT
 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS OUTER_SORT
 ,DP.ANIMALS
 FROM DATA DP
),
CALC
AS
(
 SELECT
 T.OUTER_SORT
 ,T.ANIMALS
 ,T.ANIMAL
 ,T.INNER_SORT
 ,ROW_NUMBER() OVER (PARTITION BY T.OUTER_SORT, T.ANIMAL ORDER BY T.INNER_SORT) AS ORDINAL
 ,COUNT(*) OVER (PARTITION BY T.OUTER_SORT, T.ANIMAL) AS APPEARANCE
 FROM
 (
 SELECT
 DP.OUTER_SORT
 ,DP.ANIMALS
 ,TRIM(VALUE) AS ANIMAL
 ,ROW_NUMBER() OVER (PARTITION BY DP.OUTER_SORT ORDER BY (SELECT 1)) AS INNER_SORT
 FROM DATA_PREP DP
 CROSS APPLY STRING_SPLIT(DP.ANIMALS, ',')
 ) T
)
SELECT
 C.ANIMALS
FROM CALC C
GROUP BY
 C.OUTER_SORT
,C.ANIMALS
ORDER BY
 C.OUTER_SORT
;

&&&

Solving the challenge of Add Suffix to Duplicates with Power Query

Solving the challenge of Add Suffix to Duplicates with Excel

Solving the challenge of Add Suffix to Duplicates with SQL

Leave a Reply Cancel reply