Rank by Best of 3 Subjects

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.Combine(
    List.Transform(
      Table.ToRows(Source), 
      each Table.AddRankColumn(
        Table.AddColumn(
          Table.FromRows(
            List.Transform(List.Split(List.Skip(List.RemoveNulls(_)), 6), (l) => {_{0}} & l), 
            List.FirstN(Table.ColumnNames(Source), 7)
          ), 
          "Best of 3 Total", 
          each List.Sum(List.MaxN(List.Skip(Record.ToList(_), 2), 3))
        ), 
        "Rank", 
        {"Best of 3 Total", 1}
      )
    )
  )
in
  Ans

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.FromRows(
    List.Accumulate(
      List.Sort(
        List.Combine(
          List.Transform(
            Table.ToRows(Source), 
            each 
              let
                r = List.RemoveNulls(_)
              in
                List.Transform(
                  List.Split(List.Skip(r), 6), 
                  (n) => {r{0}} & n & {List.Sum(List.FirstN(List.Sort(List.Skip(n), 1), 3))}
                )
          )
        ), 
        {each _{0}, {each _{7}, 1}, each _{1}}
      ), 
      {}, 
      (s, c) =>
        s
          & {
            c
              & {
                if List.Last(s, {0}){0} <> c{0} then
                  1
                else
                  List.Last(s){8} + Number.From(List.Last(s){7} <> c{7})
              }
          }
    ), 
    List.FirstN(Table.ColumnNames(Source), 7) & {"Best of 3 Total", "Rank"}
  )
in
  S

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Class"}, 
    {
      {
        "All", 
        each 
          let
            a = Table.RemoveColumns(_, "Class"), 
            b = List.Transform(Table.ToRows(a), each List.Split(_, 6)){0}, 
            c = Table.SelectRows(
              Table.FromRows(b, List.Skip(List.FirstN(Table.ColumnNames(Source), 7))), 
              each [Student] <> null
            ), 
            d = Table.AddColumn(
              c, 
              "Best of 3 Total", 
              each List.Sum(List.MaxN(List.Skip(Record.ToList(_)), 3))
            ), 
            e = Table.AddRankColumn(
              d, 
              "Rank", 
              {"Best of 3 Total", Order.Descending}, 
              [RankKind = RankKind.Dense]
            )
          in
            Table.SelectRows(e, each [Rank] < 4)
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
  Sol

let
  Fonte = Tabela1, 
  un = Table.UnpivotOtherColumns(Fonte, {"Class"}, "Atributo", "Valor"), 
  add = Table.AddColumn(
    un, 
    "Student", 
    each if Text.StartsWith([Atributo], "Student") then [Valor] else null
  ), 
  pb = Table.FillDown(add, {"Student"}), 
  fil = Table.SelectRows(pb, each [Valor] is number), 
  gp = Table.Group(
    fil, 
    {"Class", "Student"}, 
    {
      {
        "Contagem", 
        each [
          tab = Table.TransformColumns(_, {{"Atributo", each Text.Start(_, 4), type text}}), 
          Un = Table.Pivot(tab, List.Distinct(tab[Atributo]), "Atributo", "Valor", List.Sum), 
          Best = Table.AddColumn(
            Un, 
            "Best of 3 Total", 
            each List.Sum(List.MaxN(List.RemoveFirstN(Record.FieldValues(_), 2), 3))
          )
        ][Best]
      }
    }
  ), 
  exp = Table.ExpandTableColumn(
    gp, 
    "Contagem", 
    List.RemoveFirstN(Table.ColumnNames(gp[Contagem]{0}), 2)
  ), 
  grp = Table.Group(
    exp, 
    {"Class"}, 
    {
      {
        "Contagem", 
        each Table.AddRankColumn(
          Table.Sort(_, {{"Best of 3 Total", Order.Descending}}), 
          "Rank", 
          {"Best of 3 Total", Order.Descending}
        )
      }
    }
  ), 
  res = Table.ExpandTableColumn(
    grp, 
    "Contagem", 
    List.RemoveFirstN(Table.ColumnNames(grp[Contagem]{0}), 1)
  )
in
  res

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  UnpivotOther = Table.UnpivotOtherColumns(Source, {"Class"}, "Attribute", "Value"), 
  AddStudent = Table.FillDown(
    Table.AddColumn(
      UnpivotOther, 
      "Student", 
      each if Value.Type([Value]) = Number.Type then null else [Value]
    ), 
    {"Student"}
  ), 
  Filter = Table.RemoveColumns(
    Table.SelectRows(AddStudent, each Text.StartsWith([Attribute], "Sub")), 
    "Attribute"
  ), 
  AddBestOf3 = Table.Group(
    Filter, 
    {"Class", "Student"}, 
    {"BestOf3", each List.Sum(List.FirstN(List.Sort([Value], Order.Descending), 3)), Int64.Type}
  ), 
  Group = Table.Group(
    AddBestOf3, 
    {"Class"}, 
    {{"All", each _, type table [Class = number, Student = text, BestOf3 = number]}}
  ), 
  AddRank = Table.RemoveColumns(
    Table.AddColumn(
      Group, 
      "RankScores", 
      each Table.AddRankColumn(
        [All], 
        "Rank", 
        {"BestOf3", Order.Descending}, 
        [RankKind = RankKind.Dense]
      )
    ), 
    "All"
  ), 
  Expand = Table.ExpandTableColumn(
    AddRank, 
    "RankScores", 
    {"Student", "BestOf3", "Rank"}, 
    {"Student", "BestOf3", "Rank"}
  )
in
  Expand

let
  Source = Excel.CurrentWorkbook(){[Name = "StudentSub"]}[Content], 
  Unpivoted = Table.UnpivotOtherColumns(Source, {"Class"}, "Attribute", "Value"), 
  Replaced = Table.ReplaceValue(
    Unpivoted, 
    each [Attribute], 
    each if Text.StartsWith([Attribute], "Student") then "Student" else Text.Start([Attribute], 4), 
    Replacer.ReplaceValue, 
    {"Attribute"}
  ), 
  Student = Table.AddColumn(
    Replaced, 
    "Student", 
    each if [Attribute] = "Student" then [Value] else null
  ), 
  FilledDown = Table.FillDown(Student, {"Student"}), 
  Filtered = Table.SelectRows(FilledDown, each ([Attribute] <> "Student")), 
  Pivoted = Table.Pivot(Filtered, List.Distinct(Filtered[Attribute]), "Attribute", "Value"), 
  Best3 = Table.AddColumn(
    Pivoted, 
    "Best of 3 Total", 
    each List.Sum(List.MaxN(List.RemoveFirstN(Record.ToList(_), 2), 3))
  ), 
  Grouped = Table.Group(
    Best3, 
    {"Class"}, 
    {{"All", each Table.AddRankColumn(_, "Rank", {"Best of 3 Total", Order.Descending})}}
  )[All], 
  Expand = Table.Combine(Grouped)
in
  Expand

let
  Source = Excel.CurrentWorkbook(){[Name = "problem"]}[Content], 
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), 
  result = 
    let
      a = Table.ToRows(#"Promoted Headers"), 
      b = List.Transform(
        a, 
        each 
          let
            ba = List.RemoveNulls(_), 
            bb = List.Split(List.Skip(ba, 1), 6), 
            bc = List.Transform(
              bb, 
              each {List.First(ba)} & _ & {List.Sum(List.MaxN(List.Skip(_, 1), 3))}
            )
          in
            Table.FromRows(
              bc, 
              {"Class", "Student", "Sub1", "Sub2", "Sub3", "Sub4", "Sub5", "Best of 3 Total"}
            )
      ), 
      c = List.Transform(
        b, 
        each Table.AddRankColumn(
          _, 
          "Rank", 
          {"Best of 3 Total", Order.Descending}, 
          [RankKind = RankKind.Competition]
        )
      )
    in
      Table.Combine(c)
in
  result

=LET(a,B2:Y5,c,TOCOL(IFS(a>"",A2:A5),3),b,WRAPROWS(TOCOL(a,3),6),t,BYROW(b,LAMBDA(c,SUM(LARGE(c,{1,2,3})))),
VSTACK(HSTACK(A1:G1,{"Best of 3 Total","Rank"}),SORT(HSTACK(c,b,t,MAP(c,t,LAMBDA(d,s,XMATCH(s,UNIQUE(SORT(FILTER(t,c=d),,-1)))))),{1,9})))

=REDUCE(HSTACK(UNIQUE(A1:Y1,1),"Best of 3 Total",
"Rank"),A2:A5,LAMBDA(a,d,LET(V,OFFSET(d,,1,,24),
x,WRAPROWS(FILTER(V,V>0),6),c,BYROW(x,LAMBDA(
q,SUM(TAKE(SORT(q,,-1,1),,4)))),p,SORT(c,,-1),VSTACK(a,
HSTACK(SORT(IFNA(HSTACK(d,x,c),d),8,-1),SCAN(0,
p<>DROP(VSTACK(0,p),-1),LAMBDA(a,d,a+d)))))))

=LET(
 _tbl, WRAPROWS(TOCOL(B2:Y5, 3), 6),
 class, MAP(TAKE(_tbl, , 1), LAMBDA(b, FILTER(A2:A5, BYROW(B2:Y5 = b, LAMBDA(a, OR(a)))))),
 uclass, UNIQUE(class),
 name, TAKE(_tbl, , 1),
 _stbl, SORTBY(_tbl, class, , name, ),
 _best3sum, MAKEARRAY(ROWS(_stbl), 1, LAMBDA(r, c, INDEX(SUM(DROP(SORT(INDEX(DROP(_stbl, , 1), r, ), , , 1), , 2)), c))),
 rank, TEXTSPLIT(
 ARRAYTOTEXT(MAP(uclass, LAMBDA(a, LET(_f, FILTER(_best3sum, class = a), ARRAYTOTEXT(XMATCH(_f, SORT(_f, , -1))))))),
 ,
 ", "
 ),
 HSTACK(class, _stbl, _best3sum, rank)
)

=LET(t,A1:Y5,d,DROP(t,1,1),y,DROP(TAKE(t,,1),1),m,WRAPROWS(TOCOL(d,1),6),n,BYROW(DROP(m,,1),LAMBDA(a,SUM(TAKE(SORT(a,,-1,1),,3)))),c,MAP(TAKE(m,,1),LAMBDA(a,MAX(IF(a=d,y)))),o,SORTBY(HSTACK(c,m,n),c,1,n,-1),r,REDUCE("Rank",y,LAMBDA(a,b,VSTACK(a,LET(p,FILTER(TAKE(o,,-1),c=b),XLOOKUP(p,p,SEQUENCE(COUNT(p)),,-1))))),HSTACK(VSTACK(HSTACK(TAKE(t,1,7),"Best of 3 Total"),o),r))

=LET(w,WRAPROWS(TOCOL(B2:Y5),6,1),f,FILTER(HSTACK(INDEX(A2:A5,INT(SEQUENCE(ROWS(w),,1,1/ROWS(A2:A5))),),w),ISTEXT(TAKE(w,,1))),h,HSTACK(INDEX(A2:A5,INT(SEQUENCE(ROWS(w),,1,1/ROWS(A2:A5))),),w),s,BYROW(TAKE(f,,-5),LAMBDA(b,SUM(TAKE(SORT(b,,,1),,-3)))),r,MMULT((TOROW(TAKE(f,,1))=TAKE(f,,1))*(TOROW(s)>s),SEQUENCE(ROWS(s),,,0))+1,VSTACK(HSTACK(A1:G1,"Total","Rank"),SORT(HSTACK(f,s,r),{1,9})))

=REDUCE(HSTACK(A1:G1, "Best of 3 Total","Rank"),SEQUENCE(4), LAMBDA(acc,itr,LET(a,INDEX(A2:Y5,itr,),
b,TRANSPOSE(a),
c,ISNUMBER(DROP(b,1)),
d,WRAPROWS(FILTER(DROP(b,1),c),5), 
e,TAKE(b,1)&"-"&FILTER(b,ISTEXT(b)),
f,BYROW(d,LAMBDA(x,SUM(LARGE(x,SEQUENCE(3))))), g,TEXTSPLIT(e,"-"),
h,TEXTAFTER(e,"-"), 1),
VSTACK(acc,HSTACK(g,SORTBY(h,f,-1),SORTBY(d,f,-1),SORT(f,,-1),XMATCH(SORT(f,,-1),SORT(f,,-1)))))))

={A1:G1,"Best of 3 Total","Rank";SORT(LET(v,REDUCE(TOCOL(,1),SEQUENCE(4),LAMBDA(a,c,VSTACK(a,QUERY(CHOOSECOLS(A2:5,1,SEQUENCE(6,1,6*(c-1)+2)),"where Col2<>''",)))),c,INDEX(v,,1),l,BYROW(v,LAMBDA(r,SUM(LARGE(r,{1,2,3})))),{v,l,MAP(c,l,LAMBDA(_c,_l,RANK(_l,FILTER(l,c=_c))))}),1,1,9,1)

=LET(Data, A2:Y5, fx_OneRow, LAMBDA(OneRow, LET(OneRow2, A5:Y5, WrappedData, WRAPROWS(DROP(OneRow, , 1), 6), RemoveBlanks, FILTER(WrappedData, CHOOSECOLS(WrappedData, 1) <> ""), BestThreeTotal, BYROW(RemoveBlanks, LAMBDA(row, SUM(TAKE(SORT(DROP(row, , 1), , , TRUE), , -3)))), MarkWithBestThree, SORTBY(HSTACK(RemoveBlanks, BestThreeTotal), BestThreeTotal, -1), Rank, XMATCH(CHOOSECOLS(MarkWithBestThree, -1), CHOOSECOLS(MarkWithBestThree, -1), 0), Result, HSTACK(SEQUENCE(ROWS(Rank), , INDEX(OneRow, 1, 1), 0), MarkWithBestThree, Rank), Result)), REDUCE(HSTACK(A1:G1, "Best Of 3 Total", "Rank"), SEQUENCE(ROWS(Data)), LAMBDA(a,v, VSTACK(a, fx_OneRow(CHOOSEROWS(Data, v))))))