Excel BI Challenge Workout 196

[EnterpriseDNA]

— 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

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/dMsEVhs9

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

Excel BI’s LinkedIn post

Excel BI on LinkedIn: #excel #excelchallenge #powerquerychallenge...

[EnterpriseDNA]

LinkedIn Post by
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.

[EnterpriseDNA]

LinkedIn post by
Pieter 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.

[EnterpriseDNA]

LinkedIn Post by
Brian Julius

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]}}),
AddPositionIndex = Table.RemoveColumns( Table.AddColumn(Group, "Position", each Table.AddIndexColumn([All], "Position", 1, 1)), "All"),
AddRepeatText = Table.RemoveColumns( Table.AddColumn(AddPositionIndex, "Repeat", each Table.AddColumn( [Position], "Repeat", each Text.Repeat([Letter], [Position]))), "Position"),
SelCols = Table.RemoveColumns( Table.AddColumn(AddRepeatText, "Repeats", each Table.SelectColumns( [Repeat], "Repeat")), "Repeat"),
CombineText = Table.RemoveColumns( Table.AddColumn(SelCols, "Answer", each Text.Combine([Repeats][Repeat], "-")), "Repeats")
in
CombineText

[EnterpriseDNA]

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

[EnterpriseDNA]

Linked In Post by:
Alejandro Simón

Adjunto mi query…

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

[EnterpriseDNA]

LinkedIn post by:
Morteza Rahmani

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

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

[EnterpriseDNA]

LinkedIn post by:
Tyler N.

let
Src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Res = Table.AddColumn(Src,"R",each Text.Combine(Table.AddColumn(Table.AddIndexColumn(Table.FromList(Text.ToList([String]),null,{"a"}),"b",1,1),"x",each Text.Repeat([a],[b]))[x],"-"))
in
Res

[EnterpriseDNA]

LinkedIn post by:
Bo Rydobon

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

[EnterpriseDNA]

LinkedIn post by:
JvdV

M-Code

let
   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   Answer = Table.AddColumn(Source, "Answer", each Text.Range(List.Accumulate(Text.ToList([String]),[s="",n=1],(x,y)=>[s=x[s]&"-"&Text.Repeat(y,x[n]),n=x[n]+1])[s],1))
in
   Answer

[EnterpriseDNA]

LinkedIn Post by
Rick Rothstein

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)

[EnterpriseDNA]

LinkedIn Post by:
Sunny Baggu

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

[EnterpriseDNA]

LinkedIn Post by:
Krzysztof Kominiak

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddNestTab = Table.AddColumn(Source, "NT", each Table.AddIndexColumn(Table.FromList(Text.ToList([String])), "Id",1,1) ),
ExpandToRows = Table.ExpandTableColumn(AddNestTab, "NT", {"Column1", "Id"}, {"Column1", "Id"}),
AddNewText = Table.AddColumn(ExpandToRows, "Ntxt", each Text.Repeat([Column1],[Id]))[[String],[Ntxt]],
Result = [Table.Group](http://table.group/)(AddNewText, {"String"}, {{"Answer", each Text.Combine([Ntxt], "-"), type text}})
in
Result

[EnterpriseDNA]

LinkedIn Post by:
Luan Rodrigues

let
 Fonte = Tabela1,
 add = Table.AddColumn(Fonte, "Personalizar", each Text.ToList([String])),
 exp = Table.ExpandListColumn(add, "Personalizar"),
 res = Table.Group(exp, {"String"}, {{"Contagem", each 
 [
 a = Table.AddIndexColumn(_, "Ind",1,1),
 b = Text.Combine(Table.AddColumn(a,"Txt", each Text.Repeat([Personalizar],[Ind]))[Txt],"-")
 ][b]
}})
in
 res

[EnterpriseDNA]

LinkedIn Post by
Guillermo Arroyo

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

[EnterpriseDNA]

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

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

[EnterpriseDNA]

LinkedIn post by:
Surendra Reddy

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

[EnterpriseDNA]

LinkedIn Post by:
Quadri Olayinka Atharu

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

[EnterpriseDNA]

LinkedIn Post by:
Paul Reynolds

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

[EnterpriseDNA]

LinkedIn Post by:
Muhammad Rayan S.

=MAP(
 A2:A6,
 LAMBDA(s,
 LET(
 split, MID(s, SEQUENCE(LEN(s)), 1),
 Seq, SEQUENCE(LEN(s)),
 table, HSTACK(split, Seq, REPT(split, Seq)),
 TEXTJOIN("-", , TAKE(table, , -1))
 )
 )
)