Create the triangle on the basis of given input.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 38
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Create the triangle on input with Power Query
Power Query solution 1 for Create the triangle on input, proposed by Rick de Groot:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
Letters = #"Changed Type"[Data],
Result = List.Generate(
() => [ x = Letters{0}, Nr = 0 ],
each [Nr] < List.Count( Letters ),
each [ x = Text.Combine( { Letters{ [Nr]+1}, [x], Letters{ [Nr]+1 } }, " " ), Nr = [Nr] + 1 ],
each [x] )
in
Result
Happy Holidays!
Power Query solution 2 for Create the triangle on input, proposed by Luan Rodrigues:
let
Fonte = Data,
Result = Table.AddColumn(
Fonte,
"natal",
each [
a = {"A" .. [Data]},
b = Text.Combine(List.Reverse(a)),
c = Text.Combine(List.RemoveFirstN(a, 1)),
d = b & c,
e = Text.Combine(
Splitter.SplitTextByCharacterTransition({"a" .. "z", "A" .. "Z"}, {"A" .. "Z"})(d),
" "
)
][e]
)[[natal]]
in
ResultPower Query solution 3 for Create the triangle on input, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
MarkerIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
MaxCount = List.Max(MarkerIndex[Index]),
PyramidGenerate = Table.AddColumn(
MarkerIndex,
"Triangle",
each List.Generate(
() => [x = 0, y = {[Data]}{0}],
each [x] <= MaxCount,
each [x = [x] + 1, y = Text.Combine({MarkerIndex[Data]{x}, [y], MarkerIndex[Data]{x}}, "")],
each [y]
)
),
SelectTriangle = Table.SelectColumns(PyramidGenerate, "Triangle"){0},
ToTable = Table.RenameColumns(
Table.FromList(
SelectTriangle[Triangle],
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
{"Column1", "Triangle"}
)
in
ToTablePower Query solution 4 for Create the triangle on input, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "LetterTriangle"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Triangle",
each Text.Combine(List.RemoveLastN(List.Reverse({"A" .. [Data]})) & {"A" .. [Data]}, " ")
)[[Triangle]]
in
#"Added Custom"
Merry Christmas!
Power Query solution 5 for Create the triangle on input, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = List.Accumulate(
Source[Data],
{},
(state, current) =>
if List.IsEmpty(state) then
state & {current}
else
state & {current & " " & List.Last(state) & " " & current}
)
in
ResultPower Query solution 6 for Create the triangle on input, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each List.FirstN(Source[Data], 1 + List.PositionOf(Source[Data], [Data]))
),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Triangle",
each List.Reverse([Custom]) & List.Skip([Custom])
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1", {"Custom", "Data"}),
#"Extracted Values" = Table.TransformColumns(
#"Removed Columns",
{"Triangle", each Text.Combine(List.Transform(_, Text.From))}
)
in
#"Extracted Values"Power Query solution 7 for Create the triangle on input, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Base = List.Transform( {1..Table.RowCount(Source)}, each List.Repeat( {List.Reverse(Source[Data]){_-1}}, _)),
Triangle = Table.ReverseRows(
Table.FromColumns( List.RemoveLastN( Base, 1) & List.Reverse( Base)
)
)
in
Triangle
or for a result in one column
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Base = List.Transform( {1..Table.RowCount(Source)}, each List.Repeat( {List.Reverse(Source[Data]){_-1}}, _)),
Triangle = Table.AddColumn( Table.ReverseRows(
Table.FromColumns( List.RemoveLastN( Base, 1) & List.Reverse( Base)
)), "Tree", each Text.Combine( Record.ToList(_))
)[[Tree]]
in
Triangle
Power Query solution 8 for Create the triangle on input, proposed by Thomas DUCROQUETZ:
letters aren't consecutive, it just considers the order, probably a better way to achieve this.
let
Source = YourRawData,
ListOfLetters = Source[Data],
LettersAndPos = List.Zip({ListOfLetters,List.Positions(ListOfLetters)}),
AllLettersSym = List.Reverse( ListOfLetters ) & List.Select( ListOfLetters,each _ <> Source[Data]{0} ),
Triangle =
Table.AddColumn(
Source,
"Triangle",
each let
currentLetter = [Data],
ListLetters = Table.FirstN(Source,List.Select(LettersAndPos,each _{0} = currentLetter){0}{1}+1)[Data],
ListComplete = List.Transform(AllLettersSym,each if List.Contains(ListLetters,_) then _ else " ")
in Text.Combine(ListComplete," "),
type text
)[[Triangle]]
in
Triangle
Power Query solution 9 for Create the triangle on input, proposed by Luiz K.:
let
// Step 1: Create a function that generates a list of integers
generateList = (n) => List.Generate(() => 1, (i) => i < n, (i) => i + 1),
// Step 2: Create a function that generates a list of lists
generateTriangle = (n) => List.Accumulate(generateList(n), {}, (prev, cur) => prev & {cur}),
// Step 3: Convert the list of lists into a table
triangle = Table.FromList(
generateTriangle(5),
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
)
in
triangleSolving the challenge of Create the triangle on input with Excel
Excel solution 1 for Create the triangle on input, proposed by Rick Rothstein:
=SCAN(
A2,
A2:A9,
LAMBDA(
a,
x,
IF(
ROW(
x
)=2,
a,
x&" "&a&" "&x
)
)
)Excel solution 2 for Create the triangle on input, proposed by 🇰🇷 Taeyong Shin:
=SCAN(
,
A2:A9,
LAMBDA(
a,
v,
v&" "&a&" "&v
)
)Excel solution 3 for Create the triangle on input, proposed by Kris Jaganah:
=VSTACK(
A2,
SCAN(
A2,
A3:A9,
LAMBDA(
x,
y,
REPT(
y,
1
)&" "&x&" "&REPT(
y,
1
)
)
)
)Excel solution 4 for Create the triangle on input, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
A2:A9,
_c,
SCAN(
TAKE(
_d,
1
),
_d,
LAMBDA(
a,
b,
TEXTJOIN(
" ",
1,
b,
a,
b
)
)
),
_r,
SUBSTITUTE(
_c,
" A A",
""
),
_r
)
Way 2:
=LET(
_d,
A2:A9,
_r,
REDUCE(
TAKE(
_d,
1
),
DROP(
_d,
1
),
LAMBDA(
a,
b,
VSTACK(
a,
TEXTJOIN(
" ",
1,
b,
TAKE(
a,
-1
),
b
)
)
)
),
_r
)
Way 3:
=LET(
_d,
A2:A9,
_f,
TAKE(
_d,
1
),
_c,
SCAN(
_f,
DROP(
_d,
1
),
LAMBDA(
a,
b,
TEXTJOIN(
" ",
1,
b,
a,
b
)
)
),
_r,
VSTACK(
_f,
_c
),
_r
)Excel solution 5 for Create the triangle on input, proposed by Stefan Olsson:
=SCAN(
"",
A2:A9,
LAMBDA(
_s,
_a,
IF(
_s<>"",
TEXTJOIN(
" ",
TRUE,
_a,
_s,
_a
),
TEXTJOIN(
" ",
TRUE,
_s,
_a,
_s
)
)
)
)Excel solution 6 for Create the triangle on input, proposed by Abhishek Kumar Jain:
=TRIM(
IF(
A2="A",
"A",
A2&" "&IFERROR(
TEXTJOIN(
" ",
TRUE,
CHAR(
SEQUENCE(
,
CODE(
A2
)-66,
CODE(
A2
)-1,
-1
)
)
),
""
)&" "&TEXTJOIN(
" ",
TRUE,
CHAR(
SEQUENCE(
,
CODE(
A2
)-65+1,
65,
1
)
)
)
)
)Excel solution 7 for Create the triangle on input, proposed by Abhishek Kumar Jain:
=IF(A2="A",A2,A2&" "&H1&" "&A2)Solving the challenge of Create the triangle on input with Python
Python solution 1 for Create the triangle on input, proposed by Igor Perković:
# Source
data = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H']
# Processing
tmp = ''
for i in range(len(data)):
if i:
tmp = data[i] + tmp + data[i]
else:
tmp = data[i]
print(tmp.center(len(data)*2))
Python solution 2 for Create the triangle on input, proposed by Matteo Castagna:
Seriously? XL?
for each line:
N = whatever
print(paste0(paste(LETTERS[1:N], collapse = ""), paste(LETTERS[(N-1):1], collapse="")))
Solving the challenge of Create the triangle on input with SQL
SQL solution 1 for Create the triangle on input, proposed by Zoran Milokanović:
WITH
DATA_PREPARATION
AS
(
SELECT
ROW_NUMBER() OVER () AS ORDINAL_NUMBER
,COUNT(*) OVER () TOTAL_INPUT
,D.INPUT
FROM DATA D
)
SELECT
FROM
(
SELECT
LW.ORDINAL_NUMBER
,C.INPUT
,(C.ORDINAL_NUMBER - 1) * -1 AS SORT
,C.TOTAL_INPUT
FROM DATA_PREPARATION LW
JOIN DATA_PREPARATION C ON
LW.ORDINAL_NUMBER >= C.ORDINAL_NUMBER
UNION
SELECT
RW.ORDINAL_NUMBER
,C.INPUT
,(C.ORDINAL_NUMBER - 1) AS SORT
,C.TOTAL_INPUT
FROM DATA_PREPARATION RW
JOIN DATA_PREPARATION C ON
RW.ORDINAL_NUMBER >= C.ORDINAL_NUMBER
ORDER BY
1, 3
) F
GROUP BY
F.TOTAL_INPUT
,F.ORDINAL_NUMBER
ORDER BY
F.ORDINAL_NUMBER
;
&&&