Generate Output Table from Data

let
  Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]), 
  Ans = Table.FromRows(
    List.Combine(
      List.Transform(
        List.Skip(Source), 
        each List.Transform(
          List.Split(List.Skip(_), 4), 
          (r) => {_{0}} & r & {List.Sum(List.Skip(r, 2))}
        )
      )
    ), 
    List.Distinct(Source{0}) & {"Total"}
  )
in
  Ans

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.FromRows(
    List.Combine(
      List.Transform(
        Table.ToRows(Source), 
        each List.Transform(
          List.Split(List.Skip(_), 4), 
          (t) => {_{0}} & t & {List.Sum(List.LastN(t, 2))}
        )
      )
    ), 
    List.FirstN(Table.ColumnNames(Source), 5) & {"Total"}
  )
in
  S

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Group"}, "Attribute", "Value"), 
  AttTrans = Table.TransformColumns(
    Unpivot, 
    {
      "Attribute", 
      each 
        if Text.StartsWith(_, "P") then
          "Period"
        else if Text.StartsWith(_, "M") then
          Text.Start(_, 6)
        else
          "Score"
    }
  ), 
  Count = Table.TransformColumns(
    Table.AddIndexColumn(AttTrans, "Index", 1, 1 / 4, Int64.Type), 
    {"Index", each Number.RoundDown(_)}
  ), 
  Pivot = Table.Pivot(Count, List.Distinct(Count[Attribute]), "Attribute", "Value"), 
  Total = Table.RemoveColumns(Table.AddColumn(Pivot, "Total", each [Marks1] + [Marks2]), {"Index"})
in
  Total

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Group"}, "Att", "Val"), 
  Att = Table.TransformColumns(
    Unpivot, 
    {
      "Att", 
      each 
        if Text.StartsWith(_, "M") then
          Text.Start(_, 6)
        else if Text.StartsWith(_, "S") then
          "Score"
        else
          "Period"
    }
  ), 
  Integer = Table.TransformColumns(
    Table.AddIndexColumn(Att, "Idx", 0), 
    {{"Idx", each Number.IntegerDivide(_, 4)}}
  ), 
  Pivot = Table.RemoveColumns(
    Table.Pivot(Integer, List.Distinct(Integer[Att]), "Att", "Val"), 
    "Idx"
  ), 
  Sol = Table.AddColumn(Pivot, "Total", each [Marks1] + [Marks2])
in
  Sol

let
  Fonte = Tabela1, 
  add = Table.AddColumn(
    Fonte, 
    "Tab", 
    each Table.FromRows(
      List.Transform(
        List.Split(List.RemoveFirstN(Record.FieldValues(_), 1), 4), 
        (x) => x & {List.Sum(List.LastN(x, 2))}
      ), 
      List.LastN(List.FirstN(Table.ColumnNames(Fonte), 5), 4) & {"Total"}
    )
  )[[Group], [Tab]], 
  res = Table.ExpandTableColumn(add, "Tab", Table.ColumnNames(add[Tab]{0}))
in
  res

let
  Source = Excel.CurrentWorkbook(){[Name = "tData113"]}[Content], 
  Transform = Table.TransformRows(
    Source, 
    each 
      let
        _L = Record.ToList(_), 
        _R = List.Accumulate(
          List.Split(List.Skip(_L), 4), 
          {}, 
          (s, c) => s & {{_L{0}} & c & {List.Sum(List.LastN(c, 2))}}
        )
      in
        _R
  ), 
  ToTable = Table.FromRows(
    List.Combine(Transform), 
    List.FirstN(Table.ColumnNames(Source), 5) & {"Total"}
  )
in
  ToTable

let
  Source = Excel.CurrentWorkbook(){[Name = "Table4"]}[Content], 
  S1 = Table.UnpivotOtherColumns(Source, {"Group"}, "Attribute", "Value"), 
  S2 = Table.AddIndexColumn(S1, "Index", 1, 1, Int64.Type), 
  S3 = Table.AddColumn(S2, "Custom", each Number.Mod([Index], 4)), 
  S4 = Table.AddColumn(
    S3, 
    "Custom.1", 
    each 
      if [Custom] = 1 then
        "Period"
      else if [Custom] = 2 then
        "Score"
      else if [Custom] = 3 then
        "Marks1"
      else if [Custom] = 0 then
        "Marks2"
      else
        null
  ), 
  S5 = Table.RemoveColumns(S4, {"Attribute", "Index", "Custom"}), 
  S6 = Table.Group(
    S5, 
    {"Custom.1"}, 
    {{"Count", each _}}, 
    GroupKind.Local, 
    (c, n) => if n[Custom.1] = "Period" then 1 else 0
  )[Count], 
  S7 = List.Transform(S6, each Table.Pivot(_, List.Distinct(_[Custom.1]), "Custom.1", "Value")), 
  S8 = Table.Combine(S7), 
  S9 = Table.AddColumn(S8, "Total", each [Marks1] + [Marks2], type number)
in
  S9

let
 Source = Excel.CurrentWorkbook(){[Name="Problem"]}[Content],
 DemoteHeaders = Table.DemoteHeaders(Source),
 Transpose = Table.Transpose(DemoteHeaders),

 RemoveNr = Table.TransformColumns(Transpose,{{"Column1", each Text.Remove(_,{"1".."9"}), type text}}),

 Groups = Table.First( RemoveNr),

 Skip = Table.Skip(RemoveNr,1),

 Index = Table.AddIndexColumn(Skip, "Index", 1, 1, Int64.Type),


 GroupID = Table.AddColumn(Index, "GroupID", each if [Column1] = "Period" then [Index] else null),
 FillDown = Table.FillDown(GroupID,{"GroupID"}),

 GroupTransformations = Table.Group(FillDown, {"GroupID"}, {{"GroupedTables", 
each let

RemoveCols = Table.RemoveColumns(_,{"Index","GroupID"}),

AddGroup = Table.InsertRows(RemoveCols,0,{Groups} ),

Pivot = Table.PromoteHeaders( Table.Transpose(AddGroup) ),

in Table.AddColumn(Pivot,"Total", each [Marks] + [Marks_1])
}}),

 Combine= Table.Combine(GroupTransformations[GroupedTables]),
 RenameMarks = Table.RenameColumns(Combine,{{"Marks", "Marks1"}, {"Marks_1", "Marks2"}}),
 Reorder = Table.Sort(RenameMarks,{{"Group", Order.Ascending}
 RemoveIndex = Table.RemoveColumns(Reorder,{"Index"})
in
 RemoveIndex


                    
                  
          

=LET(d,WRAPROWS(TOCOL(B2:M4),4),VSTACK(HSTACK(A1:E1,"Total"),HSTACK(TOCOL(IF(SEQUENCE(,3),A2:A4)),d,MMULT(d,{0;0;1;1}))))

=LET(
i,A1:M1,
b,B2:M4,
n,RIGHT(TAKE(i,,-1))+2,
x,COLUMNS(b)/n,
V,WRAPROWS(TOROW(b),n),
IFNA(VSTACK(TAKE(i,,n+1),HSTACK(TOCOL(IFNA(
A2:A4,SEQUENCE(,x))),V,
MMULT(DROP(V,,2),SEQUENCE(n-2)^0))),"Total"))

=LET(V,WRAPROWS(TOROW(B2:M4),4),IFNA(VSTACK(A1:E1,HSTACK(TOCOL(IFNA(A2:A4,SEQUENCE(,3))),V,DROP(V,,3)+INDEX(V,,3))),"Total"))

=LET(V,WRAPROWS(TOROW(B2:M4),4),IFNA(VSTACK(A1:E1,HSTACK(TOCOL(IFNA(A2:A4,SEQUENCE(,3))),V,MMULT(DROP(V,,2),{1;1}))),"Total"))

=LET(V,WRAPROWS(TOROW(B2:M4),4),HSTACK(TOCOL(IFNA(A2:A4,SEQUENCE(,3))),V,MMULT(DROP(V,,2),{1;1})))

=LET(_g, A2:A4, _f,ROW(_g), _c,COLUMNS(B2:M4), _m, WRAPROWS(TOCOL(B2:M4), _c/COUNT(_f)), _s,MMULT(TAKE(_m, ,-2), {1; 1}), _r, TOCOL(REPT(_g, TOROW(_f)^0)), VSTACK(HSTACK(A1:E1, "Total"), HSTACK(_r,_m,_s)))

=LET(a,REDUCE(A1:E1,UNIQUE(A2:A4),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,WRAPROWS(TOCOL(FILTER(B2:M4,A2:A4=y)),4)),y)))),b,BYROW(TAKE(a,,-2),SUM),HSTACK(a,IF(b,b,"Total")))

=LET(
 _a, TOCOL(IF(SEQUENCE(, 3), A2:A4)),
 _b, WRAPROWS(TOCOL(B2:M4), 4),
 _c, BYROW(TAKE(_b, , -2), LAMBDA(x, SUM(x))),
 HSTACK(_a, _b, _c)
)

=LET(a,DROP(REDUCE("",
SEQUENCE(ROWS(B2:M4)), LAMBDA(a,v,VSTACK(a,
TRANSPOSE(WRAPCOLS(INDEX(B2:M4,v,),4))))),1),
b,TOCOL(IF(SEQUENCE(,3),A2:A4)),
HSTACK(b,a,BYROW(TAKE(a,,-2), LAMBDA(x,SUM(x)))))

= Repivotλ(periodData, group)

= LET(
 SUMλ, LAMBDA(x, SUM(x)),
 repivotted, WRAPROWS(TOCOL(periodData), 4),
 groupHeader, TOCOL(IF({1,1,1}, group)),
 total, BYROW(TAKE(repivotted,, -2), SUMλ),
 HSTACK(groupHeader, repivotted, total)
 )

={A1:E1,"Total";SORT(REDUCE(TOCOL(,1),SEQUENCE(COUNTIF(1:1,"P*"),1,2,4),LAMBDA(a,i,LET(x,CHOOSECOLS(A2:4,1,SEQUENCE(4,1,i)),VSTACK(a,{x,MMULT(CHOOSECOLS(x,4,5),{1;1})})))),1,1)

=LET(d,WRAPROWS(TOCOL(B3:M5),4),VSTACK(HSTACK(A2:E2,"Total"),HSTACK(INDEX(A3:A5,SEQUENCE(9,,,1/3)),d,INDEX(d,,3)+INDEX(d,,4))))

=LET(s,A2:A4,t,DROP(REDUCE("",s,LAMBDA(a,x,VSTACK(a,LET(d,
WRAPROWS(OFFSET(x,,1,1,12),4),IFNA(HSTACK(x,d),x))))),1),
VSTACK(HSTACK(A1:E1,"Total"),HSTACK(t,BYROW(t,LAMBDA(y,SUM(TAKE(y,,-2)))))))

=LET(a,TOCOL(IF(SEQUENCE(,3),A2:A4)),b,WRAPROWS(TOCOL(B2:M4),4),c,BYROW(CHOOSECOLS(b,{-1,-2}),LAMBDA(a,SUM(a))),HSTACK(a,b,c))

I just noticed that instead of
"BYROW(CHOOSECOLS(b,{-1,-2})"
I could have used
BYROW(TAKE(b,,-2)
                    
                  

df =xl("A1:M4", headers=True)
up =pd.DataFrame([np.hstack((r[:1],r[i:i+4],sum(r[i+2:i+4]))) for r in df.values for i in range(1,len(r),4)])
up.columns =list(df.columns[:5])+['Total']
up
                    
                  

np.vstack([["Group","Period","Score","Marks1","Marks2","Total"],[[a[0], a[4*i+1], a[4*i+2], a[4*i+3], a[4*i+4], a[4*i+3]+a[4*i+4]] for a in xl("A1:M4", headers=True).values for i in range((len(a)-1)//4)]])
I'm saving all my Python solutions to these challenges here if anyone wants to explore:
bit.ly/PythonLearningFolder