Generate Top 3 students in all subjects. The order of subjects in Result table should be same as in source table. In case of duplicates, show the names in one cell below the other Of course, the query should be dynamic i.e. Adding a new subject or deleting a new subject should give right result. Dynamism is not a requirement for Excel formula solutions.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 19
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Top Students Subject Order with Power Query
Power Query solution 1 for Top Students Subject Order, proposed by Eric Laforce:
let
fxGetTop3Name = (subject as text) =>
let
_Extract = Table.SelectColumns(Data, {"Name",subject}),
_Rename = Table.RenameColumns(_Extract,{{subject, "Mark"}}),
_Rank = Table.AddRankColumn(_Rename, "Rank", {"Mark", Order.Descending}, [RankKind=RankKind.Dense]),
_Filter = Table.SelectRows(_Rank, each [Rank] <= 3),
_Group = Table.Group(_Filter, {"Rank"}, {{"Data",
each Table.AddIndexColumn(_, "Index", 1,1), type table [Name=text,Rank=nullable number,Index=nullable number]}})
in Table.ExpandTableColumn(_Group, "Data", {"Name","Index"},{"Name","Index"}),
Source = Excel.CurrentWorkbook(){[Name="tData19"]}[Content],
Data = Table.Buffer(Table.RenameColumns(Source,{{"NameSubject", "Name"}})),
Subjects = List.Skip(Table.ColumnNames(Data)),
Custom1 = List.Accumulate(Subjects, {}, (s,c)=> s & {[Subject=c, Top=fxGetTop3Name(c)]}),
ToTable = Table.FromRecords(Custom1),
Expand = Table.ExpandTableColumn(ToTable, "Top", {"Rank","Name","Index"}, {"Rank","Name","Index"}),
Pivot = Table.Pivot(Expand, List.Distinct(Expand[Subject]), "Subject","Name"),
RemoveIndex = Table.RemoveColumns(Pivot,{"Index"})
in
RemoveIndex
Power Query solution 4 for Top Students Subject Order, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"NameSubject", type text}, {"Maths", Int64.Type}, {"English", Int64.Type}, {"Physics", Int64.Type}, {"History", Int64.Type}, {"Social Studies", Int64.Type}, {"Chemistry", Int64.Type}, {"Biology", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"NameSubject"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"NameSubject"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Attribute", Order.Ascending}, {"Value", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Attribute"}, {{"All", each _, type table [Attribute=text, Value=number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Table", each Table.AddIndexColumn([All],"Index.",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Table"}),
#"Grouped Rows1" = Table.Group(#"Added Index1", {"Attribute"}, {{"Count", each _, type table [#"NameSubject"=nullable text, Attribute=text, #"Index."=nullable number, Index=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Count],"Indx.",1,1)),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"NameSubject", "Attribute", "Index.", "Index", "Indx."}, {"NameSubject", "Attribute", "Index.", "Index", "Indx."}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Attribute]), "Attribute", "NameSubject"),
Custom2 = Table.SelectColumns(#"Pivoted Column",{"Indx.","Maths","English","Physics","History","Social Studies","Chemistry","Biology"},MissingField.UseNull)
in
Custom2
Power Query solution 5 for Top Students Subject Order, proposed by Matthias Friedmann:
lets you choose between RankKind.Ordinal, RankKind.Competition and RankKind.Dense is not yet available. 😒
-Dynamic?
My go to option with UI support is Unpivot.
-Which ranking kind to take?
Clearly for Top3 you need to go for "standard competition".
https://en.wikipedia.org/wiki/Ranking
Excel BI chose dense though (s. e.g. Social Studies). 🤷♂️
-What to do without the new ranking formula?
With grouping and adding index you can achieve ranking.
Here it is applied twice, and getting the min of an index gives you standard competition ranking: https://lnkd.in/eH3jTRHu
#"Expanded Table" = Table.ExpandTableColumn(#"Removed Other Columns", "Table", {"Attribute", "Value", "Index."}, {"Attribute", "Value", "Index."}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table", each [#"Index."] <= 3),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Attribute", type text}, {"Value", Int64.Type}, {"Index.", Int64.Type}}),
Back = #"Unpivoted Other Columns",
Custom1 = Table.NestedJoin(Back,{"Attribute","Value"},#"Changed Type1",{"Attribute","Value"},"Back",JoinKind.LeftOuter),
#"Expanded Back" = Table.ExpandTableColumn(Custom1, "Back", {"Value", "Index."}, {"Value.1", "Index."}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Back", each ([#"Index."] <> null)),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows1",{{"Attribute", Order.Ascending}, {"Index.", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Value", "Value.1"}),
#"Added Index1" = Table.AddIndexColumn(#"Removed Columns1", "Index", 1, 1, Int64.Type),
Power Query solution 6 for Top Students Subject Order, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Subjects = List.Skip(Table.ColumnNames(Source)),
GetLists = List.Accumulate({1..3}, {}, (s1, c1) => s1 & { {{c1}} & List.Accumulate(Subjects, {}, (s2, c2) => s2 & {Table.SelectRows( Table.AddRankColumn( Source, c2 & "_rank", {c2, Order.Descending}, [RankKind = RankKind.Dense]), each Record.Field(_, c2 & "_rank") = c1)[#"NameSubject"]})}),
Combine = Table.Combine(List.Transform(GetLists, each Table.FromColumns(_, {"Rank"} & Subjects))),
#"Filled Down" = Table.FillDown(Combine,{"Rank"})
in
#"Filled Down"
Solving the challenge of Top Students Subject Order with Excel
Excel solution 1 for Top Students Subject Order, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
SEQUENCE(
3
),
z,
A2:H31,
rks,
MAP(
z,
LAMBDA(
x,
XMATCH(
x,
SORT(
UNIQUE(
INDEX(
z,
,
COLUMN(
x
)
)
),
,
-1
)
)
)
),
REDUCE(
HSTACK(
"Rank",
B1:H1
),
n,
LAMBDA(
aa,
vv,
VSTACK(
aa,
LET(
nx,
REDUCE(
vv,
SEQUENCE(
,
COLUMNS(
z
)-1,
2
),
LAMBDA(
a,
v,
HSTACK(
a,
FILTER(
TAKE(
z,
,
1
),
CHOOSECOLS(
rks,
v
)=vv
)
)
)
),
fr,
TAKE(
nx,
1
),
IF(
N(
+fr
),
fr,
IFERROR(
nx,
""
)
)
)
)
)
)
)Excel solution 2 for Top Students Subject Order, proposed by محمد حلمي:
=VSTACK(
HSTACK(
"Rank",
B1:H1
),
DROP(
REDUCE(
0,
ROW(
1:3
),
LAMBDA(
C,
V,
VSTACK(
C,
LET(
z,
TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
"/",
,
BYCOL(
B2:H31,
LAMBDA(
A,
TEXTJOIN(
"-",
,
FILTER(
A2:A31,
A=LARGE(
UNIQUE(
A
),
V
)
)
)
)
)
),
"-",
"/",
1,
,
""
)
),
HSTACK(
SEQUENCE(
ROWS(
z
),
,
V,
0
),
z
)
)
)
)
),
1
)
)Excel solution 3 for Top Students Subject Order, proposed by 🇰🇷 Taeyong Shin:
=REDUCE(
HSTACK(
"Rank",
B1:H1
),
SEQUENCE(
3
),
LAMBDA(
a,
x,
VSTACK(
a,
REDUCE(
0,
B2:H2,
LAMBDA(
a,
v,
LET(
c,
TAKE(
v:H31,
,
1
),
s,
HSTACK(
a,
FILTER(
A2:A31,
XMATCH(
c,
SORT(
UNIQUE(
c
),
,
-1
)
)=x
)
),
IFS(
SEQUENCE(
,
COLUMNS(
s
)
)=1,
x,
ISNA(
s
),
"",
1,
s
)
)
)
)
)
)
)Excel solution 4 for Top Students Subject Order, proposed by 🇰🇷 Taeyong Shin:
=LET(Name,
A2:A31,
Head,
B1:H1,
Score,
B2:H31,
nth,
3,
fnLarge,
LAMBDA(
col,
LARGE(
UNIQUE(
col
),
nth
)
),
MaxCol,
BYCOL(Score,
LAMBDA(bc,
COUNT(1/(bc>=fnLarge(
bc
) )) )),
Col,
CHOOSECOLS(
Score,
XMATCH(
MAX(
MaxCol
),
MaxCol
)
),
R,
SORT(
FILTER(
Col,
fnLarge(
Col
)<=Col
)
),
Rnk,
XMATCH(
R,
UNIQUE(
R
)
),
Expr,
LAMBDA(c,
LET(Cols,
CHOOSECOLS(
Score,
c
),
RnkTbl,
SORT(
FILTER(
HSTACK(
Name,
Cols
),
Cols>=fnLarge(
Cols
)
),
2,
-1
),
RnkName,
CHOOSECOLS(
RnkTbl,
1
),
RnkScore,
CHOOSECOLS(
RnkTbl,
2
),
idx,
XMATCH(
RnkScore,
UNIQUE(
RnkScore
)
),
REDUCE("",
SEQUENCE(
nth
),
LAMBDA(a,
b,
VSTACK(a,
EXPAND(FILTER(
RnkName,
idx=b
),
COUNT(1/(Rnk=b)),
,
"")) ))
)),
Body,
DROP(
REDUCE(
"",
SEQUENCE(
COLUMNS(
Score
)
),
LAMBDA(
a,
b,
HSTACK(
a,
Expr(
b
)
)
)
),
1,
1
),
VSTACK(
HSTACK(
"Rank",
Head
),
HSTACK(
Rnk,
Body
)
)
)Excel solution 5 for Top Students Subject Order, proposed by Duy Tùng:
=LET(
f,
LAMBDA(
a,
IFNA(
HSTACK(
a,
IFNA(
DROP(
REDUCE(
a,
B1:H1,
LAMBDA(
x,
y,
HSTACK(
x,
LET(
b,
TAKE(
y:H31,
,
1
),
FILTER(
A1:A31,
b=LARGE(
UNIQUE(
b
),
& a
)
)
)
)
)
),
,
1
),
""
)
),
a
)
),
VSTACK(
J1:Q1,
f(
1
),
f(
2
),
f(
3
)
)
)Excel solution 6 for Top Students Subject Order, proposed by Md. Zohurul Islam:
=LET(
nam,A2:A31,
sub,B1:H1,
data,B2:H31,
rnk,SEQUENCE(3),
sq,SEQUENCE(,COUNTA(sub)),
res,DROP(REDUCE("",rnk,LAMBDA(q,p,LET(u,IFNA(DROP(REDUCE("",sq,LAMBDA(x,y,LET(a,CHOOSECOLS(data,y),b,LARGE(UNIQUE(a),p),c,FILTER(nam,a=b),d,HSTACK(x,c),d))),, 1),""),v,IFNA(HSTACK(p,u),p),w,VSTACK(q,v),w))),1),
ans,VSTACK(HSTACK("Rank",sub),res),
ans)Excel solution 7 for Top Students Subject Order, proposed by Sarun Chimamphant:
=LET(
score,
B2:H31,
DROP(
REDUCE(
"",
{1,
2,
3},
LAMBDA(
acc,
row,
VSTACK(
acc,
IFERROR(
HSTACK(
row,
DROP(
REDUCE(
"",
SEQUENCE(
7
),
LAMBDA(
acc,
col,
IFERROR(
HSTACK(
acc,
FILTER(
A2:A31,
INDEX(
score,
,
col
)=LARGE(
UNIQUE(
INDEX(
score,
,
col
)
),
row
)
)
),
""
)
)
),
,
1
)
),
row
)
)
)
),
1
)
)