Top Initials in Names

Insert the total row containing count of employees and sum of salary at the bottom of each dept groups. Max bonus is 10% of the salary. At the end of the table, count and salary is for entire table as a whole.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 165
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Department Totals and Summary with Power Query

Power Query solution 1 for Department Totals and Summary, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  T = (_, h) =>
    let
      H = {"Total", "Grand Total"}, 
      R = Table.ToColumns(_)
    in
      Table.FromColumns(
        List.Transform(List.Positions(R), each {{H{h}, List.Count(R{1}), List.Sum(R{2})}{_}}), 
        Table.ColumnNames(_)
      ), 
  P = Table.Combine(
    Table.Group(
      Source, 
      {"Dept"}, 
      {{"A", each _ & T(_, 0)}}, 
      0, 
      (b, e) => Byte.From(e[Dept] <> null)
    )[A]
  ), 
  S = Table.AddColumn(P & T(Source, 1), "Max Bonus", each [Salary] * 0.1)
in
  S

Power Query solution 2 for Department Totals and Summary, proposed by Aditya Kumar Darak 🇮🇳:

let
  MyFunction = (Table, Total) =>
    Table.FromRecords(
      {
        [
          Dept      = Total, 
          Emp       = Table.RowCount(Table), 
          Salary    = List.Sum(Table[Salary]), 
          Max Bonus = List.Sum(Table[Max Bonus])
        ]
      }
    ), 
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  MaxBonus = Table.AddColumn(Source, "Max Bonus", each [Salary] * 0.1), 
  Group = Table.Group(
    MaxBonus, 
    "Dept", 
    {"Group", each _ & MyFunction(_, "Total")}, 
    0, 
    (x, y) => Number.From(y <> null)
  ), 
  Final = Table.Combine(Group[Group]), 
  Return = Final & MyFunction(MaxBonus, "Grand Total")
in
  Return

Power Query solution 3 for Department Totals and Summary, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  FD = Table.FillDown(Source, {"Dept"}), 
  Group = Table.Combine(
    Table.Group(
      FD, 
      {"Dept"}, 
      {
        {
          "A", 
          each 
            let
              a = Table.AddColumn(_, "Max Bonus", each 1.1 * [Salary]), 
              b = Table.ToColumns(a), 
              c = {{b{0}{0}} & List.Repeat({null}, List.Count(b{0}) - 1)} & List.Skip(b), 
              d = {c{0} & {"Total"}}
                & {c{1} & {List.Count(c{1})}}
                & {c{2} & {List.Sum(c{2})}}
                & {c{3} & {List.Sum(c{3})}}, 
              e = Table.FromColumns(d, Table.ColumnNames(Source) & {"Max Bonus"})
            in
              e
        }
      }
    )[A]
  )
in
  Group
    & Table.FromRows(
      {
        {
          "Grand Total", 
          List.Count(Source[Emp]), 
          List.Sum(Source[Salary]), 
          List.Sum(Source[Salary]) * .1
        }
      }, 
      Table.ColumnNames(Source) & {"Max Bonus"}
    )

Power Query solution 4 for Department Totals and Summary, proposed by Luan Rodrigues:

let
 Fonte = Tabela1,
 col = Table.AddColumn(Fonte, "Personalizar", each [Dept]),
 pd = Table.FillDown(col,{"Personalizar"}),
 gp = Table.Combine(Table.Group(pd, {"Personalizar"}, {{"tab", each 
[a = Table.RemoveColumns(Table.AddColumn(_,"Max Bonus", each [Salary]*0.1),{"Personalizar"}), 
b = a & hashtag#table(Table.ColumnNames(a),{{"Total",List.Count(a[Emp]),List.Sum(a[Salary]),List.Sum(a[Max Bonus]) }} ) 
][b]}})[tab]),
 res = gp & Table.FromRows({let 
a = Table.SelectRows(gp, each [Dept]="Total") 
in
{"Grand Total", List.Sum(a[Emp]),List.Sum(a[Salary]),List.Sum(a[Max Bonus])}},Table.ColumnNames(gp) )
in
 res

Power Query solution 5 for Department Totals and Summary, proposed by Hussein SATOUR:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
DuplicatDept = Table.DuplicateColumn(Source, "Dept", "Dept - Copy"),
FillIt = Table.FillDown(DuplicatDept,{"Dept - Copy"}),
Group = Table.Group(FillIt, {"Dept - Copy"}, {{"Count", each _, type table [Dept=nullable text, Emp=text, Salary=number, #"Dept - Copy"=text]}}),
NewTab = Table.AddColumn(Group, "Custom", each let
 a = Table.RemoveColumns([Count], {"Dept - Copy"}),
 b = hashtag#table({"Dept", "Emp", "Salary"},
 {{"Total", Table.RowCount(a), List.Sum(a[Salary])}})
 in Table.Combine({a, b})),
RemovCols = Table.RemoveColumns(NewTab,{"Dept - Copy", "Count"}),
Expand = Table.ExpandTableColumn(RemovCols, "Custom", {"Dept", "Emp", "Salary"}, {"Dept", "Emp", "Salary"}),
FilterTot = Table.SelectRows(Expand, each ([Dept] = "Total")),
SumGeneral = List.Sum(FilterTot[Salary]),
NbGeneral = List.Sum(FilterTot[Emp]),
Custom1 = hashtag#table({"Dept", "Emp", "Salary"}, {{"Grand Total", NbGeneral, SumGeneral}}),
#"Appended Query" = Table.Combine({Expand, Custom1})
in #"Appended Query"

Power Query solution 6 for Department Totals and Summary, proposed by Ramiro Ayala Chávez:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  TT = Table.TransformColumns, 
  a = Table.FillDown(S, {"Dept"}), 
  b = Table.Group(a, {"Dept"}, {{"G", each _}})[[G]], 
  c = TT(b, {"G", each Table.AddColumn(_, "D", each S[Dept]{List.PositionOf(S[Emp], [Emp])})}), 
  d = TT(
    c, 
    {"G", each Table.RenameColumns(Table.SelectColumns(_, {"D", "Emp", "Salary"}), {"D", "Dept"})}
  ), 
  e = TT(d, {"G", each Table.AddColumn(_, "Max Bonus", each [Salary] * 0.1)}), 
  g = TT(
    e, 
    {
      "G", 
      each {"Total"}
        & List.Transform(List.RemoveLastN(List.Skip(Table.ToColumns(_)), 2), List.Count)
        & List.Transform(List.Skip(Table.ToColumns(_), 2), List.Sum)
    }
  ), 
  h = TT(g, {"G", each Table.FromRows({_}, Table.ColumnNames(a) & {"Max Bonus"})}), 
  i = List.Generate(
    () => [i = 0], 
    each [i] < Table.RowCount(e), 
    each [i = [i] + 1], 
    each e{[i]}[G] & h{[i]}[G]
  ), 
  j = Table.Combine(i), 
  k = {"Grand Total"}
    & {
      List.Sum(List.Select(List.RemoveLastN(List.Skip(Table.ToColumns(j)), 2){0}, each _ is number))
    }
    & List.Transform(List.Skip(Table.ToColumns(S), 2), List.Sum), 
  l = k & {k{2} * 0.1}, 
  Sol = j & Table.FromRows({l}, Table.ColumnNames(a) & {"Max Bonus"})
in
  Sol

Power Query solution 7 for Department Totals and Summary, proposed by Eric Laforce:

let
 Source = Excel.CurrentWorkbook(){[Name="tData165"]}[Content],
 CN = Table.ColumnNames(Source),
 fxTTotal = (t, sTotal)=> hashtag#table(CN, {{sTotal, List.Count(t[Emp]), List.Sum(t[Salary])}}),
 Group = Table.Group(Source, "Dept", {"All", each _ & fxTTotal(_, "Total")},0, (p,c)=> Number.From(c<>null)),
 Add_GT = Table.Combine(Group[All]) & fxTTotal(Source, "Grand Total"),
 Add_B = Table.AddColumn(Add_GT, "Max Bonus", each [Salary]*0.1)
in
 Add_B

Power Query solution 8 for Department Totals and Summary, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  T = Table.FillDown(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], {"Dept"}), 
  A = Table.AddColumn(T, "Max Bonus", each 0.1 * [Salary]), 
  B = Table.Group(
    A, 
    {}, 
    {
      {"Emp", each Table.RowCount(_)}, 
      {"Salary", each List.Sum([Salary])}, 
      {"Max Bonus", each List.Sum([Max Bonus])}
    }
  ), 
  X = Table.AddColumn(B, "Dept", each "Z"), 
  C = Table.Group(
    A, 
    {"Dept"}, 
    {
      {"Emp", each Table.RowCount(_)}, 
      {"Salary", each List.Sum([Salary])}, 
      {"Max Bonus", each List.Sum([Max Bonus])}
    }
  ), 
  M = Table.TransformColumns(C, {{"Dept", each _ & " Total"}}), 
  O = Table.Combine({A, M, X}), 
  G = Table.Group(
    O, 
    {"Dept"}, 
    {{"T", each _, type table [Dept = text, Emp = any, Salary = number, Max Bonus = number]}}
  ), 
  H = Table.AddColumn(G, "T2", each Table.AddIndexColumn([T], "In", 1, 1)), 
  R = Table.SelectColumns(H, {"T2"}), 
  E = Table.ExpandTableColumn(
    R, 
    "T2", 
    {"Dept", "Emp", "Salary", "Max Bonus", "In"}, 
    {"Dept", "Emp", "Salary", "Max Bonus", "In"}
  ), 
  S = Table.Sort(E, {{"Dept", Order.Ascending}, {"In", Order.Ascending}}), 
  N = Table.AddColumn(
    S, 
    "Dept.", 
    each 
      if [Dept] = "Z" then
        "Grand Total"
      else if Text.Contains([Dept], "Total") then
        "Total"
      else if [In] = 1 then
        [Dept]
      else
        null
  ), 
  F = Table.SelectColumns(N, {"Dept.", "Emp", "Salary", "Max Bonus"})
in
  F

Power Query solution 9 for Department Totals and Summary, proposed by Venkata Rajesh:

let
 Source       = Data,
 #"Grand Total"   = hashtag#table(type table [Dept= text, Emp= text, Salary= number], {{"Grand Total", List.Count(Source[Emp]), List.Sum(Source[Salary])}}),
 #"Grouped Rows"  = Table.Group(Source, {"Dept"}, {{"Table", each _ & hashtag#table({"Dept", "Emp", "Salary"}, {{"Total", List.Count(_[Emp]), List.Sum(_[Salary])}}), type table [Dept= text, Emp= text, Salary= number]}}),
 #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Dept"}),
 #"Expanded Table" = Table.ExpandTableColumn(#"Removed Columns", "Table", {"Dept", "Emp", "Salary"}) & #"Grand Total",
 #"Max Bonus"    = Table.AddColumn(#"Expanded Table", "Max Bonus", each [Salary] * 0.1, Int64.Type)
in
 #"Max Bonus"

Power Query solution 10 for Department Totals and Summary, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Filled Down" = Table.FillDown(Source, {"Dept"}), 
  #"Added Custom" = Table.AddColumn(#"Filled Down", "Custom", each [Salary] * 0.1), 
  #"Grouped Rows" = Table.Combine(
    Table.Group(
      #"Added Custom", 
      {"Dept"}, 
      {
        {
          "Count", 
          each Table.InsertRows(
            _, 
            Table.RowCount(_), 
            {
              [
                Dept   = "Total", 
                Emp    = List.Count(_[Emp]), 
                Salary = List.Sum(_[Salary]), 
                Custom = List.Sum(_[Custom])
              ]
            }
          )
        }
      }
    )[Count]
  ), 
  Custom1 = Table.InsertRows(
    #"Grouped Rows", 
    Table.RowCount(#"Grouped Rows"), 
    {
      [
        Dept   = "Total", 
        Emp    = List.Count(#"Added Custom"[Emp]), 
        Salary = List.Sum(#"Added Custom"[Salary]), 
        Custom = List.Sum(#"Added Custom"[Custom])
      ]
    }
  )
in
  Custom1

Power Query solution 11 for Department Totals and Summary, proposed by Glyn Willis:

let
  gt = Table.ToRecords(
    Table.Group(
      #"Changed Type", 
      {}, 
      {
        {"Dept", each "Grand Total"}, 
        {"Emp", each Table.RowCount(_), Int64.Type}, 
        {"Salary", each List.Sum([Salary]), type nullable number}, 
        {"Max Bonus", each List.Sum([Salary]) * 0.10, type nullable number}
      }
    )
  ), 
  fxgroup = (t) =>
    Table.Group(
      t, 
      {"Dept"}, 
      {
        {"Emp", each Table.RowCount(_), Int64.Type}, 
        {"Salary", each List.Sum([Salary]), type nullable number}, 
        {"Max Bonus", each List.Sum([Salary]) * 0.10, type nullable number}, 
        {"Data", each fxgroup2(_)}
      }, 
      GroupKind.Local, 
      (x, y) => Int64.From(y[Dept] <> null)
    ), 
  fxgroup2 = (t) =>
    Table.ToRecords(
      Table.Group(
        t, 
        {"Dept", "Emp", "Salary"}, 
        {{"Max Bonus", each [Salary]{0} * 0.10, type nullable number}}
      )
    ), 
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Dept", type text}, {"Emp", type text}, {"Salary", Int64.Type}}
  ), 
  #"Grouped Rows" = 
    let
      t = fxgroup(#"Changed Type"), 
      r = Table.ToRecords(t)
    in
      Table.FromRecords(
        List.Combine(
          List.Transform(
            r, 
            (x) =>
              x[Data]
                & {
                  Record.TransformFields(Record.RemoveFields(x, {"Data"}), {{"Dept", each "Total"}})
                }
          )
            & {gt}
        )
      )
in
  #"Grouped Rows"

Power Query solution 12 for Department Totals and Summary, proposed by Alexandra Popoff:

let
  Src = Table.FillDown(Excel.CurrentWorkbook(){[Name = "DB"]}[Content], {"Dept"}), 
  #"L Type" = Table.AddColumn(Src, "I T", each "A"), 
  #"ST Gp" = Table.Group(
    #"L Type", 
    {"Dept"}, 
    {{"Emp", each Table.RowCount(_), Int64.Type}, {"Salary", each List.Sum([Salary]), type number}}
  ), 
  #"ST Type" = Table.AddColumn(#"ST Gp", "I T", each "B"), 
  #"Ov Gp" = Table.Group(
    #"ST Type", 
    {}, 
    {{"Emp", each List.Sum([Emp]), type number}, {"Salary", each List.Sum([Salary]), type number}}
  ), 
  #"Ov Dept" = Table.AddColumn(#"Ov Gp", "Dept", each "Overall Total"), 
  #"L+ST" = Table.Combine({#"L Type", #"ST Type"}), 
  #"Sort" = Table.Sort(#"L+ST", {{"Dept", Order.Ascending}, {"I T", Order.Ascending}}), 
  #"Dept" = Table.AddColumn(
    #"Sort", 
    "Dept i", 
    each if [I T] = "B" then [Dept] & " Total" else [Dept]
  ), 
  #"Keep Col" = Table.SelectColumns(#"Dept", {"Dept i", "Emp", "Salary"}), 
  #"Dept Name" = Table.RenameColumns(#"Keep Col", {{"Dept i", "Dept"}}), 
  #"Add Total" = Table.Combine({#"Dept Name", #"Ov Dept"}), 
  #"Bonus" = Table.AddColumn(#"Add Total", "Max Bonus", each [Salary] * 0.1, type number)
in
  #"Bonus"

Solving the challenge of Department Totals and Summary with Excel

Excel solution 1 for Department Totals and Summary, proposed by Bo Rydobon 🇹🇭:

=LET(z,A2:C11,a,A2:A11,d,SCAN(,a,LAMBDA(a,v,IF(v>0,v,a))),e,DROP(VSTACK(d,0),1)<>d,
v,VSTACK(REDUCE(A1:C1,SEQUENCE(ROWS(a)),LAMBDA(c,n,VSTACK(c,TAKE(VSTACK(HSTACK(INDEX(a&"",n),DROP(INDEX(z,n,),,1)),
HSTACK("Total",SUM(N(INDEX(d,n,1)=d)),SUM((DROP(z,,2)*(INDEX(d,n,1)=d))))),1+@INDEX(e,n))))),HSTACK("Grand Total",ROWS(d),SUM(z))),
HSTACK(v,IFERROR(DROP(v,,2)/10,"Max Bonus")))

Excel solution 2 for Department Totals and Summary, proposed by Bo Rydobon 🇹🇭:

=LET(a,A2:A11,d,SCAN(,a,LAMBDA(a,v,IF(v>0,v,a))),g,GROUPBY(d,C2:C11,HSTACK(ROWS,SUM)),e,DROP(VSTACK(d,0),1)<>d,
w,WRAPROWS(TOCOL(VSTACK(HSTACK(A2:C11,IFS(e,CHOOSEROWS(g,XMATCH(d,TAKE(g,,1))))),TAKE(g,-1)),2),3),
VSTACK(F1:I1,IFNA(HSTACK(TOCOL(HSTACK(a&"",IFS(e,"Total")),2),DROP(w,,1),DROP(w,,2)/10),"Grand Total")))

Excel solution 3 for Department Totals and Summary, proposed by محمد حلمي:

=LET(v,A2:C11,s,SUM(v),u,"Total",j,SCAN(0,A2:A11,LAMBDA(a,d,
IF(d=0,a,d))),e,VSTACK(REDUCE(HSTACK(A1:C1,"Max Bonus"),
UNIQUE(j),LAMBDA(a,d,LET(i,FILTER(v,j=d),r,VSTACK(i,
HSTACK(u,ROWS(i),SUM(i))),VSTACK(a,HSTACK(r,DROP(r,,2)*0.1))))),
HSTACK("Grand "&u,ROWS(v),s,s*0.1)),IF(e=0,"",e))

Excel solution 4 for Department Totals and Summary, proposed by 🇰🇷 Taeyong Shin:

=LET(d,SCAN(,A2:A11,LAMBDA(a,v,IF(v<>"",v,a))),e,B2:B11,g,DROP(GROUPBY(HSTACK(XMATCH(d,d),XMATCH(e,e),d,e),C2:C11,HSTACK(ROWS,SUM,LAMBDA(x,SUM(x*0.1))),,2),1),n,SEQUENCE(ROWS(g)),t,INDEX(g,n,IF(INDEX(g,,2)<>"",{3,4,6,7},{3,5,6,7})),a,IFS((t="")*(n=MAX(n)),"GrandTotal",t="","Total",1,t),VSTACK(HSTACK(A1:C1,"MaxBonus"),IF(VSTACK({1,1,1,1},DROP(a,-1)<>DROP(a,1)),a,"")))

Excel solution 5 for Department Totals and Summary, proposed by Duy Tùng:

=LE&T(H,HSTACK,V,VSTACK,m,SCAN(,A2:A11,LAMBDA(x,y,IF(y>0,y,x))),u,REDUCE(HSTACK(A1:C1,"Max Bonus"),UNIQUE(m),LAMBDA(x,y,LET(d,FILTER(B2:C11,m=y),e,INDEX(d,,2)*10%,IFNA(V(x,V(H(y,d,e),H("Total",COUNTA(INDEX(d,,1)),SUM(INDEX(d,,2)),SUM(e)))),"")))),V(u,H("Grand Total",DROP(BYCOL(FILTER(u,INDEX(u,,2)<""),SUM),,1))))

Excel solution 6 for Department Totals and Summary, proposed by Sunny Baggu:

=LET(
 _f, SCAN("", A2:A11, LAMBDA(a, v, IF(v = "", a, v))),
 _e, TOCOL(A2:A11, 3),
 _tbl, REDUCE(
 HSTACK(A1:C1, "Max Bonus"),
 _e,
 LAMBDA(x, y,
 VSTACK(
 x,
 LET(
 _a, FILTER(IF(A2:C11 = "", "", A2:C11), _f = y),
 _b, TAKE(_a, , -1) * 0.1,
 _c, HSTACK(_a, _b),
 _d, HSTACK(ROWS(_c), BYCOL(TAKE(_c, , -2), LAMBDA(b, SUM(b)))),
 VSTACK(_c, HSTACK("", _d))
 )
 )
 )
 ),
 VSTACK(_tbl, HSTACK("Grand Total", BYCOL(FILTER(TAKE(_tbl, , -3), ISNUMBER(INDEX(_tbl, , 2))), LAMBDA(A, SUM(A)))))
)

Excel solution 7 for Department Totals and Summary, proposed by LEONARD OCHEA 🇷🇴:

=> A1:C11
=LET(t,A1:C11,h,TAKE(t,1),g,DROP(t,1),s,SCAN("",TAKE(g,,1),LAMBDA(a,b,IF(b="",a,b))),u,UNIQUE(s),k,SUM(TAKE(g,,-1)),r,VSTACK(REDUCE(HSTACK(h,"Max Bonus"),u,LAMBDA(c,d,LET(f,FILTER(g,s=d),v,VSTACK( f,HSTACK("Total",ROWS(f),SUM(TAKE(f,,-1)))),VSTACK(c,HSTACK(v,TAKE(v,,-1)*0.1))))),HSTACK("Grand Total",ROWS(g),k,k*0.1)),IF(r=0,"",r))

Excel solution 8 for Department Totals and Summary, proposed by 🇵🇪 Ned Navarrete C.:

=LET(s,SCAN(0,A2:A11,LAMBDA(a,v,IF(v="",a,1+a))),m,REDUCE({"Dept","Emp","Salary"},UNIQUE(s),LAMBDA(a,v,LET(f,FILTER(A2:C11,s=v),g,IF(f=0,"",f),VSTACK(a,g,HSTACK("Total",ROWS(g),SUM(g)))))),n,HSTACK(m,VSTACK("Max Bonus",DROP(m,1,2)/10)),x,FILTER(n,TAKE(n,,1)="Total"),y,BYCOL(x,LAMBDA(r,SUM(r))),VSTACK(n,IF(y,y,"Grand Total")))

Excel solution 9 for Department Totals and Summary, proposed by Asheesh Pahwa:

=LET(dp,A2:A13,emp, B2:B13,sl,C2:C13,
sc,SCAN("",dp, LAMBDA(x,y,IF(y="",x,y))),
w,DROP(REDUCE("",UNIQUE (sc),LAMBDA(acc, itr, VSTACK(acc, LET (a, FILTER (HSTACK(emp,sl), sc=itr),dp,itr&TAKE(a,1,1),ttl,SUM(TAKE (a,,-1)),ct,COUNTA(TAKE(a,,1)),b,TAKE(a,,-1),bn,b*10%,sm,SUM(b),s,SUM(bn),v,HSTACK(ct,ttl,s),h,VSTACK(HSTACK(a,bn),v),h)))),1),r,HSTACK(XLOOKUP(TAKE(w,,1),emp,dp,"Total"),w),f,IF(r=0,"",r),wm,BYCOL(FILTER(DROP(r,, 1),DROP(REDUCE("",SEQUENCE(ROWS(r)),LAMBDA (x,y,VSTACK(x,INDEX(TAKE (r,, 1),y,)="Total"))),1)), LAMBDA(x,SUM(x),VSTACK(f,HSTACK("Grand Total",wm)))

Excel solution 10 for Department Totals and Summary, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=LET(j,LET(r,TEXTSPLIT(TEXTJOIN(,,MAP(LET(x,UNIQUE(A2:A11),FILTER(x,x<>0)),LAMBDA(x,TEXTJOIN(",",,LET(b,LET(y,LET(x,UNIQUE(A2:A11),FILTER(x,x<>0)),BYROW(SCAN(0,IF(IF(A2:A11<>"",ROW(A2:A11),"")<>"",1,0),LAMBDA(a,b,a+b)),LAMBDA(c,XLOOKUP(c,SEQUENCE(COUNTA(y)),y)))),VSTACK(HSTACK(FILTER(A2:C11,x=b),TAKE(FILTER(A2:C11,x=b),,-1)*0.1&"/"),LET(a,FILTER(A2:C11,x=b),HSTACK("/Total",COUNTA(FILTER(b,x=b)),SUM(TAKE(a,,-1)),SUM(TAKE(HSTACK(a,TAKE(a,,-1)*0.1),,-1))&"/")))))))),",","/"),FILTER(r,NOT(ISNA(TAKE(r,,-1))))),VSTACK(j,HSTACK("Grand Total", BYCOL(VALUE(FILTER(CHOOSECOLS(j,{2,3,4}),ISNUMBER(VALUE(CHOOSECOLS(j,2))))),LAMBDA(a,SUM(a))))))

Excel solution 11 for Department Totals and Summary, proposed by Oscar Javier Rosero Jiménez:

=LET(
_a,SCAN("",A2:A11,LAMBDA(x,y,IF(y="",x,y))),
_num,SCAN(1,A2:A11,LAMBDA(x,y,IF(y="",x,x+1))),
_b, HSTACK(_num,_a,B2:C11,C2:C11*10%),
_u, UNIQUE(_a),
_cont, MAP(_u, LAMBDA(x, COUNTA(FILTER(CHOOSECOLS(_b,3),CHOOSECOLS(_b,2)=x)))),
_suma, LAMBDA(_col, MAP(_u, LAMBDA(x, SUM(FILTER(CHOOSECOLS(_b,_col),CHOOSECOLS(_b,2)=x))))),
_res,SORT(VSTACK(_b, HSTACK(SEQUENCE(COUNTA(_u))+1,TEXTSPLIT("Total-"&SEQUENCE(COUNTA(_u)),,"-"),_cont,_suma(4),_suma(5)))),
_GT, LAMBDA(x, SUM(IF(--(CHOOSECOLS(_res,2)="Total"),CHOOSECOLS(_res,x),0))),
_res2, DROP(VSTACK(_res, HSTACK("","Grand Total",_GT(3),_GT(4),_GT(5))),,1),
_res2)

Excel solution 12 for Department Totals and Summary, proposed by LUIS FLORENTINO COUTO CORTEGOSO:

=LET(d,SCAN(,A2:A11,LAMBDA(a,i,IF(i="",a,i))),t,HSTACK(A2:C11,C2:C11*0.1),u,TOCOL(A2:A11,1),m,REDUCE(F1:I1,u,LAMBDA(a,x,LET(g,FILTER(t,d=x,""),VSTACK(a,g,HSTACK("Total",ROWS(g),BYCOL(CHOOSECOLS(g,{3;4}),SUM)))))),f,VSTACK(m,HSTACK("Grand Total",ROWS(t),BYCOL(CHOOSECOLS(t,{3;4}),SUM))),IF(f=0,"",f))

Excel solution 13 for Department Totals and Summary, proposed by Andres Rojas Moncada:

=LET(dep,A2:A11,emp,B2:B11,sal,C2:C11,a,SCAN("",dep,LAMBDA(acu,val,IF(val="",acu,val))),b,ROWS(a),c,GROUPBY(a,sal,HSTACK(ROWS,SUM),0,1),
d,REDUCE(A1:C1,SEQUENCE(b),LAMBDA(acu,val,LET(e,INDEX(dep,val)&"",f,INDEX(emp,val),g,INDEX(sal,val),h,INDEX(a,val-1),
i,HSTACK("Total",VLOOKUP(h,c,2,0),VLOOKUP(h,c,3,0)),j,HSTACK(e,f,g),VSTACK(acu,IFS(((val=1)+(e=""))*(val





Excel solution 14 for Department Totals and Summary, proposed by Burhan Cesur:

=REDUCE(HSTACK(A1:C1,"Max Bonus"),TOCOL(UNIQUE(K3:K12)),LAMBDA(x,y,LET(a,FILTER(A2:C11,DROP(SCAN(,A1:A11,LAMBDA(x,y,IF(y="",x,y))),1)=y),VSTACK(x,HSTACK(a,CHOOSECOLS(a,3)*10%),HSTACK("Total",ROWS(a),SUM(CHOOSECOLS(a,3)),SUM(CHOOSECOLS(a,3))*10%)))))




Excel solution 15 for Department Totals and Summary, proposed by Burhan Cesur:

=LET(bc,REDUCE(HSTACK(A1:C1,"Max Bonus"),TOCOL(UNIQUE(SCAN(,A2:A11,LAMBDA(x,y,IF(y="",x,y))))),LAMBDA(x,y,LET(a,FILTER(A2:C11,SCAN(,A2:A11,LAMBDA(x,y,IF(y="",x,y)))=y),VSTACK(x,HSTACK(a,CHOOSECOLS(a,3)*10%),HSTACK("Total",ROWS(a),SUM(CHOOSECOLS(a,3)),SUM(CHOOSECOLS(a,3))*10%))))),VSTACK(bc,HSTACK("Grand Total",SUM(BYROW(CHOOSECOLS(bc,2),SUM)),BYCOL(FILTER(CHOOSECOLS(bc,3,4),CHOOSECOLS(bc,1)<>"Total"),SUM))))




Excel solution 16 for Department Totals and Summary, proposed by José Antônio Morato de Carvalho:

Dear Asheesh,
                    
                  








Solving the challenge of Department Totals and Summary with R



R solution 1 for Department Totals and Summary, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
library(janitor)
input = read_excel("Power Query/PQ_Challenge_165.xlsx", range = "A1:C11")
test = read_excel("Power Query/PQ_Challenge_165.xlsx", range = "F1:I15")
r1 = input %>%
 mutate(`Max Bonus` = Salary * 0.1,
 group = cumsum(!is.na(Dept)))
make_summary = function(df, gr) {
 data <- df %>%
 filter(group == gr) 
 
 summary <- data %>%
 mutate(Dept = "Total") %>%
 summarise(Dept = first(Dept),
 Emp = as.character(n()),
 Salary = sum(Salary),
 `Max Bonus` = sum(`Max Bonus`))
 result = bind_rows(data, summary)
 return(result)
}
groups = unique(r1$group)
r2 = map_dfr(groups, ~make_summary(r1, .x))
grand_total = r2 %>%
 filter(!is.na(group)) %>%
 summarise(Dept = "Grand Total",
 Emp = as.character(n()),
 Salary = sum(Salary),
 `Max Bonus` = sum(`Max Bonus`))
result = bind_rows(r2, grand_total) %>%
 select(-group)
                    
                  



&&

Solving the challenge of Department Totals and Summary with Power Query

Solving the challenge of Department Totals and Summary with Excel

Solving the challenge of Department Totals and Summary with R

Leave a Reply Cancel reply