Sales Year Monthly Summary

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  T = each [Team], 
  N = each [Runs Scored], 
  R = (f, v) => Table.SelectRows(Source, each f(_) = v), 
  S = Table.AddColumn(
    Table.AddColumn(
      List.Accumulate(
        {0 .. Table.RowCount(R(T, List.Mode(T(Source)))) - 1}, 
        Table.Distinct(Source[[Team]]), 
        (s, c) =>
          Table.AddColumn(
            s, 
            "Player" & Text.From(c + 1), 
            each (R(T, T(_)){c}? ?? [Player = null])[Player]
          )
      ), 
      "Highest Scoring Player", 
      each Text.Combine(R(N, List.Max(N(R(T, T(_)))))[Player], ", ")
    ), 
    "Highest Score", 
    each List.Max(N(R(T, T(_))))
  )
in
  S

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  T = each Table.SelectRows(Source, (r) => r[Team] = [Team]), 
  P = Table.ReplaceValue(
    Source, 
    each Table.PositionOf(T(_), _), 
    0, 
    (x, y, z) => "Player" & Text.From(y + 1), 
    {"Runs Scored"}
  ), 
  S = Table.Sort(
    Table.ExpandRecordColumn(
      Table.AddColumn(
        Table.Pivot(P, List.Distinct(P[#"Runs Scored"]), "Runs Scored", "Player"), 
        "H", 
        each 
          let
            m = List.Max(T(_)[Runs Scored])
          in
            [K = m, J = Text.Combine(Table.SelectRows(T(_), each [Runs Scored] = m)[Player], ", ")]
      ), 
      "H", 
      {"J", "K"}, 
      {"Highest Scoring Player", "Highest Score"}
    ), 
    each List.PositionOf(Source[Team], [Team])
  )
in
  S

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Idx = Table.AddColumn(Source, "Team I", each List.PositionOf(Source[Team], [Team]) + 1), 
  No = Table.AddColumn(
    Idx, 
    "No.", 
    each "Player" & Text.From(Character.ToNumber([Player]) - 63 - [Team I])
  ), 
  Max = Table.AddColumn(
    No, 
    "Highest Score", 
    each List.Max(Table.SelectRows(No, (x) => x[Team] = [Team])[Runs Scored])
  ), 
  High = Table.AddColumn(
    Max, 
    "Highest Scoring Player", 
    each Text.Combine(
      Table.SelectRows(No, (x) => x[Team] = [Team] and x[Runs Scored] = [Highest Score])[Player], 
      ", "
    )
  ), 
  Remov = Table.RemoveColumns(High, {"Runs Scored"}), 
  Pivot = Table.Pivot(Remov, List.Distinct(Remov[#"No."]), "No.", "Player"), 
  Sort = Table.Sort(Pivot, {"Team I", 0}), 
  Keep = Table.SelectColumns(
    Sort, 
    {"Team", "Player1", "Player2", "Player3", "Player4", "Highest Scoring Player", "Highest Score"}
  )
in
  Keep

let
  Source = Table1, 
  grp = Table.Group(
    Source, 
    {"Team"}, 
    {
      {
        "Players", 
        each [
          players = List.Transform({1 .. List.Count([Player])}, each "Player" & Text.From(_)), 
          rec     = Record.FromList([Player], players)
        ][rec]
      }, 
      {
        "Highest Scoring Player", 
        each [
          maxValue = List.Max([Runs Scored]), 
          myTable  = Table.SelectRows(_, (x) => x[Runs Scored] = maxValue), 
          combine  = Text.Combine(myTable[Player], ", ")
        ][combine]
      }, 
      {"Highest Score", each List.Max([Runs Scored])}
    }
  ), 
  exp = Table.ExpandRecordColumn(grp, "Players", {"Player1", "Player2", "Player3", "Player4"})
in
  exp

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Group = Table.Group(
    Source, 
    "Team", 
    {
      {
        "P", 
        each [
          L = {[Player]}, 
          T = Table.FromRows(L), 
          R = Table.TransformColumnNames(T, (f) => Text.Replace(f, "Column", "Player "))
        ][R]
      }, 
      {
        "Highest Scorer", 
        each [
          M = List.Max([Runs Scored]), 
          S = Table.SelectRows(_, (f) => f[Runs Scored] = M)[Player], 
          R = Text.Combine(S, ", ")
        ][R]
      }, 
      {"Highest Score", each List.Max([Runs Scored])}
    }
  ), 
  ColNames = Table.ColumnNames(List.Max(Group[P], 1, Table.ColumnCount)), 
  Return = Table.ExpandTableColumn(Group, "P", ColNames)
in
  Return

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Team"}, 
    {
      {
        "Player", 
        each Table.FromRows(
          {[Player]}, 
          List.Transform({1 .. List.Count([Player])}, (x) => "Player" & Text.From(x))
        )
      }, 
      {
        "Highest Score Player", 
        each Text.Combine(
          let
            a = List.Max([Runs Scored]), 
            b = Table.SelectRows(_, each [Runs Scored] = a)
          in
            b[Player], 
          ", "
        )
      }, 
      {"Highest Score", each List.Max([Runs Scored])}
    }
  ), 
  Pos = 
    let
      a = List.Transform(Group[Player], each Table.ColumnCount(_)), 
      b = List.Max(a), 
      c = List.PositionOf(a, b)
    in
      c, 
  Sol = Table.ExpandTableColumn(Group, "Player", Table.ColumnNames(Group[Player]{Pos}))
in
  Sol

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Team"}, 
    {
      {
        "A", 
        each 
          let
            a = [Player], 
            b = List.Max([Runs Scored]), 
            c = Table.SelectRows(_, each [Runs Scored] = b), 
            d = Text.Combine(c[Player], ", "), 
            e = c[Runs Scored]{0}, 
            f = Table.FromRows(
              {a}, 
              List.Transform({1 .. List.Count(a)}, each "Player" & Text.From(_))
            ), 
            g = Table.AddColumn(
              Table.AddColumn(f, "Highest Scoring Player", each d), 
              "Highest Score", 
              each e
            )
          in
            g
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{2}))
in
  Sol

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Team"}, 
    {
      {
        "A", 
        each 
          let
            a = [Player], 
            b = List.Max([Runs Scored]), 
            c = Table.SelectRows(_, each [Runs Scored] = b), 
            d = Text.Combine(c[Player], ", "), 
            e = c[Runs Scored]{0}, 
            f = Table.FromRows(
              {a}, 
              List.Transform({1 .. List.Count(a)}, each "Player" & Text.From(_))
            ), 
            g = Table.AddColumn(
              Table.AddColumn(f, "Highest Scoring Player", each d), 
              "Highest Score", 
              each e
            )
          in
            g
      }
    }
  ), 
  Pos = 
    let
      a = List.Transform(Group[A], each List.Count(Table.ColumnNames(_))), 
      b = List.PositionOf(a, List.Max(a))
    in
      b, 
  Sol = Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{Pos}))
in
  Sol

let
  Fonte = Tabela1, 
  gp = Table.Group(
    Fonte, 
    {"Team"}, 
    {
      {
        "tab", 
        each 
          let
            t = Table.FromRows({[Player]}), 
            r = List.Transform(Table.ColumnNames(t), each Text.Replace(_, "Column", "Player"))
          in
            Table.RenameColumns(t, List.Zip({Table.ColumnNames(t), r}))
      }, 
      {
        "Highest Scoring Player", 
        each 
          let
            max = List.Max([Runs Scored]), 
            tb  = Table.SelectRows(_, each [Runs Scored] = max)
          in
            Text.Combine(tb[Player], ", ")
      }, 
      {
        "Highest Score", 
        each 
          let
            max = List.Max([Runs Scored])
          in
            max
      }
    }
  ), 
  max = 
    let
      a = List.Transform(gp[tab], Table.ColumnCount), 
      m = List.Max(a)
    in
      List.PositionOf(a, m), 
  res = Table.ExpandTableColumn(gp, "tab", Table.ColumnNames(gp[tab]{max}))
in
  res

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Team"}, 
    {
      {
        "All", 
        each Table.TransformColumns(
          Table.AddIndexColumn(_, "Ind", 1), 
          {{"Ind", each "Playr" & Text.From(_)}}
        )
      }, 
      {"Highest Score", each List.Max([Runs Scored])}
    }
  ), 
  AddHighPlay = Table.AddColumn(
    Group, 
    "Scoring Player", 
    each 
      let
        a = [Highest Score]
      in
        Table.SelectRows([All], each ([Runs Scored] = a))[Player]
  ), 
  Extract = Table.TransformColumns(
    AddHighPlay, 
    {"Scoring Player", each Text.Combine(List.Transform(_, Text.From), ",")}
  ), 
  Expand = Table.ExpandTableColumn(Extract, "All", {"Player", "Ind"}, {"Player", "Ind"}), 
  PivotPlayer = Table.Pivot(Expand, List.Distinct(Expand[Ind]), "Ind", "Player")
in
  PivotPlayer

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.Group(
    S, 
    {"Team"}, 
    {
      {
        "G", 
        each Table.TransformColumnNames(
          Table.FromRows({[Player]}), 
          each Text.Replace(_, "Column", "Player")
        )
      }, 
      {
        "Highest Scoring Player", 
        each Text.Combine(
          Table.Combine(
            Table.MaxN(Table.Group(_, {"Runs Scored"}, {"H", each _}), "Runs Scored", 1)[H]
          )[Player], 
          ", "
        )
      }, 
      {"Highest Score", each List.Max([Runs Scored])}
    }
  ), 
  Sol = Table.ExpandTableColumn(a, "G", {"Player1", "Player2", "Player3", "Player4"})
in
  Sol

let
  Source = Excel.CurrentWorkbook(){[Name = "tData179"]}[Content], 
  Group = Table.Group(
    Source, 
    "Team", 
    {
      {
        "P", 
        each 
          let
            P  = [Player], 
            CN = List.Transform({1 .. List.Count(P)}, each "Player" & Text.From(_))
          in
            Table.FromRows({P}, CN)
      }, 
      {
        "Highest Scoring Player", 
        each 
          let
            MaxS    = List.Max([Runs Scored]), 
            Players = Table.SelectRows(_, each [Runs Scored] = MaxS)[Player]
          in
            Text.Combine(Players, ", ")
      }, 
      {"Highest Score", each List.Max([Runs Scored])}
    }
  ), 
  CN = Table.ColumnNames(List.Max(Group[P], 1, Table.ColumnCount)), 
  Expand = Table.ExpandTableColumn(Group, "P", CN)
in
  Expand

let
  So = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Z = Table.Group(
    So, 
    {"Team"}, 
    {{"T", each _, type table [Team = text, Player = text, Runs Scored = number]}}
  ), 
  T1 = Table.AddColumn(
    Z, 
    "M", 
    each Table.AddRankColumn(
      [T], 
      "R", 
      {"Runs Scored", Order.Descending}, 
      [RankKind = RankKind.Competition]
    )
  ), 
  T2 = Table.AddColumn(
    T1, 
    "N", 
    each Table.AddIndexColumn(Table.Sort([M], {"Player", Order.Ascending}), "I", 1, 1)
  ), 
  T3 = Table.AddColumn(T2, "O", each Table.AddColumn([N], "F", each "Player " & Text.From([I]))), 
  X = Table.SelectColumns(T3, {"O"}), 
  L = Table.ExpandTableColumn(X, "O", {"Team", "Player", "F"}, {"Team", "Player", "F"}), 
  T = Table.Pivot(L, List.Distinct(L[F]), "F", "Player"), 
  K = Table.ExpandTableColumn(
    X, 
    "O", 
    {"Team", "Player", "Runs Scored", "I", "R", "F"}, 
    {"Team", "Player", "Runs Scored", "I", "R", "F"}
  ), 
  M = Table.SelectRows(K, each ([R] = 1)), 
  O = Table.Group(
    M, 
    {"Team"}, 
    {
      {"HS", each List.Max([Runs Scored]), type number}, 
      {"HSPlayer", each Text.Combine([Player], ","), type text}
    }
  ), 
  P = Table.NestedJoin(T, {"Team"}, O, {"Team"}, "N"), 
  Q = Table.ExpandTableColumn(P, "N", {"HS", "HSPlayer"}, {"HS", "HSPlayer"})
in
  Q

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  getPlCols = (c) => List.Transform({1 .. c}, each "Player" & Text.From(_)), 
  hs_cols = {"Highest #(lf)Scoring #(lf)Player", "Highest #(lf)Score"}, 
  getPl = each Record.FromList([Player], getPlCols(List.Count([Player]))), 
  getHS = each [
    max    = List.Max([Runs Scored]), 
    pl     = Table.SelectRows(_, each [Runs Scored] = max)[Player], 
    result = Record.FromList({Text.Combine(pl, ", "), max}, hs_cols)
  ][result], 
  group = Table.Group(Source, {"Team"}, {{"pl", getPl}, {"hs", getHS}}), 
  pl_cols = getPlCols(List.Max(List.Transform(group[pl], Record.FieldCount))), 
  expand = Table.ExpandRecordColumn, 
  result = expand(expand(group, "pl", pl_cols), "hs", hs_cols)
in
  result

let
  Source = Data, 
  Result = Table.Group(
    Source, 
    {"Team"}, 
    {
      {
        "Player", 
        each [
          players = [Player], 
          columns = List.Transform({1 .. List.Count(players)}, each "Player" & Text.From(_)), 
          records = Record.FromList(players, columns)
        ][records]
      }, 
      {
        "Highest Scoring Player", 
        each [
          MaxScore = List.Max([Runs Scored]), 
          Players  = Text.Combine(Table.SelectRows(_, each [Runs Scored] = MaxScore)[Player], ", ")
        ][Players]
      }, 
      {"Highest Score", each List.Max([Runs Scored]), type number}
    }
  ), 
  Players = Table.ExpandRecordColumn(
    Result, 
    "Player", 
    Record.FieldNames(Record.Combine(Result[Player]))
  )
in
  Players

let
  Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content], 
  GroupBy = Table.Group(
    Source, 
    {"Team"}, 
    {
      {
        "tbl", 
        each 
          let
            ListCol = {"Team"}
              & List.Transform({1 .. Table.RowCount(_)}, each "Player " & Text.From(_))
              & {"HighestScorePlayer", "HighestScore"}, 
            MaxScore = List.Max(_[Runs Scored]), 
            PlayerMax = Text.Combine(
              Table.SelectRows(_, each [Runs Scored] = MaxScore)[Player], 
              ","
            )
          in
            Table.FromRows(
              {{_[Team]{0}} & List.Combine(Table.ToRows(_[[Player]])) & {PlayerMax} & {MaxScore}}, 
              ListCol
            )
      }
    }
  ), 
  ReorderColumns = 
    let
      a = Table.ColumnNames(Table.Combine(GroupBy[tbl])), 
      b = {"Team"} & List.Sort(List.Select(a, each Text.Contains(_, "Player ")), 0)
    in
      b & List.Difference(a, b), 
  Combine = Table.Combine(GroupBy[tbl], ReorderColumns)
in
  Combine

=LET(t,A2:A10,p,B2:B10,s,C2:C10,u,UNIQUE(t),m,MAXIFS(s,t,u),q,TEXTSPLIT(TEXTJOIN(N(t=DROP(VSTACK(t,0),1)),0,p),1,0,,,""),
VSTACK(HSTACK(A1,B1&SEQUENCE(,COLUMNS(q)),"Highest Scor"&{"ing Player","e"}),
HSTACK(u,q,MAP(u&m,LAMBDA(x,ARRAYTOTEXT(FILTER(p,x=t&s)))),m)))

=LET(c,A2:A10,r,MAX(COUNTIF(c,c)),REDUCE(HSTACK(A1,
B1&SEQUENCE(,r),"Highest Scor"&{"ing Player","e"}),
UNIQUE(c),LAMBDA(a,v,VSTACK(a,LET(i,FILTER(B2:B10,c=v),
b,FILTER(C2:C10,c=v),m,MAX(b),HSTACK(v,EXPAND(
TOROW(i),,r,""),ARRAYTOTEXT(FILTER(i,m=b)),m))))))

=LET(e,A2:A10,c,C2:C10,s,SEQUENCE(,
MAX(COUNTIFS(e,e))),REDUCE(HSTACK(A1,B1&s,J1,K1),
UNIQUE(e),LAMBDA(a,v,LET(J,FILTER(B2:B10,e=v),
U,FILTER(c,e=v),m,MAX(U),VSTACK(a,HSTACK(v,IFERROR(
INDEX(J,s),""),ARRAYTOTEXT(FILTER(J,U=m)),m))))))

=LET(t,A2:A10,d,SORT(A2:C10,{1,3},{1,-1}),f,LAMBDA(x,ARRAYTOTEXT(FILTER(INDEX(d,,2),TAKE(d,,-1)=@x))),g,VSTACK(J1:K1,DROP(GROUPBY(TAKE(d,,1),TAKE(d,,-1),HSTACK(f,SINGLE),,0),1,1)),pv,HSTACK(PIVOTBY(t,B1&MAP(t,LAMBDA(x,COUNTIF(A2:x,x))),B2:B10,SINGLE,,0,,0),g),SORTBY(pv,IFNA(XMATCH(TAKE(pv,,1),t),)))

=LET(Te,A2:A10,P,B2:B10,R,C2:C10,TU,UNIQUE(Te),HS,MAXIFS(R,Te,TU),HSP,MAP(HS,TU,LAMBDA(A,T,TEXTJOIN(", ",,FILTER(P,R&Te=A&T)))),PA,TEXTJOIN("@",,MAP(TU,LAMBDA(T,TEXTJOIN(";",,FILTER(P,Te=T))))),HSTACK(TU,IFNA(TEXTSPLIT(PA,";","@"),""),HSP,HS))

=LET(T,A2:C10,Te,TAKE(T,,1),P,CHOOSECOLS(T,2),R,TAKE(T,,-1),TU,UNIQUE(Te),HS,MAXIFS(R,Te,TU),HSP,MAP(HS,TU,LAMBDA(A,T,TEXTJOIN(", ",,FILTER(P,R&Te=A&T)))),PA,TEXTJOIN("@",,MAP(TU,LAMBDA(T,TEXTJOIN(";",,FILTER(P,Te=T))))),VSTACK(HSTACK("Team","Player"&{1,2,3,4},"Highest Scoring Player","Highest Score"),HSTACK(TU,IFNA(TEXTSPLIT(PA,";","@"),""),HSP,HS)))

=LET(t, A2:A10, p, B2:B10, r, C2:C10, u, UNIQUE(t), m, MAXIFS(r, t, u), c, MAP(t, LAMBDA(a, COUNTIF(A2:a,a))), x, XLOOKUP(u&SEQUENCE( , 4) , t&c, p, ""), h, MAP(m, LAMBDA(i, ARRAYTOTEXT(FILTER(p, r=i)))), HSTACK(u, x, h, m))

=LET(
 u, UNIQUE(A2:A10),
 r, MAX(MAP(u, LAMBDA(a, SUM(N(A2:A10 = a))))),
 HSTACK(
 u,
 DROP(
 REDUCE(
 "",
 u,
 LAMBDA(a, v,
 VSTACK(
 a,
 LET(
 t, WRAPCOLS(TOCOL(FILTER(B2:C10, A2:A10 = v)), 2),
 a, TAKE(t, 1),
 b, TAKE(t, -1),
 c, HSTACK(TEXTJOIN(",", , IF(MAX(b) = b, a, "")), MAX(b)),
 HSTACK(EXPAND(a, , r, ""), c)
 )
 )
 )
 ),
 1
 )
 )
)

=LET(
 u, UNIQUE(A2:A10),
 r, MAX(MAP(u, LAMBDA(a, SUM(N(A2:A10 = a))))),
 VSTACK(
 HSTACK(A1, "Player" & SEQUENCE(, r), "Highest Scoring player", "Highest score"),
 HSTACK(
 u,
 DROP(
 REDUCE(
 "",
 u,
 LAMBDA(a, v,
 VSTACK(
 a,
 LET(
 t, WRAPCOLS(TOCOL(FILTER(B2:C10, A2:A10 = v)), 2),
 a, TAKE(t, 1),
 b, TAKE(t, -1),
 c, HSTACK(TEXTJOIN(",", , IF(MAX(b) = b, a, "")), MAX(b)),
 HSTACK(EXPAND(a, , r, ""), c)
 )
 )
 )
 ),
 1
 )
 )
 )
)

=LET(
 t, A2:A10,
 p, B2:B10,
 rs, C2:C10,
 u, UNIQUE(t),
 cnt, 3 + MAX(MAP(u, LAMBDA(x, SUM(N(t = x))))),
 MAKEARRAY(
 ROWS(u),
 cnt,
 LAMBDA(r, c,
 INDEX(
 LET(
 _r, INDEX(u, r),
 _a, TOROW(IF(t = _r, p, x), 3),
 _b, TOROW(IF(t = _r, rs, x), 3),
 IFNA(HSTACK(_r, EXPAND(_a, , 4), TEXTJOIN(", ", , IF(MAX(_b) = _b, _a, "")), MAX(_b)), "")
 ),
 c
 )
 )
 )
)

=LET(t,A2:A10,p,B2:B10,s,C2:C10,u,UNIQUE(t),F,LAMBDA(x,IFNA(DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,TOROW(FILTER(x,t=b))))),1),"")),m,BYROW(F(s),MAX),n,BYROW(IF(F(s)=m,F(p),""),LAMBDA(y,TEXTJOIN(", ",,y))),HSTACK(u,F(p),n,m))
With header
=LET(t,A2:A10,p,B2:B10,s,C2:C10,u,UNIQUE(t),F,LAMBDA(x,IFNA(DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,TOROW(FILTER(x,t=b))))),1),"")),m,BYROW(F(s),MAX),n,BYROW(IF(F(s)=m,F(p),""),LAMBDA(y,TEXTJOIN(", ",,y))),VSTACK(HSTACK(A1,"Player"&SEQUENCE(,COLUMNS(F(p))),J1,K1),HSTACK(u,F(p),n,m)))

=LET(a,A2:A10,b,B2:B10,c,C2:C10,d,UNIQUE(a),REDUCE(E1:K1,d, LAMBDA(x,y,LET(e,MAX(FILTER(c,a=y)),f,MAX(COUNTIF(a,d)),VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(b,a=y)),,f,""),TEXTJOIN(", ",,FILTER(b,c=e)),e))))))

=LET(a,A2:A10,b,B2:B10,c,C2:C10,d,UNIQUE(a),e,MAX(COUNTIF(a,d)), REDUCE(HSTACK("Team","Player"&SEQUENCE(,e),"Highest Scoring Player","Highest Score"),d,LAMBDA(x,y,LET(f,MAX(FILTER(c,a=y)), VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(b,a=y)),,e,""),TEXTJOIN(", ",,FILTER(b,c=f)),f))))))

=LET(u,A2:A10,v,B2:B10,w,C2:C10,z,UNIQUE(u),
f,LAMBDA(a,b,c,TOROW(FILTER(a,b=c))),
a,IFNA(DROP(REDUCE("",z,LAMBDA(x,y,VSTACK(x,f(v,u,y)))),1),""),
b,DROP(REDUCE("",z,LAMBDA(x,y,LET(p,f(v,u,y),q,f(w,u,y),mx,MAX(q),VSTACK(x,HSTACK(ARRAYTOTEXT(f(p,q,mx)),mx))))),1),
hdr,HSTACK("Team","Player"&SEQUENCE(,COLUMNS(a)),"Highest Scoring Player","Highest Score"),
d,VSTACK(hdr,HSTACK(z,a,b)),
d)

=LET(t,A2:A10,p,B2:B10,г,C2:C10,u,UNIQUE(t),i,IFNA(DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,LET(f,FILTER(p,t=y),HSTACK(y,TOROW(f)))))),1),""),rd,DROP(REDUCE("",UNIQUE(t),LAMBDA(x,y,VSTACK(x,LET(f,FILTER(r,t=y),m,MAX(f),fl,FILTER(p,t=y),HSTACK(MAP(m,LAMBDA(z,ARRAYTOTEXT(FILTER(fl,f=z)))),m))))),1),h,HSTACK(i,rd),v,VSTACK(HSTACK(A1,"Players"&SEQUENCE(,COLUMNS(DROP(i,,1))),"Highest"&{"Scoring Player","Score")),h),v)

=x)))),VSTACK({"Team","Player1","Player2","Player3","Player4","Highest Scoring Player","HighestScore"},SORT(HSTACK(CHOOSECOLS(a,1),b,d,c),4)))

=LET
(
HEADERS,HSTACK("Team","Player  " & SEQUENCE(1,MAX(LET(A,A3:A11,MAP(UNIQUE(A),LAMBDA(X,COUNTIF(A,X)))))),"Highest Scoring Player", "Highest Score"), 
TEAM,A3:A11, 
PLAYER,B3:B11, 
SCORE,C3:C11, 
UNQTEAM,UNIQUE(TEAM), 
PLAYERLIST,MAP(UNQTEAM,LAMBDA(X,TOROW(FILTER(PLAYER,TEAM=X)))), 
MAXSCORE,MAP(UNQTEAM,LAMBDA(Y,MAX(TRANSPOSE(FILTER(SCORE,TEAM=Y))))), 
NAME,MAP(MAXSCORE,LAMBDA(Z,IF(COUNTIF(SCORE,Z)>1,TEXTJOIN(",",,FILTER(PLAYER,SCORE=Z)),FILTER(PLAYER,SCORE=Z)))), 
VSTACK(HEADERS,HSTACK(UNQTEAM,PLAYERLIST,NAME,MAXSCORE)))

= df.groupby('Team')['Player'].apply(list).reset_index()
team_max_runs = df.groupby('Team')['Runs Scored'].max().reset_index()
highest_scorers = df[df['Runs Scored'].eq(df.groupby('Team')['Runs Scored'].transform('max'))]
team_high_scorers = highest_scorers.groupby('Team')['Player'].apply(list).reset_index()
merged_df = pd.merge(team_players, team_max_runs, on='Team')
final_df = pd.merge(merged_df, team_high_scorers, on='Team', suffixes=('_all', '_high_scorers'))

=LET(t,A2:A10,Ut,UNIQUE(A2:A10),r,C2:C10,p,B2:B10,tb,IFERROR(TEXTSPLIT(TEXTJOIN(",",,MAP(Ut,LAMBDA(x,x&":"&TEXTJOIN(":",,FILTER(p,t=x))))),":",","),""),hs,MAP(Ut,LAMBDA(x,MAX(TAKE(FILTER(r,t=x),,-1)))),hsp,MAP(Ut,hs,LAMBDA(x,y,TEXTJOIN(", ",,TAKE(FILTER(p,((t=x)*(r=y))),,-1)))),HSTACK(tb,hsp,hs))

=LET(t,UNIQUE(A2:A10),REDUCE(E1:K1,t,LAMBDA(s,v,VSTACK(s,
 LET(a,FILTER(B2:C10,A2:A10=v),
 I,CHOOSECOLS(a,1),II,CHOOSECOLS(a,2),III,MAX(II),P,TOROW(TEXTJOIN(", ",,FILTER(I,II=III))),
 HSTACK(v,EXPAND(TOROW(I),,4," "),P,III))))))

=LET(t,A2:A10,u,UNIQUE(t),f,REDUCE(E1:K1,u,LAMBDA(a,x,LET(f,FILTER(B2:C10,t=x),i,CHOOSECOLS(f,1),j,CHOOSECOLS(f,2),m,MAX(j),w,TEXTJOIN(", ",,FILTER(i,j=m)),s,HSTACK(x,EXPAND(TOROW(i),,4,""),w,m),VSTACK(a,s)))) ,f)

Just been exploring LAMBDA functionality recently.  Versatile.

import pandas as pd
input = pd.read_excel("PQ_Challenge_179.xlsx",  usecols="A:C", nrows=10)
test = pd.read_excel("PQ_Challenge_179.xlsx",  usecols="E:K", nrows=3)
test = test.rename(columns={"Team.1": "Team"}).sort_values("Team").reset_index(drop=True)
r1 = input.copy()
r1 = r1[["Team", "Player"]]
r1["row"] = r1.groupby("Team").cumcount()+1
r1 = r1.pivot(index="Team", columns="row", values="Player").add_prefix("Player").reset_index()
r2 = input.copy()
r2["Max"] = r2.groupby("Team")["Runs Scored"].transform("max")
r2 = r2[r2["Runs Scored"] == r2["Max"]]
r2 = r2.groupby("Team").agg({"Player": lambda x: ", ".join(x), "Max": "first"}).reset_index()
result = r1.merge(r2, on="Team", how="left")
result.columns = test.columns
print(result.equals(test)) # True
                    
                  

import pandas as pd
file_path = 'PQ_Challenge_179.xlsx'
df = pd.read_excel(file_path, usecols='A:C')
# Perform data transformation and cleansing
max_players = df.groupby('Team')['Player'].count().max()
values = []
for team in df['Team'].unique():
 players = df['Player'][df['Team'] == team].tolist()
 high_score = df.groupby('Team')['Runs Scored'].max()[team]
 hs_players = ', '.join(df['Player'][(df['Team'] == team) & (df['Runs Scored'] == high_score)].tolist())
 players = [team] + players + [''] * (max_players - len(players)) + [hs_players, high_score]
 values.append(players)
# Make players columns dynamic
names = ['Team'] + ['Player' + str(x) for x in range(1, max_players + 1)] + ['Highest Scoring Player', 'Highest Score']
df = pd.DataFrame(values, columns=names)
df
                    
                  

library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_179.xlsx", range = "A1:C10")
test = read_excel("Power Query/PQ_Challenge_179.xlsx", range = "E1:K4")
r1 = input %>%
 select(-`Runs Scored`) %>%
 mutate(player = paste0("Player",row_number()), .by = Team) %>%
 pivot_wider(names_from = player, values_from = Player)
r2 = input %>%
 mutate(max = max(`Runs Scored`), .by = Team) %>%
 filter(`Runs Scored` == max) %>%
 summarise(`Highest Scoring Player` = paste0(Player, collapse = ", "),
 `Highest Score` = unique(`Runs Scored`), .by = Team)
result = r1 %>%
 left_join(r2, by = "Team")
                    
                  

library(tidyverse)
df <- data.frame(
 Team = c("Atlanta Braves", "Atlanta Braves", "Atlanta Braves", "New York Yankees", "New York Yankees", "Chicago Cubs", "Chicago Cubs", "Chicago Cubs", "Chicago Cubs"),
 Player = c("A", "B", "C", "D", "E", "F", "G", "H", "I"),
 Runs_Scored = c(4, 13, 44, 48, 37, 9, 26, 16, 26)
)
Pivoted <- df |>
 arrange(Player) |>
 mutate(Index = row_number(), .by = Team) |>
 pivot_wider(
 id_cols = Team, names_from = Index,
 values_from = Player, names_prefix = "Player "
 )
HS <- df |>
 summarise(
 `Highest Score` = max(Runs_Scored),
 .by = Team
 )
HSP <- df |>
 slice_max(order_by = Runs_Scored, n = 1, by = Team, with_ties = TRUE) |>
 summarise(HSP = str_c(Player, collapse = ","), .by = Team)
Results <- merge(HSP, HS)
Answer <- Pivoted |>
 left_join(Results, by = "Team")
Answer
Answer