Transpose the problem table into result table.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 202
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Transpose Vertical to Horizontal with Power Query
Power Query solution 1 for Transpose Vertical to Horizontal, proposed by Zoran Milokanović:
let
Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]),
P = List.Accumulate(
Source,
{},
(s, c) =>
let
l = List.Last(s, {null})
in
s
& {
List.Transform(
List.Positions(c),
each {l{_}, c{_}, (l{_} ?? 0) + 1}{
List.PositionOf(
{List.Count(List.RemoveNulls(List.Skip(c, _ + 1))) > 0, c{_} = null, true},
true
)
}
)
}
),
S = Table.FromRows(
List.TransformMany(
List.Positions(Source),
each {List.Transform(P{_}, Text.From)},
(i, _) => {Text.Combine(_, ".")} & List.RemoveNulls(Source{i})
),
{"Serial", "Names"}
)
in
SPower Query solution 2 for Transpose Vertical to Horizontal, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FD = Table.FillDown(Source, {"Name1"}),
GR = Table.Group(
FD,
{"Name1"},
{
{
"All",
each
let
a = _,
b = List.Zip(List.Skip(Table.ToColumns(a))),
c = List.Generate(
() => [x = 0, y = 0, z = 0],
each [x] <= List.Count(b),
each [
x = [x] + 1,
y = if b{[x]}{0} <> null then [y] + 1 else [y],
z = if b{[x]}{1} <> null then [z] + 1 else 0
],
each Record.ToList([[y], [z]])
),
d = List.Transform(
c,
each List.Transform(_, (x) => Text.Replace(Text.From(x), "0", ""))
),
e = List.Skip(List.Transform(d, each Text.Combine(List.Select(_, (x) => x <> ""), "."))),
f = List.RemoveNulls(List.Distinct(List.Combine(Table.ToRows(a))))
in
Table.FromColumns({e, f}, {"A", "Names"})
}
}
),
Idx = Table.AddIndexColumn(GR, "Idx", 1, 1)[[All], [Idx]],
Exp = Table.ExpandTableColumn(Idx, "All", Table.ColumnNames(GR[All]{0})),
Sol = Table.AddColumn(
Exp,
"Serial",
each Text.Combine({Text.From([Idx]), if [A] = "" then null else [A]}, ".")
)[[Serial], [Names]]
in
SolPower Query solution 3 for Transpose Vertical to Horizontal, proposed by Eric Laforce:
let
fxNextSerial = (s) =>
let
xHeadTrail = [
p = Text.PositionOf(s, ".", Occurrence.Last),
Head = if p = - 1 then "" else Text.Start(s, p),
Trail = if p = - 1 then s else Text.Middle(s, p + 1)
]
in
xHeadTrail[Head]
& (if xHeadTrail[Head] <> "" then "." else "")
& Text.From(Number.From(xHeadTrail[Trail]) + 1),
Source = Excel.CurrentWorkbook(){[Name = "tData202"]}[Content],
Transform = List.Accumulate(
Table.ToRows(Source),
[i = 0, x = "0", r = {}],
(s, c) =>
let
i = List.PositionOf(c, List.RemoveNulls(c){0}),
x =
if (i > s[i]) then
s[x] & ".1"
else if i = s[i] then
fxNextSerial(s[x])
else
fxNextSerial(Text.Start(s[x], Text.PositionOf(s[x], ".", Occurrence.All){i}))
in
[i = i, x = x, r = s[r] & {{x, c{i}}}]
),
Result = Table.FromRows(Transform[r], {"Serial", "Names"})
in
ResultPower Query solution 4 for Transpose Vertical to Horizontal, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.SelectRows(Table.SelectColumns(Table.Distinct(Source,{"Name1"}),{"Name1"}), each ([Name1] <> null)),
B1 = Table.RenameColumns(Table.AddIndexColumn(A, "S.1", 1, 1, Int64.Type),{{"Name1", "N"}}),
C = Table.Group(Table.FillDown(Source,{"Name1"}), {"Name1"}, {{"Tb", each _, type table [Name1=text, Name2=nullable text, Name3=nullable text]}}),
D = Table.AddColumn(C, "Tb1", each Table.AddIndexColumn(Table.SelectRows([Tb],(each [Name2]<>null)),"S.2",1,1)),
E = Table.SelectRows(Table.ExpandTableColumn(D, "Tb1", {"Name2", "S.2"}, {"Name2", "S.2"}), each ([Name2] <> null)),
F = Table.NestedJoin(E,{"Name1"},B1,{"N"},"C"),
G = Table.ExpandTableColumn(F, "C", {"S.1"}, {"S.1"}),
H = Table.CombineColumns(Table.TransformColumnTypes(G, {{"S.1", type text}, {"S.2", type text}}, "en-US"),{"S.1", "S.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"S"),
B2 = Table.RenameColumns(H,{{"S", "S.1"}, {"Name2", "N"}}),
I = Table.AddColumn(D, "T2", each Table.AddIndexColumn(Table.SelectRows(Table.FillDown([Tb],{"Name2"}),(each [Name3]<>null)),"S.3",1,1)),
Power Query solution 5 for Transpose Vertical to Horizontal, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
Part2:
J = Table.ExpandTableColumn(I, "T2", {"Name2", "Name3", "S.3"}, {"Name2", "Name3", "S.3"}),
K = Table.NestedJoin(J,{"Name2"},B2,{"N"},"C"),
L = Table.ExpandTableColumn(K, "C", {"S.1"}, {"S.1"}),
M = Table.CombineColumns(Table.TransformColumnTypes(L, {{"S.3", type text}}, "en-US"),{"S.1", "S.3"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"S"),
B3 = Table.RenameColumns(M,{{"Name3", "N"}, {"S", "S.1"}}),
N = Table.Combine({B1,B2,B3},{"S.1","N"}),
O = Table.SelectRows(N, each ([N] <> null)),
P = Table.DuplicateColumn(O, "S.1", "S"),
Q = Table.SplitColumn(Table.TransformColumnTypes(P, {{"S", type text}}, "en-US"), "S", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"S.1.1", "S.2", "S.3"}),
R = Table.TransformColumnTypes(Q,{{"S.1.1", Int64.Type}, {"S.2", Int64.Type}, {"S.3", Int64.Type}}),
T = Table.Sort(R,{{"S.1.1", Order.Ascending}, {"S.2", Order.Ascending}, {"S.3", Order.Ascending}}),
U = Table.SelectColumns(T,{"S.1", "N"}),
Sol = Table.RenameColumns(U,{{"S.1", "Serial"}, {"N", "Names"}})
in
Sol
Power Query solution 6 for Transpose Vertical to Horizontal, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group1 = Table.Group(
Table.FillDown(Source, {"Name1", "Name2"}),
{"Name1", "Name2"},
{{"Count", each Table.AddIndexColumn(_, "Index", 0, 1, type text)}}
),
Group2 = Table.Group(
Group1,
{"Name1"},
{{"Count", each Table.AddIndexColumn(_, "Index", 0, 1, type text)}}
),
Index = Table.AddIndexColumn(Group2, "Index", 1, 1, type text),
Exp1 = Table.ExpandTableColumn(
Index,
"Count",
{"Name2", "Count", "Index"},
{"Name2", "Count.1", "Index.1"}
),
Exp2 = Table.ExpandTableColumn(Exp1, "Count.1", {"Name3", "Index"}, {"Name3", "Index.2"}),
Serial = Table.AddColumn(
Exp2,
"Serial",
each Text.Replace(
Text.From([Index]) & "." & Text.From([Index.1]) & "." & Text.From([Index.2]),
".0",
""
)
),
Names = Table.AddColumn(
Serial,
"Names",
each if [Index.2] = 0 then if [Index.1] = 0 then [Name1] else [Name2] else [Name3]
),
Result = Table.SelectColumns(Names, {"Serial", "Names"})
in
ResultPower Query solution 7 for Transpose Vertical to Horizontal, proposed by Ahmed Ariem:
let
Source = Excel.CurrentWorkbook(){[Name = "tbl"]}[Content],
Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
FillDown = Table.FillDown(Headers, {"Name1"}),
Group = Table.Group(
FillDown,
{"Name1"},
{
{
"Count1",
(x) =>
[
a = Table.FillDown(x, {"Name2"}),
b = Table.Group(
a,
{"Name1", "Name2"},
{{"Count2", (z) => Table.AddIndexColumn(z, "idx2", 0, 1)}}
),
c = Table.AddIndexColumn(b, "idx1", 0, 1)
][c]
}
}
),
IndexColumn = Table.AddIndexColumn(Group, "idx0", 1, 1, Int64.Type),
Expand1 = Table.ExpandTableColumn(IndexColumn, "Count1", {"Name2", "Count2", "idx1"}),
Expand2 = Table.ExpandTableColumn(Expand1, "Count2", {"Name3", "idx2"}),
Comb = Table.CombineColumns(
Expand2,
{"idx0", "idx1", "idx2"},
(x) => Text.Combine(List.Transform(x, Text.From), "."),
"Serial"
),
Rep = Table.ReplaceValue(Comb, ".0", "", Replacer.ReplaceText, {"Serial"}),
AddCol = Table.AddColumn(
Rep,
"Names",
each [a = List.RemoveLastN(Record.ToList(_), 1), b = a{Text.Length(Text.Select([Serial], "."))}][
b
]
)[[Names], [Serial]]
in
AddColPower Query solution 8 for Transpose Vertical to Horizontal, proposed by Joevan Bedico:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer =
let
x = List.Transform(
Table.ToRows(Source),
each List.Combine(List.Select(List.Zip({_, {10000, 100, 1}}), each _{0} <> null))
)
in
Table.FromColumns(
{
List.Transform(
List.Accumulate(
List.Transform(x, List.Last),
{},
(s, c) => s & {List.Sum({Number.RoundDown(List.Last(s) / c) * c, c})}
),
each Text.Combine(
List.Select(
List.Transform(
List.Split(Text.ToList(Number.ToText(_, "000000")), 2),
each Number.ToText(Number.From(Text.Combine(_)), "##")
),
each _ <> ""
),
"."
)
)
}
& {List.Transform(x, List.First)},
{"Serial", "Names"}
)
in
AnswerPower Query solution 9 for Transpose Vertical to Horizontal, proposed by Alejandra Horvath CPA, CGA:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
v = Table.Group,
w = Table.AddColumn,
x = Table.AddIndexColumn,
y = Table.ExpandTableColumn,
z = Table.TransformColumns,
G1 = v(
Source,
{"Name1"},
{
{
"A",
each
let
a = Table.FillDown(_, {"Name2"}),
c = v(a, {"Name2"}, {{"B", each _}}),
e = x(c, "I2"),
f = y(e, "B", {"Name3"})
in
e
}
},
0,
(x, y) => Number.From(y[Name1] <> null)
),
I1 = x(G1, "I1", 1),
EA = y(I1, "A", {"Name2", "B", "I2"}, {"Name2", "B", "I2"}),
T = z(
EA,
{
"B",
each
if List.NonNullCount(Table.Column(_, "Name3")) <> 0 then
x(_, "I3")
else
w(_, "I3", each null)
}
),
EB = y(T, "B", {"Name3", "I3"}, {"Name3", "I3"}),
CT = Table.TransformColumnTypes(EB, {{"I1", type text}, {"I2", type text}, {"I3", type text}}),
RV = Table.ReplaceValue(CT, "0", null, Replacer.ReplaceValue, {"I3", "I2", "I1"}),
MI = Table.CombineColumns(RV, {"I1", "I2", "I3"}, Combiner.CombineTextByDelimiter("."), "Serial"),
TR = z(MI, {{"Serial", each Text.TrimEnd(_, ".")}}),
S = w(
TR,
"Names",
each if [Name2] = null then [Name1] else if [Name3] = null then [Name2] else [Name3]
)[[Serial], [Names]]
in
SSolving the challenge of Transpose Vertical to Horizontal with Excel
Excel solution 1 for Transpose Vertical to Horizontal, proposed by Bo Rydobon 🇹🇭:
=HSTACK(REGEXREPLACE(SCAN(0,
C2:C18,
LAMBDA(a,
v,
LET(n,
MATCH(
"*",
TAKE(
A18:v,
1
),
),
LEFT(
a,
n-1
)&(0&MID(
a,
n,
1
))+1))),
"d(?=.)",
"$0."),
BYROW(
A2:C18,
CONCAT
))Excel solution 2 for Transpose Vertical to Horizontal, proposed by Bo Rydobon 🇹🇭:
=HSTACK(
SCAN(
0,
C2:C18,
LAMBDA(
a,
v,
LET(
d,
".",
n,
MATCH(
"*",
TAKE(
A18:v,
1
),
),
TEXTJOIN(
d,
,
IF(
n>1,
TEXTBEFORE(
a&d,
d,
n-1
),
""
),
IFERROR(
INDEX(
TEXTSPLIT(
a,
d
),
n
),
0
)+1
)
)
)
),
BYROW(
A2:C18,
CONCAT
)
)Excel solution 3 for Transpose Vertical to Horizontal, proposed by Bo Rydobon 🇹🇭:
=LET(z,
A2:C18,
HSTACK(MID(REGEXREPLACE(SCAN(0,
SEQUENCE(
ROWS(
z
)
),
LAMBDA(a,
i,
LET(
b,
IF(
INDEX(
z,
i,
1
)>0,
FLOOR(
a,
100
)+100,
a
),
IF(
INDEX(
z,
i,
2
)>0,
FLOOR(
b,
10
)+10,
b
)+(INDEX(
z,
i,
3
)>0)))),
"0|(d)",
"${1:+.$1}"),
2,
9),
BYROW(
z,
CONCAT
)))Excel solution 4 for Transpose Vertical to Horizontal, proposed by محمد حلمي:
=LET(
r,
SCAN(
0,
A2:A18,
LAMBDA(
a,
v,
LET(
u,
TAKE(
C1:v,
-2
),
i,
MATCH(
"z",
DROP(
u,
1
)
)-MATCH(
"z",
TAKE(
u,
1
)
),
IFS(
v>0,
LEFT(
a
),
i>0,
a&0,
i<0,
LEFT(
a,
LEN(
a
)-2
)&MID(
a,
2,
1
),
1,
a
)+1
)
)
),
HSTACK(
TEXT(
r,
0&REPT(
".0",
LEN(
r
)-1
)
),
TOCOL(
A2:C18,
1
)
)
)Excel solution 5 for Transpose Vertical to Horizontal, proposed by محمد حلمي:
=HSTACK(
SCAN(0,
A2:A18,
LAMBDA(a,
v,
LET(
u,
TAKE(
C1:v,
-2
),
k,
".",
x,
SUM(((DROP(
u,
1
)>0)-(TAKE(
u,
1
)>0))*SEQUENCE(
,
3
)),
IFS(
v>0,
LEFT(
a
)+1,
x>0,
a&k&1,
x<0,
TEXTBEFORE(
a,
k,
-2
)&k&MID(
a,
3,
1
)+1,
1,
TEXTBEFORE(
a,
k,
-1
)&k&TEXTAFTER(
a,
k,
-1
)+1
)))),
TOCOL(
A2:C18,
1
))
3 = COLUMNS(
A1:C1
)
TAKE(
u,
1
) >>> P&revious_row
To know the location of the name
DROP(
u,
1
) >>> Current line
To know the location of the name
IF v = 1 this means New basic level
(left(
Previous value
)+1)
IF x>0 this means new level accidental (the Previous value &".1")
IF X=i this means we in the same level (Previous value + 1) (1 in ifs)
IF X<0 this means we in new level linear (Previous value Excluding the last value + 1 (5.1.1 to 5.2)Excel solution 6 for Transpose Vertical to Horizontal, proposed by Kris Jaganah:
=LET(a,
A2:C18,
b,
1/(10^(BYROW(
MAP(
a,
LAMBDA(
x,
IF(
x<>"",
COLUMN(
x
),
0
)
)
),
SUM
)-1)),
c,
SCAN(0,
b,
LAMBDA(x,
y,
IFS(INT(
y
)=y,
INT(
x
)+1,
(y=0.1)*(LEN(
MOD(
x,
1
)
)>1),
ROUNDDOWN(
x,
1
)+y,
y=0.1,
x+y,
y=0.01,
x+y))),
HSTACK(
IF(
LEN(
c
)>3,
LEFT(
c,
3
)&"."&MID(
c,
4,
1
),
c&""
),
TOCOL(
a,
3
)
))Excel solution 7 for Transpose Vertical to Horizontal, proposed by Julian Poeltl:
=LET(
T,
A2:C18,
N,
BYROW(
T,
LAMBDA(
A,
CONCAT(
A
)
)
),
O,
SCAN(
0,
TAKE(
T,
,
1
),
LAMBDA(
A,
B,
IF(
B<>"",
A+1,
A
)
)
),
S,
LAMBDA(
A,
SCAN(
0,
A,
LAMBDA(
A,
B,
IF(
B<>"",
A+1,
)
)
)
),
L,
S(
TAKE(
T,
,
-1
)
),
M,
IF(
TAKE(
T,
,
1
)<>"",
"X",
CHOOSECOLS(
T,
2
)
),
MM,
SCAN(
0,
M,
LAMBDA(
A,
B,
IF(
B=0,
A,
IF(
B<>"X",
A+1,
0
)
)
)
),
AR,
HSTACK(
O,
MM,
L
),
VSTACK(
HSTACK(
"Serial",
"Names"
),
HSTACK(
BYROW(
IF(
AR=0,
"",
AR
),
LAMBDA(
A,
TEXTJOIN(
".",
1,
A
)
)
),
N
)
)
)Excel solution 8 for Transpose Vertical to Horizontal, proposed by Anshu Bantra:
=LET(
lvl1_,
SCAN(
0,
A2:A18,
LAMBDA(
ini,
ro,
ini+COUNTA(
ro
)
)
),
lvl2_,
SCAN(
0,
B2:B18,
LAMBDA(
ini,
ro,
IFS(
COUNTA(
ro
)>0,
ini+COUNTA(
ro
),
COUNTA(
OFFSET(
ro,
0,
1
)
)>0,
ini,
TRUE,
0
)
)
),
lvl3_,
SCAN(
0,
C2:C18,
LAMBDA(
ini,
ro,
IF(
COUNTA(
ro
)>0,
ini+COUNTA(
ro
),
0
)
)
),
arr_,
HSTACK(
lvl1_,
lvl2_,
lvl3_
),
lvls_,
BYROW(
SUBSTITUTE(
arr_,
0,
""
),
LAMBDA(
ro,
TEXTJOIN(
".",
,
ro
)
)
),
names_,
BYROW(
A2:C18,
LAMBDA(
ro,
CONCAT(
ro
)
)
),
VSTACK(
{"Serial",
"Names"},
HSTACK(
lvls_,
names_
)
)
)Excel solution 9 for Transpose Vertical to Horizontal, proposed by JvdV -:
=HSTACK(
LET(
f,
LAMBDA(
s,
t,
x,
y,
n,
TEXT(
SUM(
N(
XLOOKUP(
"*",
s:x,
t:y,
,
2,
n
):y<>0
)
),
"[>0]-0;"
)
),
MAP(
A2:A18,
B2:B18,
C2:C18,
LAMBDA(
a,
b,
c,
-f(
A2,
A2,
a,
a,
1
)&f(
A2,
B2,
a,
b,
-1
)&IF(
b&c="",
"",
f(
B2,
C2,
b,
c,
-1
)
)
)
)
),
TOCOL(
A2:C18,
1
)
)Excel solution 10 for Transpose Vertical to Horizontal, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=SEQUENCE(
COUNTA(
A2:A18
),
,
1,
1
)
=HSTACK(SCAN(
0,
IF(
A2:A18<>"",
1,
0
),
LAMBDA(
i,
j,
SUM(
i,
j
)
)
)&"."&LET(
i,
IF(
C2:C18<>"",
1,
BYROW(
VALUE(
TRANSPOSE(
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
MAP(
D2#,
LAMBDA(
d,
TEXTJOIN(
",",
,
LET(
c,
COUNTIF(
d,
SCAN(
0,
IF(
A2:A18<>"",
1,
0
),
LAMBDA(
i,
j,
SUM(
i,
j
)
)
)
),
IF(
c=0,
0,
SCAN(
0,
c,
LAMBDA(
a,
b,
SUM(
a,
b
)
)
)-1
)
)
)&"/"
)
)
),
",",
"/"
),
-1
)
)
),
LAMBDA(
e,
SUM(
e
)
)
)
),
IF(
i=0,
"",
i
)
)&"."&LET(i,
BYROW(VALUE(TRANSPOSE(DROP(TEXTSPLIT(TEXTJOIN(,
,
MAP(D2#,
LAMBDA(v,
TEXTJOIN(",",
,
LET(j,
IF(
IF(
C2:C18="",
0,
1
)=1,
COUNTIF(
v,
SCAN(
0,
IF(
A2:A18<>"",
1,
0
),
LAMBDA(
i,
j,
SUM(
i,
j
)
)
)
),
0
),
IF(j=0,
0,
SCAN(0,
j,
LAMBDA(a,
b,
(SUM(
a,
b
)))))))&"/"))),
",",
"/"),
-1))),
LAMBDA(
a,
SUM(
a
)
)),
IF(
i=0,
"",
i
)),
LET(
a,
TOCOL(
A2:C18
),
FILTER(
a,
a<>0
)
))Excel solution 11 for Transpose Vertical to Horizontal, proposed by El Badlis Mohd Marzudin:
=LET(
s,
SCAN(
0,
A2:A18,
LAMBDA(
a,
b,
IFS(
b<>"",
a+1,
1,
a
)
)
),
d,
SCAN(
0,
B2:B18,
LAMBDA(
a,
b,
IFS(
b<>"",
a+1,
INDEX(
C2:C18,
ROWS(
B2:b
)
)<>"",
a,
b="",
0
)
)
),
t,
SCAN(
0,
C2:C18,
LAMBDA(
a,
b,
IFS(
b<>"",
a+1,
1,
0
)
)
),
HSTACK(
MAP(
s,
d,
t,
LAMBDA(
a,
b,
c,
CONCAT(
a,
IF(
b<>0,
"."&b,
""
),
IF(
c<>0,
"."&c,
""
)
)
)
),
TOCOL(
A2:C18,
3
)
)
)Solving the challenge of Transpose Vertical to Horizontal with Python
Python solution 1 for Transpose Vertical to Horizontal, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_202.xlsx"
input = pd.read_excel(path, usecols="A:C")
test = pd.read_excel(path, usecols="E:F")
result = input.copy()
result["L1"] = result["Name1"].notna().cumsum()
result["L2"] = result.groupby("L1")["Name2"].transform(lambda x: x.notna().cumsum())
result["L3"] = result.groupby(["L1", "L2"])["Name3"].transform(lambda x: x.notna().cumsum())
result[["L1", "L2", "L3"]] = result[["L1", "L2", "L3"]].astype("Int64").astype(str).replace("0", pd.NA)
result["Names"] = result["Name3"].combine_first(result["Name2"]).combine_first(result["Name1"])
result["Serial"] = result[["L1", "L2", "L3"]].apply(lambda x: ".".join(x.dropna()), axis=1)
result = result[["Serial", "Names"]]
print(result.equals(test)) # True
Solving the challenge of Transpose Vertical to Horizontal with Python in Excel
Python in Excel solution 1 for Transpose Vertical to Horizontal, proposed by Abdallah Ally:
df = xl("A1:C18", headers=True)
# Perform data munging
name1 = df['Name1'].replace(np.nan, '')
df['Name1'] = df['Name1'].ffill()
df['Level1'] = (df['Name1'] != df['Name1'].shift(1)).cumsum()
df['Level2'] = df.dropna(subset='Name2')[['Name1', 'Name2']].groupby('Name1').transform('cumcount') + 1
df['Level2'] = df['Level2'].ffill()
df['Level3'] = pd.notna(df['Name3'])
df['Level3'] = df[['Name1', 'Name3', 'Level3']].dropna().groupby('Name1')['Level3'].transform('cumsum')
df = df.fillna(0)
df[['Level1', 'Level2', 'Level3']] = df[['Level1', 'Level2', 'Level3']].astype(int)
df = df.astype(str).replace('0', '')
df['Level2'] = df['Level2'] * ((df['Name2'] != '') + (df['Name3'] != ''))
df['Name1'] = name1
df['Serial'] = df.apply(lambda x: '.'.join([y for y in x[3:] if y]), axis=1)
df['Names'] = df.apply(lambda x: '.'.join([y for y in x[:3] if y]), axis=1)
df = df[['Serial', 'Names']] # Final report as expected
df
Python in Excel solution 2 for Transpose Vertical to Horizontal, proposed by Anshu Bantra:
df = xl("A1:C18", headers=True)
lst, names = [], []
lvl1, lvl2, lvl3 = 0,0,0
for _ in df.itertuples():
n1, n2, n3 = ('','','')
if _.Name1:
lvl1 += 1
n1=_.Name1
if _.Name2:
lvl2 += 1
n2=_.Name2
elif _.Name3:
lvl2
else:
lvl2 = 0
if _.Name3:
lvl3 += 1
n3=_.Name3
else:
lvl3 = 0
lst.append(
('.'.join([str(lvl1), str(lvl2), str(lvl3)]).replace('.0',''),
(n1+n2+n3)
)
)
lst
Solving the challenge of Transpose Vertical to Horizontal with R
R solution 1 for Transpose Vertical to Horizontal, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_202.xlsx"
input = read_excel(path, range = "A1:C18")
test = read_excel(path, range = "E1:F18")
result = input %>%
mutate(L1 = cumsum(!is.na(Name1))) %>%
mutate(L2 = cumsum(!is.na(Name2)), .by = L1) %>%
mutate(L3 = cumsum(!is.na(Name3)), .by = c(L1, L2)) %>%
mutate(across(starts_with("L"), ~ ifelse(. == 0, NA, .))) %>%
mutate(across(everything(), ~ as.character(.))) %>%
rowwise() %>%
mutate(Names = coalesce(Name3, Name2, Name1),
Serial = case_when(
!is.na(L3) ~ paste(L1, L2, L3, sep = "."),
!is.na(L2) ~ paste(L1,L2, sep = "."),
!is.na(L1) ~ L1
)) %>%
ungroup() %>%
select(Serial, Names)
identical(result, test)
# [1] TRUE
R solution 2 for Transpose Vertical to Horizontal, proposed by Anil Kumar Goyal:
library(tidyverse)
library(readxl)
df <- read_excel("PQ/PQ_Challenge_202.xlsx",
range = cell_cols("A:C"))
df |>
pivot_longer(everything(),
names_to = c(".value", "L"),
names_pattern = "(\D*)(\d)",
values_drop_na = TRUE) |>
mutate(new1 = cumsum(L == 1)) |>
mutate(new2 = cumsum(L == 2), .by = new1) |>
mutate(new3 = cumsum(L == 3), .by = c(new1, new2)) |>
mutate(Serial = str_c(new1, new2, new3, sep = "."),
Serial = str_remove_all(Serial, regex("\.?0?\.?0?$")),
Names = Name,
.keep = "none")
Solving the challenge of Transpose Vertical to Horizontal with Excel VBA
Excel VBA solution 1 for Transpose Vertical to Horizontal, proposed by Ümit Barış Köse, MSc:
Sub C202()
Dim Count1 As Integer, Count2 As Integer, Count3 As Integer
Dim i1 As Integer
Dim c As Range
Dim n As String
Count1 = 0
Count2 = 0
Count3 = 0
For i1 = 2 To 18
Set c = Range("A" & i1 & ":C" & i1).Find(What:="*", LookIn:=xlValues)
Select Case c.Column
Case 1
Count1 = Count1 + 1
Count2 = 0
Count3 = 0
n = CStr(Count1)
Case 2
Count2 = Count2 + 1
Count3 = 0
n = CStr(Count1) & "." & CStr(Count2)
Case 3
Count3 = Count3 + 1
n = CStr(Count1) & "." & CStr(Count2) & "." & CStr(Count3)
End Select
Cells(i1, 8).Value = n
Cells(i1, 9).Value = c.Value
End If
Next i1
End Sub
&