Excel BI Challenge Workout 179

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 179 —
(Solutions in any language are also welcome for Excel Challenges)

If a character appears consecutively, then remove all consecutive characters other than first.
Ex - xxxyxyyz
x appears 3 times consecutively. Hence first x will be retained and next 2 will be removed.
yy appears 2 times consecutively, hence only one y will be retained.
Answer = xyxyz

Post answers in Comment.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

Download Practice File - https://lnkd.in/dxEs2tXT

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

Excel BI LinkedIn Post

LinkedIn Post by:
Zoran Milokanović

Removing consecutive characters w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each Text.Combine(List.Accumulate(Text.ToList([String]), {}, (s, d) => if Comparer.OrdinalIgnoreCase(List.Last(s)??"", d) = 0 then s else s & {d})))
in
Solution

LinkedIn post by:
Aditya Kumar Darak

It may be one of the ways to do it in Power Query.

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
  Return = Table.AddColumn(
    Source,
    "Answer",
    each [
      Text = [String],
      Lower = Text.Lower(Text),
      Length = Text.Length(Text) - 1,
      Calc = List.Transform(
        {0 .. Length},
        (f) =>
          if Text.At(Lower, f) = Text.At(" " & Lower, f) then null else Text.At(Text, f)
      ),
      Final = Text.Combine(Calc)
    ][Final]
  )
in
  Return

LinkedIn Post by:
Aditya Kumar Darak

Here is one way out in Excel.

=MAP(
    A2:A6,
    LAMBDA(a,
        LET(
            \sq, SEQUENCE(LEN(a)),
            \s, MID(a, \sq, 1),
            \c, IF(\s = MID(" " & a, \sq, 1), "", \s),
            \r, CONCAT(\c),
            \r
        )
    )
)

Here:
sq = Sequence
s = Split
c = Calculation for removing repeated characters
r = Return

LinkedIn post by:
Gabriel Raigosa

:arrow_forward:EN:

=MAP(A2:A6,LAMBDA(x,LET(st,MID(x,SEQUENCE(99),1),ex,DROP(st,1),CONCAT(LEFT(x),FILTER(ex,DROP(st=ex,-1)=FALSE)))))

:arrow_forward:ES:

=MAP(A2:A6,LAMBDA(x,LET(st,EXTRAE(x,SECUENCIA(99),1),ex,EXCLUIR(st,1),CONCAT(IZQUIERDA(x),FILTRAR(ex,EXCLUIR(st=ex,-1)=FALSO)))))

LinkedIn Post by:
Guillermo Arroyo

Recursive method

=MAP(A2:A6;LAMBDA(a;LET(f;LAMBDA(r;p;q;IF(p="";q;r(r;MID(p;2;99);IF(RIGHT(q)=LEFT(p);q;CONCAT(q;LEFT(p))))));f(f;a;""))))

Other method

=MAP(A2:A6;LAMBDA(a;CONCAT(FILTER(MID(a;ROW(1:100);1);VSTACK(1;MID(a;ROW(1:99);1)<>MID(a;ROW(1:99)+1;1))))))

LinkedIn Post by:
Alejandro Simón

Adjunto my query…

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Answer", each
let
a = Table.FromColumns({Text.ToList([String])}),
b = [Table.Group](http://table.group/)(a, {"Column1"},{{"All", each ""}}, GroupKind.Local, Comparer.OrdinalIgnoreCase)
in Text.Combine(b[Column1], "")
)[[Answer]]
in
Sol

LinkedIn Post by:
Sunny Baggu

=MAP(A2:A6,LAMBDA(a,LET(_m,MID(a,SEQUENCE(LEN(a)),1),_droptop,DROP(_m,1),_dropbott,DROP(_m,-1),_cond,VSTACK(_droptop<>_dropbott,TRUE),CONCAT(TOCOL(IFS(_cond,_m),2)))))

LinkedIn Post by:
Venkata Rajesh

#powerquery solution

let
Source = Data,
Output = Table.AddColumn(Source, "Expected", each
[ list1 = Text.ToList([String]),
list2 = {null} & List.RemoveLastN(list1,1),
list3 = Text.Combine(List.RemoveNulls(List.Transform(List.Positions(list1), each if Text.Lower(list1{_}) <> Text.Lower(list2{_}) then list1{_} else null)))][list3])
in
Output

LinkedIn Post by:
Julien Lacaze

My solution :

=MAP(A2:A6,LAMBDA(a,CONCAT(IF(MID(a,SEQUENCE(LEN(a)),1)<>MID(a,SEQUENCE(LEN(a))+1,1),MID(a,SEQUENCE(LEN(a)),1),""))))

But for it lowercase the Capital E in Excel
(as it takes that last letter of a repetition)

LinkedIn Post by:
Luan Rodrigues

let
 Fonte = Tabela1,
 split = Table.AddColumn(Fonte, "Personalizar", each Text.ToList([String])),
 exp = Table.ExpandListColumn(split, "Personalizar"),
 res = Table.Group(exp, {"String"}, {{"Contagem", each 
 Text.Combine(Table.Group(_,{"Personalizar"}, {{"tab", each List.Distinct(_[Personalizar])}}, GroupKind.Local,Comparer.OrdinalIgnoreCase) [Personalizar],"")}})
in
 res

LinkedIn Post by:
Bo Rydobon

M-Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.TransformRows(Source,each Text.Combine(List.Combine(List.Transform([List.Zip](http://list.zip/)({Text.ToList([String]),Text.ToList("0"&[String])}), each List.RemoveMatchingItems(_,{_{1}},Comparer.OrdinalIgnoreCase)))))
in
Ans

LinkedIn Post by:
Rick Rothstein

First attempt…

=MAP(A2:A6,LAMBDA(a,LET(s,SEQUENCE(LEN(a)),y,MID(a&"/",s,1),z,MID(a&"/",s+1,1),CONCAT(MAP(TEXTSPLIT(CONCAT(IF(y=z,y,y&" "))," "),LAMBDA(x,LEFT(x)))))))

LinkedIn Post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=BYROW(A2:A6; LAMBDA(r; LET(_s; SEQUENCE(LEN(r));_m; MID(r;_s;1);_n; MID(LOWER(r);1;_s); CONCAT(FILTER(_m; ISERROR(SEARCH("|| "; SUSBTITUTE(_n;_m;"|")&" ")))))))

LinkedIn Post by:
Stefan Olsson

This works in hashtag#googlesheets :

=ArrayFormula(
REDUCE(A2:A6, CHAR(SEQUENCE(26,1,65)),
LAMBDA(string, chr,
REGEXREPLACE(string, "(?i)("&chr&"){2,}", "$1")
)))

LinkedIn Post by:
Nicolas Micot

I wanted to use a bit of VBA for this one:
Function f_challenge(texte As String) As String
Dim resultat As String

For i = 1 To Len(texte)
 If Not LCase(Mid(texte, i, 1)) = LCase(Right(resultat, 1)) Then
 resultat = resultat & Mid(texte, i, 1)
 End If
Next i

f_challenge = resultat
End Function

LinkedIn Post by:
Rick Rothstein

Second attempt…

=MAP("/"&A2:A6,LAMBDA(a,LET(s,SEQUENCE(LEN(a)),m,MID(a,s+1,1),CONCAT(IF(m<>MID(a,s,1),m,"")))))

LinkedIn Post by:
محمد حلمي

=MAP(A2:A6,LAMBDA(a,LET(
c,MID(a,SEQUENCE(LEN(a)),1),
CONCAT(FILTER(c,VSTACK(DROP(c,1),1)<>c)))))

LinkedIn Post by:
Bo Rydobon

=MAP(A2:A6,LAMBDA(a,LET(s,SEQUENCE(40),b,MID(a,s,1),CONCAT(REPT(b,b<>MID(0&a,s,1))))))

LinkedIn Post by:
John Jairo V.

Hi to all!

One option could be:

:white_check_mark:

=REDUCE("",ROW(1:20),LAMBDA(i,x,LET(c,MID(A2:A6,x,1),IF(c=RIGHT(i),i,i&c))))

Blessings!