Large if returning name instead of value

[Fazila]

Because the heading is so clear! I have the following data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]UPN[/TD]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]SEN[/TD]
[TD]Care[/TD]
[TD]FSM[/TD]
[TD]Subject[/TD]
[TD]Type[/TD]
[TD]Class[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]abc[/TD]
[TD]7[/TD]
[TD]S[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Geography[/TD]
[TD]Homework[/TD]
[TD]7L4/Gg[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]def[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Technology[/TD]
[TD]Homework[/TD]
[TD]7L4/Te[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]678[/TD]
[TD]ghi[/TD]
[TD]8[/TD]
[TD]S[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Science[/TD]
[TD]Disruption to Learning[/TD]
[TD]8a/Sc3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]910[/TD]
[TD]jkl[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Geography[/TD]
[TD]Homework[/TD]
[TD]7L8/Gg[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]abc[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Geography[/TD]
[TD]Homework[/TD]
[TD]7L4/Gg[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I need the name of the student with the most points in Homework in a subject. So I would need the formula to return ABC for Geography as this student has 2 points. For the Science department I would want GHI to come up and for Technology DEF.

Does this make any sense?

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Points[/td][td=bgcolor:#70AD47]Subject[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]abc[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]Geography[/td][/tr]

[tr=bgcolor:#FFFFFF][td]def[/td][td]

1​

[/td][td]Technology[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ghi[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]Science[/td][/tr]

[tr=bgcolor:#FFFFFF][td]jkl[/td][td]

3​

[/td][td]Geography[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UPN", Int64.Type}, {"Name", type text}, {"Year", Int64.Type}, {"SEN", type text}, {"Care", type text}, {"FSM", type text}, {"Subject", type text}, {"Type", type text}, {"Class", type text}, {"Points", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count", each _, type table}, {"Points", each List.Sum([Points]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Subject", each List.Distinct(Table.Column([Count],"Subject"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Subject", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"[/SIZE]