Home » Symbol Replace by Frequency

Symbol Replace by Frequency

If an alphabet appears once in a word, replace it with a “?”. If an alphabet appears more than once in a word, replace it with a “*”.

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

Solving the challenge of Symbol Replace by Frequency with Power Query

Power Query solution 1 for Symbol Replace by Frequency, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.TransformRows(
    Source, 
    each Text.Combine(
      List.Transform(
        Text.ToList(Text.Lower([Words])), 
        (a) => if Text.Length(Text.Select(Text.Lower([Words]), a)) = 1 then "?" else "*"
      )
    )
  )
in
  Ans
Power Query solution 2 for Symbol Replace by Frequency, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Solution = Table.FromList(
    List.Transform(
      Source[Words], 
      each 
        let
          t = Text.ToList(Text.Upper(_))
        in
          List.Accumulate(
            t, 
            "", 
            (s, d) =>
              s & (if List.Count(List.PositionOf(t, d, Occurrence.All)) > 1 then "*" else "?")
          )
    ), 
    null, 
    {"Expected Answer"}
  )
in
  Solution
Power Query solution 3 for Symbol Replace by Frequency, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(
    Source, 
    "Ans", 
    each List.Accumulate(
      {"a" .. "z"}, 
      Text.Lower([Words]), 
      (s, c) =>
        if Text.Length(s) - Text.Length(Text.Replace(s, c, "")) = 1 then
          Text.Replace(s, c, "?")
        else
          Text.Replace(s, c, "*")
    )
  )
in
  Return
Power Query solution 4 for Symbol Replace by Frequency, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(
    Source, 
    "Ans", 
    each [
      Lower = Text.Lower([Words]), 
      ToList = Text.ToList(Lower), 
      Replace = List.Transform(
        ToList, 
        (f) => if List.Count(List.Select(ToList, (x) => x = f)) = 1 then "?" else "*"
      ), 
      Combine = Text.Combine(Replace)
    ][Combine]
  )
in
  Return
Power Query solution 5 for Symbol Replace by Frequency, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "Expected", 
    each 
      let
        a = Text.ToList(Text.Lower([Words])), 
        b = Table.FromColumns({a}), 
        c = Table.ToColumns(
          Table.Group(b, {"Column1"}, {{"Count", each if Table.RowCount(_) = 1 then "?" else "*"}})
        ), 
        d = List.ReplaceMatchingItems(a, List.Zip({c{0}, c{1}}))
      in
        Text.Combine(d)
  )[[Expected]]
in
  Sol
Power Query solution 6 for Symbol Replace by Frequency, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  list = Table.AddColumn(Fonte, "Personalizar", each Text.ToList(Text.Lower([Words]))), 
  tab = Table.ExpandListColumn(list, "Personalizar"), 
  gp = Table.Group(
    tab, 
    {"Words"}, 
    {
      {
        "Contagem", 
        each Table.AddColumn(
          Table.Group(_, {"Personalizar"}, {{"Count", each List.Count(_)}}), 
          "sub", 
          each if [Count] = 1 then "?" else "*"
        )
      }
    }
  ), 
  zip = Table.AddColumn(
    gp, 
    "Personalizar", 
    each List.Zip({[Contagem][Personalizar], [Contagem][sub]})
  )[[Words], [Personalizar]], 
  res = Table.AddColumn(
    zip, 
    "Result", 
    each Text.Combine(List.ReplaceMatchingItems(Text.ToList(Text.Lower([Words])), [Personalizar]))
  )[[Words], [Result]]
in
  res
Power Query solution 7 for Symbol Replace by Frequency, proposed by Brian Julius: 
let
  Source = Table.DuplicateColumn(
    Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
    "Words", 
    "Letters"
  ), 
  TextToList = Table.AddColumn(Source, "Custom", each Text.ToList(Text.Lower([Letters]))), 
  Expand = Table.ExpandListColumn(TextToList, "Custom"), 
  Group = Table.Group(
    Expand, 
    {"Words", "Custom"}, 
    {
      {"All", each _, type table [Words = text, Letters = text, Custom = text]}, 
      {"Count", each Table.RowCount(_), Int64.Type}
    }
  ), 
  AddChar = Table.AddColumn(Group, "Character", each if [Count] = 1 then "?" else "*"), 
  RemoveCols = Table.PrefixColumns(Table.RemoveColumns(AddChar, {"All", "Count"}), "x"), 
  Join = Table.SelectColumns(
    Table.Join(Expand, {"Words", "Custom"}, RemoveCols, {"x.Words", "x.Custom"}, JoinKind.LeftOuter), 
    {"Words", "x.Character"}
  ), 
  Regroup = Table.Group(Join, {"Words"}, {{"Answer", each [x.Character]}}), 
  Extract = Table.TransformColumns(
    Regroup, 
    {"Answer", each Text.Combine(List.Transform(_, Text.From)), type text}
  )
in
  Extract
Power Query solution 8 for Symbol Replace by Frequency, proposed by Matthias Friedmann: 
let
  Source = Excel.CurrentWorkbook(){[Name = "replWords"]}[Content], 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Replaced", 
    each [
      a = Text.ToList(Text.Lower([Words])), 
      b = List.Transform(a, (x) => if List.Count(List.Select(a, each _ = x)) = 1 then "?" else "*"), 
      c = Text.Combine(b)
    ][c]
  )
in
  #"Added Custom"
Power Query solution 9 for Symbol Replace by Frequency, proposed by Udit Chatterjee: 
let
  fxWordEncoder = (word as text) =>
    let
      cleanWord = Text.Lower(Text.Trim(word)), 
      wordList = Text.ToList(cleanWord), 
      wordCounts = List.Transform(
        wordList, 
        each List.Count(Text.PositionOf(cleanWord, _, Occurrence.All))
      ), 
      encodedList = List.Transform(wordCounts, each if _ = 1 then "?" else "*"), 
      encodedWord = Text.Combine(encodedList)
    in
      encodedWord, 
  Source = xlProblem163, 
  addFunctionCol = Table.AddColumn(Source, "Encoded Word", each fxWordEncoder([Words]), type text), 
  keepRequiredCol = Table.SelectColumns(addFunctionCol, {"Encoded Word"})
in
  keepRequiredCol

Solving the challenge of Symbol Replace by Frequency with Excel

Excel solution 1 for Symbol Replace by Frequency, proposed by Bo Rydobon 🇹🇭: 
=MAP(LOWER(A2:A11),LAMBDA(a,CONCAT(IF(LEN(a)-LEN(SUBSTITUTE(a,MID(a,SEQUENCE(LEN(a)),1),))=1,"?","*"))))
Excel solution 2 for Symbol Replace by Frequency, proposed by Rick Rothstein: 
=MAP(LOWER(A2:A11),LAMBDA(r,REDUCE("",MID(r,SEQUENCE(LEN(r)),1),LAMBDA(a,x,a&IF(LEN(r)-LEN(SUBSTITUTE(r,x,""))=1,"?","*")))))
Excel solution 3 for Symbol Replace by Frequency, proposed by Rick Rothstein: 
=MAP(LOWER(A2:A11),LAMBDA(a,CONCAT(IF(LEN(a)-LEN(SUBSTITUTE(a,MID(a,SEQUENCE(LEN(a)),1),""))>1,"*","?"))))
Excel solution 4 for Symbol Replace by Frequency, proposed by John V.: 
=MAP(A2:A11,LAMBDA(x,LET(s,SEQUENCE(LEN(x)),m,MID(x,s,1),CONCAT(IF(MMULT(N(m=TOROW(m)),s^0)=1,"?","*")))))
Excel solution 5 for Symbol Replace by Frequency, proposed by محمد حلمي: 
=MAP(A2:A11,LAMBDA(s,LET(
c,MID(s,SEQUENCE(LEN(s)),1),
E,MAP(c,LAMBDA(A,SUM(--(c=A)))),
CONCAT(IF(E=1,"?","*")))))
Excel solution 6 for Symbol Replace by Frequency, proposed by 🇰🇷 Taeyong Shin: 
=MAP(A2:A11,LAMBDA(x,LET(c,MID(x,SEQUENCE(LEN(x)),1),CONCAT(IF(MMULT(XMATCH(c,c,,{1,-1}),{-1;1}),"*","?")))))
Excel solution 7 for Symbol Replace by Frequency, proposed by Kris Jaganah: 
=MAP(A2:A11,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),CONCAT(IF(MAP(a,LAMBDA(y,SUM(--(y=a))))>1,"*","?")))))
Excel solution 8 for Symbol Replace by Frequency, proposed by Julian Poeltl: 
=MAP(A2:A11,LAMBDA(W,LET(SP,MID(W,SEQUENCE(LEN(W)),1),CONCAT(IF(ISNUMBER(XMATCH(SP,UNIQUE(SP,,1))),"?","*")))))
Excel solution 9 for Symbol Replace by Frequency, proposed by Aditya Kumar Darak 🇮🇳: 
=REDUCE(
 LOWER(A2:A11),
 CHAR(SEQUENCE(26, , 97)),
 LAMBDA(a, b,
 IF(
 LEN(a) - LEN(SUBSTITUTE(a, b, "")) = 1,
 SUBSTITUTE(a, b, "?"),
 SUBSTITUTE(a, b, "*")
 )
 )
)
Excel solution 10 for Symbol Replace by Frequency, proposed by Timothée BLIOT: 
=LET(A, LOWER(TEXTSPLIT(TEXTJOIN("/",,BYROW(A2:A11, LAMBDA(a, TEXTJOIN(":",,MID(a,SEQUENCE(LEN(a)),1)) ))),":","/",,,"")),
BYROW(A, LAMBDA(a, CONCAT( MAP(a, LAMBDA(x, IF(x<>"", IF(COUNTA(FILTER(a, a=x))>1,"*","?"),"" ) )) ) )) )
Excel solution 11 for Symbol Replace by Frequency, proposed by Hussein SATOUR: 
=MAP(A2:A11, LAMBDA(x, LET(a, MID(x,SEQUENCE(LEN(x)),1), CONCAT(MAP(a, LAMBDA(y, IF(SUM((a=y)*1)=1,"?","*")))))))
Excel solution 12 for Symbol Replace by Frequency, proposed by Oscar Mendez Roca Farell: 
=BYROW( A2:A11, LAMBDA( r, LET(_a, CHAR(SEQUENCE(26,,97)), _w, MID( r, SEQUENCE( LEN( r)), 1), CONCAT( LOOKUP(  _w, _a,  IF( ISNA( FIND( REPT( _a&"*",2), r)), "?","*"))))))
Excel solution 13 for Symbol Replace by Frequency, proposed by Sunny Baggu: 
=MAP(A2:A11,LAMBDA(a,
LET(_m,MID(a,SEQUENCE(LEN(a)),1),
_uo,UNIQUE(_m,,TRUE),
_u,UNIQUE(_m),
_list,IFNA(XMATCH(_u,_uo),_u),
_oddlist,FILTER(_list,ISERROR(--_list)),
_aa,REDUCE(a,_uo,LAMBDA(a,v,SUBSTITUTE(a,v,"?"))),
IFERROR(IFERROR(REDUCE(LOWER(_aa),LOWER(_oddlist),LAMBDA(a,v,SUBSTITUTE(a,v,"*"))),_aa),REPT("*",LEN(a))))))
Excel solution 14 for Symbol Replace by Frequency, proposed by Sunny Baggu: 
=BYROW(A2:A11,LAMBDA(d,
LET(_e1,LAMBDA(x,MID(LOWER(x),SEQUENCE(LEN(x)),1)),
CONCAT(IF(DROP(REDUCE("",_e1(d),LAMBDA(a,v,VSTACK(a,SUM(FILTER(SEQUENCE(LEN(d),,1,0),_e1(d)=v))))),1)=1,"?","*")))))
Excel solution 15 for Symbol Replace by Frequency, proposed by Md. Zohurul Islam: 
=MAP(A2:A11,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),b,MAP(a,LAMBDA(y,SUM(ABS(a=y)))),d,CONCAT(IF(b=1,"?","*")),d)))
Excel solution 16 for Symbol Replace by Frequency, proposed by Stefan Olsson: 
=MAP(A2:A11, 
LAMBDA(word, 
REDUCE("", REGEXEXTRACT(word, REPT("(.)",LEN(word))), 
LAMBDA(ans,l,
 ans&IF(REGEXMATCH(lower(word), l&".*"&l),"*","?")
))))
Excel solution 17 for Symbol Replace by Frequency, proposed by Gerson Pineda: 
=MAP(A2:A11,LAMBDA(x,LET(l,MID(x,SEQUENCE(LEN(x)),1),CONCAT(MAP(l,LAMBDA(i,IF(COUNT(1/(l=i))=1,"?","*")))))))
Excel solution 18 for Symbol Replace by Frequency, proposed by Guillermo Arroyo: 
=MAP(A2:A11;LAMBDA(a;LET(b;MID(a;SEQUENCE(LEN(a));1);CONCAT(IF(MAP(b;LAMBDA(c;SUM(--(b=c))))-1;"*";"?")))))
Excel solution 19 for Symbol Replace by Frequency, proposed by Anup Kumar: 
=MAP(A2:A11,LAMBDA(a,LET(
txt,UPPER(a),
txtArr, MID(txt,SEQUENCE(LEN(txt),,1,1),1),
cntChq, SCAN(0, txtArr,LAMBDA(p,q,ROWS(FILTER(txtArr,txtArr=q)))),
REDUCE("",cntChq,LAMBDA(p,q,IF(q=1,p&"?",p&"*")))
)))
Excel solution 20 for Symbol Replace by Frequency, proposed by Gabriel Raigosa: 
=MAP(A2:A11,LAMBDA(x,LET(w,MID(x,SEQUENCE(LEN(x)),1),CONCAT(IF(ISERROR(XMATCH(w,UNIQUE(w,,1))),"*","?"))))) 

▶️ES:
 =MAP(A2:A11,LAMBDA(x,LET(w,EXTRAE(x,SECUENCIA(LARGO(x)),1),CONCAT(SI(ESERROR(COINCIDIRX(w,UNICOS(w,,1))),"*","?")))))
Excel solution 21 for Symbol Replace by Frequency, proposed by Ricardo Alexis Domínguez Hernández: 
=BYROW(A2:A11,LAMBDA(x,CONCAT(IF(LEN(x)-LEN(SUBSTITUTE(LOWER(x),MID(LOWER(x),SEQUENCE(,LEN(x)),1),""))>1,"*","?"))))
Excel solution 22 for Symbol Replace by Frequency, proposed by roberto mensa: 
=MAP(A2:A11,LAMBDA(a,LET(l,LEN(a),s,CODE(MID(a,SEQUENCE(,l),1)),LEFT(CONCAT(IF(FREQUENCY(s,s)=1,"?","*")),l))))
Excel solution 23 for Symbol Replace by Frequency, proposed by Ibrahim Sadiq: 
=MAP(A2:A11,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),CONCAT(IF(ISNUMBER(XMATCH(a,UNIQUE(a,,1),)),"?","*")))))
Excel solution 24 for Symbol Replace by Frequency, proposed by Jeff Blakley: 
=LET(chars, MID(A2, SEQUENCE(LEN(A2)), 1), TEXTJOIN("",,IF(XMATCH(chars, chars)=XMATCH(chars, chars,,-1), "?", "*")))
Excel solution 25 for Symbol Replace by Frequency, proposed by Patrick O’Beirne: 
=CONCAT(LET(letters,LOWER(MID(A4,SEQUENCE(1,LEN(A4)),1)),IF(LEN(SUBSTITUTE(LOWER(A4),letters,""))
Excel solution 26 for Symbol Replace by Frequency, proposed by Jan Karel Pieterse: 
=TEXTJOIN("",TRUE,LET(chrs,MID(A2,SEQUENCE(LEN(A2)),1),IF(LEN(A2)-LEN(SUBSTITUTE(A2,chrs,""))=1,"?","*")))

&&

Leave a Reply