# Excel BI Challenge Workout 196

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

Repeat a character as per its position in the string and insert spaces between them.
Ex - Mango
M-aa-nnn-gggg-ooooo

(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)

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

Henriette Hamer

`=TEXTJOIN("-";TRUE;REPT(MID(A2;SEQUENCE(1;LEN(A2);1;1);1);SEQUENCE(1;LEN(A2);1;1)))`

I didn’t get the expected answer in B6, but I feel that that was a typo in the exercise?
Formula is just for the row, couldn’t get it to work for the whole range in column A.

## LinkedIn post byPieter de B.

`=MAP(A2:A6,LAMBDA(a,LET(b,LEN(a),TEXTJOIN("-",1,(REPT(MID(a,SEQUENCE(b),1),SEQUENCE(b)))))))`

where `sky` in the example returns 3 y’s of course.

My #powerquery solution. Works, but feels overly verbose given the straightforwardness of this challenge…

``````let
Source = Table.DuplicateColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "String", "Letter"),
SplitToRows = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Letter", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Letter"),
Group = [Table.Group](http://table.group/)(SplitToRows, {"String"}, {{"All", each _, type table [String=text, Letter=nullable text]}}),
in
CombineText
``````

## LinedIn post by:Zoran Milokanović

Repeating characters w/ #powerquery. #bitanbit #powebi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each [
l = Text.ToList([String]),
t = List.Accumulate(List.Positions(l), "", (s, c) => s & (if s = "" then s else "-") & Text.Repeat(l{c}, c + 1))][t])
in
Solution
``````

## Linked In Post by:Alejandro Simón

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
let
a = Text.ToList([String]),
b = List.Transform({0..List.Count(a)-1}, each Text.Repeat(a{_},_+1)),
c = Text.Combine(b,"-")
in
Sol
``````

`TEXTJOIN("-",,REPT(MID(B3,ROW(INDIRECT("1:" & LEN(B3))),1),ROW(INDIRECT("1:" & LEN(B3)))))`

Press #CSE ===> Ctrl+Shift+Enter

Tyler N.

``````let
Src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
in
Res
``````

M-Code

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.TransformColumns(Source,{"String", each Text.Combine(List.Transform({1..Text.Length(_)},(n)=> Text.Repeat(Text.Range(_,n-1,1),n )),"-") })
in
Ans
``````

M-Code

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
in
``````

Here is a solution that does not use the LAMBDA function (it assumes the text will not be longer than 26 character - if it could be, just change the Z’s to a column designation whose column number is larger than the longest text string)…

`=DROP(TEXTSPLIT(ARRAYTOTEXT(REPT(MID(A2:A6,COLUMN(A:Z),1),COLUMN(A:Z)),),,", , ",1),-1)`

``````=MAP(
A2:A6,
LAMBDA(a,
LET(
_m, MID(a, SEQUENCE(LEN(a)), 1),
_seq, SEQUENCE(ROWS(_m)),
_rep, REPT(_m, _seq),
TEXTJOIN("-", , _rep)
)
)
)
``````

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ExpandToRows = Table.ExpandTableColumn(AddNestTab, "NT", {"Column1", "Id"}, {"Column1", "Id"}),
in
Result
``````

``````let
Fonte = Tabela1,
res = Table.Group(exp, {"String"}, {{"Contagem", each
[
][b]
}})
in
res
``````

Power Query

``````let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = List.Transform(Origen[String], each Text.ToList(_)),
b = List.Transform(a, (x)=>
Text.Combine(List.Transform({0..List.Count(x)-1}, each Text.Repeat(x{_}, _+1)), "-"))
in
b
``````

``````=LET(r,ROW(1:9),
MAP(A2:A6,LAMBDA(a,TEXTJOIN("-",,REPT(MID(a,r,1),r)))))
``````

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Col = Table.AddColumn(Source, "Result", each let str = [String] in Text.Combine(List.Transform({0..Text.Length(str)-1}, each Text.Repeat(Text.Middle(str, _, 1), _ + 1)), "-"))
in
Col
``````

``````=MAP(A2:A6,
LAMBDA(_str,
LET(_seq,SEQUENCE(,LEN(_str)),
_spl,MID(_str,_seq,1),
TEXTJOIN("-",1,REPT(_spl,_seq)))))
``````

Although mine is similar to many here, I will still post as more of a thanks to this community for all the things that it is teaching me.

`=MAP(A2:A6,LAMBDA(c,LET(a,SEQUENCE(LEN(c)),b,REPT(MID(c,a,1),a),TEXTJOIN("-",,b))))`

``````=MAP(