Transpose problem table into result table. Best student will be that student who has scored the highest marks.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 249
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Transpose problem table into result with Power Query
Power Query solution 1 for Transpose problem table into result, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddIndexColumn(A, "Id", 1),
C = Table.AddColumn(
B,
"Nr",
each "Student" & Text.From([Id] - List.PositionOf(B[Class], [Class]))
),
D = Table.AddColumn(
C,
"Max",
each
if List.Max(Table.SelectRows(C, (x) => x[Class] = [Class])[Marks]) = [Marks] then
[Student]
else
null
),
E = D[[Class], [Student], [Nr]],
F = Table.Pivot(E, List.Distinct(E[Nr]), "Nr", "Student"),
G = Table.AddColumn(
F,
"Best Student",
each Text.Combine(Table.SelectRows(D, (x) => x[Class] = [Class])[Max], ", ")
)
in
GPower Query solution 2 for Transpose problem table into result, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Class",
{
{"A", each Table.Transpose([[Student]])},
{
"Best Student",
each Text.Combine(
Table.MaxN(_, "Marks", (f) => f[Marks] = List.Max([Marks]))[Student],
", "
)
}
}
),
Cols = Table.ColumnNames(Table.Combine(Group[A])),
Return = Table.ExpandTableColumn(
Group,
"A",
Cols,
List.ReplaceValue(Cols, "Column", "Student", Replacer.ReplaceText)
)
in
ReturnPower Query solution 3 for Transpose problem table into result, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Source,
{"Class"},
{
{
"A",
each
let
a = _,
b = a[Student],
c = Table.FromRows(
{b},
List.Transform({1 .. List.Count(b)}, each "Student" & Text.From(_))
)
in
c
},
{
"Best Student",
each
let
a = _,
b = Table.SelectRows(a, each [Marks] = List.Max(a[Marks]))[Student]
in
Text.Combine(b, ", ")
}
}
),
Sol = Table.ExpandTableColumn(Grp, "A", Table.ColumnNames(Table.Combine(Grp[A])))
in
SolPower Query solution 4 for Transpose problem table into result, proposed by Luan Rodrigues:
let
Fonte = Table.Group(
Tabela1,
{"Class"},
{
{
"tab",
each
let
a = Table.TransformColumns(
Table.AddIndexColumn(_, "Ind", 1, 1),
{"Ind", (x) => "Student" & Text.From(x)}
),
b = Table.RemoveColumns(a, {"Marks"}),
c = Table.Pivot(b, List.Distinct(a[Ind]), "Ind", "Student")
in
c
},
{
"Best Student",
each Text.Combine(Table.SelectRows(_, (y) => y[Marks] = List.Max(_[Marks]))[Student], ", ")
}
}
)[[tab], [Best Student]],
exp = Table.ExpandTableColumn(Fonte, "tab", Table.ColumnNames(Table.Combine(Fonte[tab])))
in
expPower Query solution 5 for Transpose problem table into result, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GroupByClass = Table.Group(
Source,
{"Class"},
{{"Student", each Text.Combine(_[Student], ",")}, {"MaxMarks", each List.Max([Marks])}}
),
ExtractBest = Table.AddColumn(
GroupByClass,
"BestStudent",
each
let
theMax = [MaxMarks],
theClass = [Class]
in
Text.Combine(
Table.SelectRows(Source, each ([Marks] = theMax) and ([Class] = theClass))[Student],
","
)
),
SplitStudentList = Table.SplitColumn(
ExtractBest,
"Student",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"Student.1", "Student.2", "Student.3"}
),
RemoveOtherCols = Table.RemoveColumns(SplitStudentList, {"MaxMarks"})
in
RemoveOtherColsPower Query solution 6 for Transpose problem table into result, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
"Class",
{
{"Student", each Table.FromRows({[Student]})},
{
"Best Student",
each Text.Combine(Table.SelectRows(_, (x) => x[Marks] = List.Max([Marks]))[Student], ", ")
}
}
),
ColNames = Table.ColumnNames(Table.Combine(Group[Student])),
Expand = Table.ExpandTableColumn(Group, "Student", ColNames),
Result = Table.TransformColumnNames(Expand, each Text.Replace(_, "Column", "Student"))
in
ResultPower Query solution 7 for Transpose problem table into result, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData249"]}[Content],
Group = Table.Group(
Source,
"Class",
{
"G",
(t) =>
let
_CN = List.Transform({1 .. Table.RowCount(t)}, each "Student" & Text.From(_))
& {"Best Student"},
_BS = Text.Combine(
Table.SelectRows(t, each [Marks] = List.Sort(t[Marks], Order.Descending){0})[Student],
", "
)
in
Table.FromRows({t[Student] & {_BS}}, _CN)
}
),
CN = List.Max(
List.Transform(Group[G], Table.ColumnNames),
1,
(x, y) => Number.From(List.Count(x) > List.Count(y))
),
Expand = Table.ExpandTableColumn(Group, "G", CN)
in
ExpandPower Query solution 8 for Transpose problem table into result, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, "Class", {{"S", each [Student]}, {"B", F}}),
F = each Text.Combine(Table.SelectRows(_, (x) => x[Marks] = List.Max([Marks]))[Student], ", "),
Cols = {Group[Class]} & List.Zip(Group[S]) & {Group[B]},
ColNames = {"Class"}
& List.Transform({1 .. List.Count(Cols) - 2}, each "Student" & Text.From(_))
& {"Best Student"},
Res = Table.FromColumns(Cols, ColNames)
in
ResPower Query solution 9 for Transpose problem table into result, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.Group(
S,
{"Class"},
{{"T", each Table.AddIndexColumn(Table.Sort(_, {"Marks", Order.Descending}), "St", 1, 1)}}
),
B = Table.Combine(A[T]),
C = Table.TransformColumns(B, {{"St", each "Student " & Text.From(_, "en-US"), type text}}),
D = Table.RemoveColumns(C, {"Marks"}),
E = Table.Pivot(D, List.Distinct(D[St]), "St", "Student"),
F = Table.Group(
S,
{"Class"},
{
{
"Best Student",
each Text.Combine(
Table.SelectRows(
Table.AddRankColumn(_, "Rank", {"Marks", Order.Descending}, [RankKind = 0]),
each [Rank] = 1
)[Student],
","
)
}
}
),
G = Table.NestedJoin(E, {"Class"}, F, {"Class"}, "T"),
H = Table.ExpandTableColumn(G, "T", {"Best Student"}, {"Best Student"})
in
HPower Query solution 10 for Transpose problem table into result, proposed by Peter Krkos:
let
Transformed = Table.Combine(
Table.Group(
Source,
{"Class"},
{
{
"T",
each Table.FromRows(
{
{_{0}[Class]}
& [Student]
& {
Text.Combine(
Table.MaxN(_, {"Marks"}, (x) => x[Marks] = List.Max([Marks]))[Student],
", "
)
}
},
{"Class"}
& List.Transform({1 .. List.Count([Student])}, (x) => "Student" & Text.From(x))
& {"Best Student"}
),
type table
}
}
)[T]
),
ReorderedColumns = Table.ReorderColumns(
Transformed,
{"Class"}
& List.Select(Table.ColumnNames(Transformed), each Text.StartsWith(_, "Student"))
& {"Best Student"}
)
in
ReorderedColumnsPower Query solution 11 for Transpose problem table into result, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(
Source,
{"Class"},
{
{
"Students",
each
let
a = _[Student],
b = List.Transform({1 .. List.Count(a)}, each "Student" & Text.From(_)),
c = Table.FromRows({a}, b)
in
c
},
{
"Best Student",
each
let
a = List.Max(_[Marks]),
b = Table.SelectRows(_, (i) => i[Marks] = a)[Student],
c = Text.Combine(b, ",")
in
c
}
}
),
Final = Table.ExpandTableColumn(Grouped, "Students", {"Student1", "Student2", "Student3"})
in
FinalPower Query solution 12 for Transpose problem table into result, proposed by Luke Jarych:
Luke Jarych one error here, dynamically set up headers like:
Final = Table.ExpandTableColumn(Grouped, "Students", Table.ColumnNames(Table.Combine(Grouped[Students])))Power Query solution 13 for Transpose problem table into result, proposed by Alexandre Garcia:
H = List.Transform,
P = Table.Group(U, "Class", {{"x", each _}, {"y", Table.RowCount}}),
L = H({"1"..Text.From(List.Max(P[y]))}, each "Student" & _),
C = Table.FromRecords(H(P[x], each [
_ = {_}{0},
M = List.Zip(List.Zip({L, _[Student]})),
S = [Class = _{0}[Class]] & Record.FromList(M{1},M{0}) & [Best Student = Text.Combine(Table.SelectRows(Table.AddRankColumn(_,"x", {"Marks",1}), each [x] = 1)[Student], ",")]] [S]))
in C
Power Query solution 14 for Transpose problem table into result, proposed by Charalampos Dimitrakopoulos:
let
Source = Excel.CurrentWorkbook(){[Name = "grades_log"]}[Content],
GroupCol = Table.Group(Source, {"Class"}, {"ClassData", each _, type table}),
MaxColumnCount = List.Max(List.Transform(GroupCol[ClassData], each Table.RowCount(_))),
ColumnNames = List.Transform({1 .. MaxColumnCount}, each "Student " & Text.From(_)),
GroupAddCol = Table.AddColumn(
GroupCol,
"DynamicColumns",
each
let
ClassData = Table.Column([ClassData], "Student"),
PaddedStudents = List.Combine(
{ClassData, List.Repeat({null}, MaxColumnCount - List.Count(ClassData))}
)
in
Record.FromList(PaddedStudents, ColumnNames)
),
Expanded = Table.ExpandRecordColumn(GroupAddCol, "DynamicColumns", ColumnNames),
AddTopScorers = Table.AddColumn(
Expanded,
"Best Student",
each
let
ClassData = [ClassData],
TopScores = List.Max(ClassData[Marks]),
TopStudents = Table.SelectRows(ClassData, each [Marks] = TopScores),
BestStudents = Text.Combine(List.Transform(TopStudents[Student], each Text.From(_)), ", ")
in
BestStudents
),
#"Removed Columns" = Table.RemoveColumns(AddTopScorers, {"ClassData"})
in
#"Removed Columns"Power Query solution 15 for Transpose problem table into result, proposed by Krupesh Bhansali:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GroupRows = Table.Group(
Source,
{"Class"},
{
{"Student", each Table.FromColumns(Table.ToRows(_[[Student]]))},
{
"Best Student",
each
let
max = List.Max(_[Marks])
in
Text.Combine(Table.SelectRows(_, each [Marks] = max)[Student], ",")
}
}
),
#"Expanded Student" = Table.ExpandTableColumn(
GroupRows,
"Student",
{"Column1", "Column2", "Column3"},
{"Student1", "Student2", "Student3"}
)
in
#"Expanded Student"Solving the challenge of Transpose problem table into result with Excel
Excel solution 1 for Transpose problem table into result, proposed by Bo Rydobon 🇹🇭:
=LET(c,
A2:A7,
b,
B2:B7,
m,
C2:C7,
n,
MAX(
COUNTIF(
c,
c
)
),
REDUCE(HSTACK(
A1,
B1&SEQUENCE(
,
n
),
"Best "&B1
),
UNIQUE(
c
),
LAMBDA(a,
v,
VSTACK(a,
HSTACK(v,
EXPAND(
TOROW(
FILTER(
b,
c=v
)
),
,
n,
""
),
ARRAYTOTEXT(FILTER(b,
(c=v)*(m=MAXIFS(
m,
c,
v
)))))))))Excel solution 2 for Transpose problem table into result, proposed by Kris Jaganah:
=LET(
a,
A2:A7,
b,
B2:B7,
c,
C2:C7,
d,
B1,
e,
PIVOTBY(
a,
d&SEQUENCE(
ROWS(
a
)
)-XMATCH(
a,
a
)+1,
b,
ARRAYTOTEXT,
,
0,
,
0
),
f,
IF(
SEQUENCE(
ROWS(
e
),
COLUMNS(
e
)
)=1,
A1,
e
),
HSTACK(
f,
VSTACK(
"Best "&d,
DROP(
GROUPBY(
a,
b,
ARRAYTOTEXT,
,
0,
,
MAXIFS(
c,
a,
a
)=c
),
,
1
)
)
)
)Excel solution 3 for Transpose problem table into result, proposed by Julian Poeltl:
=LET(
T,
A2:C7,
C,
TAKE(
T,
,
1
),
S,
CHOOSECOLS(
T,
2
),
M,
TAKE(
T,
,
-1
),
REDUCE(
HSTACK(
"Class",
"Student"&SEQUENCE(
,
3
),
"Best Student"
),
UNIQUE(
C
),
LAMBDA(
A,
B,
VSTACK(
A,
HSTACK(
B,
EXPAND(
TOROW(
FILTER(
S,
C=B
)
),
1,
3,
""
),
TEXTJOIN(
", ",
,
FILTER(
S,
B&MAX(
& FILTER(
M,
C=B
)
)=C&M
)
)
)
)
)
)
)Excel solution 4 for Transpose problem table into result, proposed by Oscar Mendez Roca Farell:
=LET(
c,
A2:A7,
s,
B2:B7,
m,
C2:C7,
t,
B1,
h,
HSTACK(
PIVOTBY(
c,
t&MAP(
c,
LAMBDA(
a,
COUNTIF(
A2:a,
a
)
)
),
s,
SINGLE,
,
0,
,
0
),
VSTACK(
"Best "&t,
DROP(
GROUPBY(
c,
s,
ARRAYTOTEXT,
,
0,
,
m=MAXIFS(
m,
c,
c
)
),
,
1
)
)
),
IF(
h>"",
h,
A1
)
)Excel solution 5 for Transpose problem table into result, proposed by Duy Tùng:
=LET(
a,
A2:A7,
b,
B2:B7,
c,
C2:C7,
u,
PIVOTBY(
a,
B1&MAP(
a,
LAMBDA(
x,
SUM(
N(
A2:x=x
)
)
)
),
b,
SINGLE,
,
0,
,
0
),
h,
IF(
SEQUENCE(
ROWS(
u
),
COLUMNS(
u
)
)=1,
A1,
u
),
HSTACK(
h,
MAP(
TAKE(
h,
,
1
),
LAMBDA(
x,
TEXTJOIN(
", ",
,
FILTER(
b,
MAXIFS(
c,
a,
x
)=c,
E11
)
)
)
)
)
)Excel solution 6 for Transpose problem table into result, proposed by Sunny Baggu:
=LET(
_u,
UNIQUE(
A2:A7
),
_m,
MAX(
MAP(
_u,
LAMBDA(
c,
SUM(
N(
A2:A7 = c
)
)
)
)
),
REDUCE(
HSTACK(
A1,
B1 & SEQUENCE(
,
_m
),
"Best" & B1
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_a,
TRANSPOSE(
FILTER(
B2:C7,
A2:A7 = y
)
),
_b,
TAKE(
_a,
1
),
_c,
TAKE(
_a,
-1
),
HSTACK(
y,
EXPAND(
_b,
,
_m,
""
),
ARRAYTOTEXT(
FILTER(
_b,
_c = MAX(
_c
)
)
)
)
)
)
)
)
)Excel solution 7 for Transpose problem table into result, proposed by Abdallah Ally:
=LET(
a,
A2:A7,
b,
B2:B7,
c,
C2:C7,
d,
MAX(
COUNTIF(
a,
a
)
),
REDUCE(
HSTACK(
"Class",
"Student"&SEQUENCE(
,
d
),
"Best Student"
),
UNIQUE(
a
),
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
y,
EXPAND(
TOROW(
FILTER(
b,
a=y
)
),
,
d,
""
),
TEXTJOIN(
", ",
,
FILTER(
b,
c=MAX(
FILTER(
c,
a=y
)
)
)
)
)
)
)
)
)Excel solution 8 for Transpose problem table into result, proposed by Md. Zohurul Islam:
=LET(
a,
A2:A7,
b,
B2:B7,
c,
C2:C7,
unq,
UNIQUE(
a
),
u,
IFNA(
DROP(
REDUCE(
"",
unq,
LAMBDA(
x,
y,
VSTACK(
x,
TOROW(
FILTER(
b,
a=y
)
)
)
)
),
1
),
""
),
hdr,
HSTACK(
A1,
B1&SEQUENCE(
,
COLUMNS(
u
)
),
"Best Student"
),
v,
DROP(
REDUCE(
"",
unq,
LAMBDA(
x,
y,
LET(
p,
FILTER(
b,
a=y
),
q,
FILTER(
c,
a=y
),
s,
ARRAYTOTEXT(
FILTER(
p,
q=MAX(
q
)
)
),
VSTACK(
x,
s
)
)
)
),
1
),
w,
VSTACK(
hdr,
HSTACK(
unq,
u,
v
)
),
w
)Excel solution 9 for Transpose problem table into result, proposed by Pieter de B.:
=LET(
s,
"Student",
i,
INDEX,
d,
A2:C7,
a,
i(
d,
,
1
),
b,
i(
d,
,
2
),
c,
i(
d,
,
3
),
z,
HSTACK(
PIVOTBY(
a,
MAP(
a,
LAMBDA(
x,
s&SUM(
N(
A2:x=x
)
)
)
),
b,
SINGLE,
,
0,
,
0
),
VSTACK(
"Best "&s,
DROP(
GROUPBY(
a,
c,
LAMBDA(
x,
TEXTJOIN(
", ",
,
IF(
c=MAX(
x
),
b,
""
)
)
)
),
-1,
1
)
)
),
IF(
SEQUENCE(
ROWS(
z
)
)-1,
z,
IF(
z="",
"Class",
z
)
)
)Excel solution 10 for Transpose problem table into result, proposed by Hamidi Hamid:
=LET(u,
A2:A7,
f,
LAMBDA(
aa,
bb,
DROP(
TEXTSPLIT(
CONCAT(
"/"&MAP(
UNIQUE(
aa
),
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
bb,
aa=a
)
)
)
)
),
", ",
"/"
),
1
)
),
z,
IFERROR(
f(
u,
B2:B7
),
""
),
x,
IFERROR(
f(
u,
C2:C7
)*1,
""
),
t,
DROP(TEXTSPLIT(CONCAT("/"&BYROW(x,
LAMBDA(a,
ARRAYTOTEXT((a=MAX(
a
))*1)))),
", ",
"/"),
1)*1,
tt,
BYROW(
IF(
t=1,
z,
""
),
LAMBDA(
a,
TEXTJOIN(
", ",
1,
a
)
)
),
h,
HSTACK(
UNIQUE(
u
),
HSTACK(
z,
tt
)
),
g,
HSTACK(
"Class",
"Student"&SEQUENCE(
,
COUNTA(
UNIQUE(
u
)
)
),
"Best Student"
),
VSTACK(
g,
h
))Excel solution 11 for Transpose problem table into result, proposed by Asheesh Pahwa:
=LET(
i,
IFNA(
DROP(
REDUCE(
"",
UNIQUE(
A2:A7
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B2:C7,
A2:A7=y
),
ts,
TAKE(
f,
,
1
),
m,
TAKE(
f,
,
-1
),
mx,
MAX(
m
),
e,
ARRAYTOTEXT(
FILTER(
ts,
mx=m
)
),
HSTACK(
y,
e,
TOROW(
ts
)
)
)
)
)
),
1
),
""
),
t,
TAKE(
i,
,
-COLUMNS(
DROP(
i,
,
2
)
)
),
h,
HSTACK(
TAKE(
i,
,
1
),
t,
CHOOSECOLS(
i,
2
)
),
VSTACK(
HSTACK(
A13,
"Student"&SEQUENCE(
,
COLUMNS(
t
)
),
E13
),
h
)
)Excel solution 12 for Transpose problem table into result, proposed by ferhat CK:
=LET(
r,
UNIQUE(
A2:A7
),
st,
DROP(
REDUCE(
0,
r,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
FILTER(
B2:C7,
A2:A7=y
),
HSTACK(
y,
TRANSPOSE(
TAKE(
a,
,
1
)
)
)
)
)
)
),
1
),
c,
MAP(
r,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
B2:B7,
C2:C7=MAXIFS(
C2:C7,
A2:A7,
x
)
)
)
)
),
d,
SEQUENCE(
,
5,
0,
),
IFNA(
VSTACK(
IFS(
d<1,
"Class",
SEQUENCE(
,
5,
0,
)>3,
"Best Student",
1=1,
"Student"&SEQUENCE(
,
5,
0,
)
),
HSTACK(
st,
c
)
),
""
)
)Excel solution 13 for Transpose problem table into result, proposed by Jaroslaw Kujawa:
=REDUCE(
A1:E1;
UNIQUE(
A2:A7
);
LAMBDA(
a;
x;
LET(
gr;
GROUPBY(
A2:A7;
C2:C7;
MAX;
;
0
);
grx;
FILTER(
TAKE(
gr;
;
-1
);
TAKE(
gr;
;
1
)=x
);
all;
FILTER(
B2:C7;
A2:A7=x
);
best;
FILTER(
TAKE(
all;
;
1
);
TAKE(
all;
;
-1
)=grx
);
VSTACK(
a;
TEXTSPLIT(
x&"|"&TEXTJOIN(
"|";
0;
TRANSPOSE(
TAKE(
all;
;
1
)
)
)&REPT(
"|";
3+1-ROWS(
all
)
)&TEXTJOIN(
", ";
1;
best
);
"|"
)
)
)
)
)Excel solution 14 for Transpose problem table into result, proposed by Albert Cid Cañigueral:
=LET(
p,
PIVOTBY(
A2:A7,
"Student"&MAP(
A2:A7,
LAMBDA(
e,
COUNTIF(
$A$2:e,
& e
)
)
),
B2:B7,
ARRAYTOTEXT,
0,
0,
,
0
),
HSTACK(
p,
VSTACK(
"Best Student",
MAP(
DROP(
p,
1,
-3
),
LAMBDA(
e,
TEXTJOIN(
", ",
1,
FILTER(
B2:B7,
C2:C7=MAX(
FILTER(
C2:C7,
A2:A7=e
)
)
)
)
)
)
)
)
)Excel solution 15 for Transpose problem table into result, proposed by JvdV –:
=LET(
a,
A2:A7,
b,
B2:B7,
c,
C2:C7,
f,
INDEX,
x,
VSTACK(
HSTACK(
A2:B7,
MAP(
a,
LAMBDA(
s,
B1&COUNTIF(
A2:s,
s
)
)
)
),
EXPAND(
FILTER(
A2:B7,
c=MAXIFS(
c,
a,
a
)
),
,
3,
"zZBest "&B1
)
),
SUBSTITUTE(
PIVOTBY(
f(
x,
,
1
),
f(
x,
,
3
),
f(
x,
,
2
),
ARRAYTOTEXT,
,
0,
,
0
),
"zZ",
)
)Excel solution 16 for Transpose problem table into result, proposed by Eddy Wijaya:
=LET(
db,
IFNA(DROP(REDUCE(0,
UNIQUE(
A2:A7
),
LAMBDA(a,
v,
VSTACK(a,
HSTACK(v,
LET(
b,
FILTER(B2:B7,
(A2:A7=v)),
c,
BYROW(
b,
LAMBDA(
r,
XLOOKUP(
r,
B2:B7,
C2:C7
)
)
),
d,
IF(
MAX(
c
)=c,
"Max",
""
),
HSTACK(
ARRAYTOTEXT(
FILTER(
b,
LEN(
d
)>1
)
),
TOROW(
b
)
)))))),
1),
""),
sq,
SEQUENCE(
,
COLUMNS(
DROP(
db,
,
2
)
)
),
VSTACK(
HSTACK(
"Class",
"Student"&sq,
"Best Student"
),
CHOOSECOLS(
db,
1,
sq+2,
2
)
))Excel solution 17 for Transpose problem table into result, proposed by Songglod P.:
=LET(c,
A2:A7,
a,
UNIQUE(
c
),
s,
B2:B7,
m,
C2:C7,
g,
DROP(
REDUCE(
0,
a,
LAMBDA(
a,
v,
VSTACK(
a,
TOROW(
FILTER(
s,
c=v
)
)
)
)
),
1
),
IFNA(HSTACK(a,
g,
DROP(REDUCE(0,
a,
LAMBDA(a,
v,
VSTACK(a,
ARRAYTOTEXT(FILTER(s,
(c=v)*(m=MAXIFS(
m,
c,
v
))))))),
1)),
""))Excel solution 18 for Transpose problem table into result, proposed by Moisés Gonga:
= 1
GROUP by Class
),
Pivot_CTE as (
SELECT
Class,
[Student1],
[Student2],
[Student3]
FROM
(
SELECT Class,
RN_STUDENT,
Student
FROM Dataset_CTE
) AS Dataset_CTE
PIVOT
(
MAX(
Student
) FOR RN_STUDENT IN ([Student1],
[Student2],
[Student3])
) AS PivotedTable
)Excel solution 19 for Transpose problem table into result, proposed by SHIV SHANKAR KUMAR:
= 1 THEN Student END) AS Student1,
MAX(
CASE WHEN RowNum = 2 THEN Student END
) AS Student2,
MAX(
CASE WHEN RowNum = 3 THEN Student END
) AS Student3,
STRING_AGG(
CASE WHEN Marks = MaxMarks THEN Student END,
',
'
) AS BestStudent
FROM RankedStudents
GROUP BY Class
)Solving the challenge of Transpose problem table into result with Python
Python solution 1 for Transpose problem table into result, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_249.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=7)
test = pd.read_excel(path, usecols="A:E", skiprows=10, nrows=4)
r1 = (input.assign(rn=input.groupby('Class').cumcount() + 1)
.pivot(index='Class', columns='rn', values='Student')
.reset_index()
.rename(columns=lambda x: f'Student{x}' if isinstance(x, int) else x))
r2 = (input.groupby(['Class', 'Marks'])['Student']
.apply(lambda x: ', '.join(x))
.reset_index()
.sort_values('Marks', ascending=False)
.drop_duplicates('Class')
.rename(columns={'Student': 'Best Student'})
.drop(columns='Marks'))
result = r1.merge(r2, on='Class')
print(result.equals(test))
# True
Python solution 2 for Transpose problem table into result, proposed by Luan Rodrigues:
PY Solution!
import pandas as pd
file = "PQ_Challenge_249.xlsx"
df = pd.read_excel(file,usecols="A:C",nrows=8)
df['Ind'] = "Student" + (df.groupby("Class").cumcount() + 1).astype(str)
df_fim = df.pivot(index='Class',columns='Ind', values='Student' ).reset_index()
df_max = df[df['Marks'] == df.groupby("Class")['Marks'].transform('max')]
grp_max = df_max.groupby("Class")['Student'].apply(lambda x: ','.join(x)).reset_index()[['Student']]
df_res = pd.concat([df_fim,grp_max], axis=1)
print(df_res)
Python solution 3 for Transpose problem table into result, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_249.xlsx'
df = pd.read_excel(file_path, usecols='A:C', nrows=6)
# Perform data manipulation
df1 = df.copy()
df1['MaxMarks'] = df1.groupby('Class')['Marks'].transform('max')
df1 = df1[['Class', 'Student']][df1['Marks'] == df1['MaxMarks']]
df1 = (
df1
.groupby('Class')['Student']
.agg(', '.join)
.reset_index()
.rename(columns={'Student': 'Best Student'})
)
df['Serial'] = df['Class']==df['Class'].shift(1)
df['Serial'] = 'Student' + (df.groupby('Class')['Serial'].cumsum() + 1).map(str)
df = df.pivot(index='Class', columns='Serial', values='Student').fillna('')
df = df.merge(df1, on='Class', how='inner')
df
Solving the challenge of Transpose problem table into result with Python in Excel
Python in Excel solution 1 for Transpose problem table into result, proposed by Alejandro Campos:
df = xl("A1:C7", headers=True)
result = (df.pivot_table(index='Class', columns=df.groupby('Class').cumcount()+1, values='Student', aggfunc='first')
.rename(columns=lambda x: f'Student{x}')
.assign(**{'Best Student': lambda r: r.index.map(lambda x: ', '.join(df.loc[df['Marks'] == df.groupby('Class')['Marks'].transform(max)]['Student'][df['Class'] == x]))})
.fillna(' ')
.reset_index())
Python in Excel solution 2 for Transpose problem table into result, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:C7", True)
header = (data.groupby("Class").cumcount() + 1).map(lambda x: f"Student{x}")
best = data.groupby("Class").apply(
lambda x: ", ".join(x[x["Marks"] == x["Marks"].max()]["Student"])
)
result = data.pivot_table("Student", "Class", header, "first", "").reset_index()
result["Best Student"] = result["Class"].map(lambda x: best[x])
result
Solving the challenge of Transpose problem table into result with R
R solution 1 for Transpose problem table into result, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_249.xlsx"
input = read_excel(path, range = "A1:C7")
test = read_excel(path, range = "A11:E14")
r1 = input %>%
mutate(rn = row_number(), .by = Class) %>%
select(-Marks) %>%
pivot_wider(names_from = rn, values_from = Student, names_glue = "Student{rn}")
r2 = input %>%
summarise(`Best Student` = paste0(Student, collapse = ", "), .by = c(Class, Marks)) %>%
slice_max(order_by = Marks, n = 1, by = Class) %>%
select(-Marks)
result = r1 %>%
left_join(r2, by = "Class")
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&