Home » Sum Bird Quantity Alphabetically

Sum Bird Quantity Alphabetically

Looks like yesterday’s problem was a difficult one as only one person responded. It would have exposed persons to some good techniques in PQ. Now, here is a simpler problem for Sunday so that it can be solved easily and in process new techniques can be discovered. Align the Problem table to Result table. Quantity in Result table is the sum of quantity in Problem table. Sort the birds in Result table alphabetically.

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

Solving the challenge of Sum Bird Quantity Alphabetically with Power Query

Power Query solution 1 for Sum Bird Quantity Alphabetically, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  torow = Table.ToRows(Table.RemoveColumns(Table.DemoteHeaders(Source), "Column1")), 
  FromCol = Table.FromColumns(
    {
      List.RemoveNulls(List.Combine(List.Alternate(torow, 1, 1, 1))), 
      List.RemoveNulls(List.Combine(List.Alternate(torow, 1, 1, 0)))
    }, 
    {"Birds", "Q"}
  ), 
  Group = Table.Sort(
    Table.Group(FromCol, {"Birds"}, {{"Quantity", each List.Sum([Q]), type number}}), 
    {{"Birds", 0}}
  )
in
  Group
Power Query solution 2 for Sum Bird Quantity Alphabetically, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "ProblemTable"]}[Content], 
  DemotedHeaders = Table.DemoteHeaders(Source), 
  TransposedTable = Table.Transpose(DemotedHeaders), 
  RemovedTop1Rows = Table.Skip(TransposedTable, 1), 
  UnpivotedColumns = Table.UnpivotOtherColumns(RemovedTop1Rows, {}, "Attribute", "Value"), 
  SeparateColumns = Table.FromRows(
    List.Zip(
      {
        List.Alternate(UnpivotedColumns[Value], 1, 1, 1), 
        List.Alternate(UnpivotedColumns[Value], 1, 1)
      }
    ), 
    {DemotedHeaders[Column1]{0}, DemotedHeaders[Column1]{1}}
  ), 
  GroupedRows = Table.Group(
    SeparateColumns, 
    {"Birds"}, 
    {{"Quantity", each List.Sum([Quantity]), type number}}
  ), 
  SortedRows = Table.Sort(GroupedRows, {{"Birds", Order.Ascending}})
in
  SortedRows
Power Query solution 3 for Sum Bird Quantity Alphabetically, proposed by Kris Jaganah: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Transposed" = Table.Transpose(Source), 
  #"Promoted" = Table.PromoteHeaders(#"Transposed", [PromoteAllScalars = true]), 
  #"Unpivoted" = Table.UnpivotOtherColumns(#"Promoted", {}, "Attribute", "Value"), 
  #"Removed" = Table.RemoveColumns(#"Unpivoted", {"Attribute"}), 
  #"Added" = Table.AddIndexColumn(#"Removed", "Index", 0, 1, Int64.Type), 
  #"Added Index1" = Table.AddIndexColumn(#"Added", "Index.1", 1, 1, Int64.Type), 
  #"Merged" = Table.NestedJoin(
    #"Added Index1", 
    {"Index.1"}, 
    #"Added", 
    {"Index"}, 
    "Added Index1", 
    JoinKind.LeftOuter
  ), 
  #"Expanded" = Table.ExpandTableColumn(
    #"Merged", 
    "Added Index1", 
    {"Value"}, 
    {"Added Index1.Value"}
  ), 
  #"Removed1" = Table.AlternateRows(#"Expanded", 1, 1, 1), 
  #"Removed2" = Table.RemoveColumns(#"Removed1", {"Index", "Index.1"}), 
  #"Renamed" = Table.RenameColumns(#"Removed2", {{"Value", "Birds"}}), 
  #"Grouped" = Table.Group(
    #"Renamed", 
    {"Birds"}, 
    {{"Quantity", each List.Sum([Added Index1.Value]), type number}}
  ), 
  #"Sorted" = Table.Sort(#"Grouped", {{"Birds", Order.Ascending}})
in
  #"Sorted"
Power Query solution 4 for Sum Bird Quantity Alphabetically, proposed by Aditya Kumar Darak 🇮🇳: 
leted some cluttering Auto Generated Codes.

let
 Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
 Index = Table.AddIndexColumn(Source, "Index", 0),
 Divide = Table.TransformColumns(Index, {"Index", each Number.IntegerDivide(_, 2)}),
 Unpivot = Table.UnpivotOtherColumns(Divide, {"Index", "Column1"}, "Attribute", "Value"),
 Pivot = Table.Pivot(Unpivot, List.Distinct(Unpivot[Column1]), "Column1", "Value"),
 Grouped = Table.Group(Pivot, "Birds", {"Quantity", each List.Sum([Quantity])}),
 Return = Table.Sort(Grouped,"Birds")
in
 Return
Power Query solution 5 for Sum Bird Quantity Alphabetically, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Transpose = Table.FromRows(
    List.Split(Table.ToColumns(Table.PromoteHeaders(Table.Transpose(Source))), 2)
  ), 
  TableCol = Table.AddColumn(
    Transpose, 
    "Custom", 
    each Table.SelectRows(Table.FromColumns({[Column1], [Column2]}), each [Column1] <> null)
  )[[Custom]], 
  Expand = Table.ExpandTableColumn(
    TableCol, 
    "Custom", 
    {"Column1", "Column2"}, 
    {"Birds", "Quantity"}
  ), 
  Group = Table.Group(Expand, {"Birds"}, {{"Quantity", each List.Sum([Quantity])}}), 
  Sol = Table.Sort(Group, {{"Birds", Order.Ascending}})
in
  Sol
Power Query solution 6 for Sum Bird Quantity Alphabetically, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  tab = Table.FromRows(
    List.Split(List.RemoveNulls(List.Combine(List.RemoveFirstN(Table.ToColumns(Fonte), 1))), 2), 
    {"Birds", "Quantity"}
  ), 
  res = Table.Sort(
    Table.Group(tab, {"Birds"}, {{"Quantity", each List.Sum([Quantity])}}), 
    {{"Birds", Order.Ascending}}
  )
in
  res
Power Query solution 7 for Sum Bird Quantity Alphabetically, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData64"]}[Content], 
  ToRows = List.Transform(Table.ToRows(Source), each List.Skip(_)), 
  sColNames = List.First(Record.ToList(Source{0})), 
  sColValues = List.First(Record.ToList(Source{1})), 
  ToTable = Table.FromColumns(
    {
      List.RemoveNulls(List.Combine(List.Alternate(ToRows, 1, 1, 1))), 
      List.RemoveNulls(List.Combine(List.Alternate(ToRows, 1, 1, 0)))
    }, 
    {sColNames, sColValues}
  ), 
  Group = Table.Group(
    ToTable, 
    {sColNames}, 
    {{sColValues, each List.Sum(Table.Column(_, sColValues))}}
  ), 
  Sort = Table.Sort(Group, {{sColNames, Order.Ascending}})
in
  Sort
Power Query solution 8 for Sum Bird Quantity Alphabetically, proposed by Victor Wang: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  DemoteHeaders = Table.DemoteHeaders(Source), 
  Combine = Table.Combine(
    List.Transform(Table.Split(DemoteHeaders, 2), each Table.PromoteHeaders(Table.Transpose(_)))
  ), 
  Group = Table.Group(Combine, {"Birds"}, {{"Quantity", each List.Sum([Quantity])}}), 
  Sort = Table.Sort(Group, {{"Birds", Order.Ascending}}), 
  NoNull = Table.SelectRows(Sort, each ([Birds] <> null))
in
  NoNull
Power Query solution 9 for Sum Bird Quantity Alphabetically, proposed by Krzysztof Kominiak: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ls = List.Transform(Table.ToRows(Source), each List.Skip(_)), 
  NewTbl = Table.FromColumns(
    {List.Combine(List.Alternate(Ls, 1, 1, 1)), List.Combine(List.Alternate(Ls, 1, 1, 0))}, 
    {"Birds", "Quantity"}
  ), 
  FilterRows = Table.SelectRows(NewTbl, each ([Birds] <> null)), 
  Result = Table.Sort(
    Table.Group(FilterRows, {"Birds"}, {{"Quantity", each List.Sum([Quantity]), type number}}), 
    {{"Birds", Order.Ascending}}
  )
in
  Result
Power Query solution 10 for Sum Bird Quantity Alphabetically, proposed by Sandeep Marwal: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  S1 = Table.FromColumns(
    {
      List.Combine(
        Table.AlternateRows(
          Table.FromColumns({List.Transform(Table.ToRows(Source), each List.Skip(_))}), 
          0, 
          1, 
          1
        )[Column1]
      )
    }
      & {
        List.Combine(
          Table.AlternateRows(
            Table.FromColumns({List.Transform(Table.ToRows(Source), each List.Skip(_))}), 
            1, 
            1, 
            1
          )[Column1]
        )
      }
  ), 
  S2 = Table.Group(S1, {"Column2"}, {{"Count", each List.Sum([Column1]), type nullable number}}), 
  S3 = Table.SelectRows(S2, each ([Column2] <> null)), 
  S4 = Table.RenameColumns(S3, {{"Column2", "Bird"}, {"Count", "Quantity"}}), 
  S5 = Table.Sort(S4, {{"Bird", Order.Ascending}})
in
  S5
Power Query solution 11 for Sum Bird Quantity Alphabetically, proposed by Mahmoud Bani Asadi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Custom1 = Table.Sort(
    Table.SelectRows(
      Table.Group(
        Table.Combine(
          List.Transform(Table.Split(Source, 2), each Table.PromoteHeaders(Table.Transpose(_)))
        ), 
        {"Birds"}, 
        {{"Qty", each List.Sum([Quantity]), Int64.Type}}
      ), 
      each [Birds] <> null
    ), 
    {{"Birds", 0}}
  )
in
  Custom1
Power Query solution 12 for Sum Bird Quantity Alphabetically, proposed by Udit Chatterjee: 
let
  Source = pqProblem64, 
  transposeTable = Table.Transpose(Source), 
  promoteHeaders = Table.PromoteHeaders(transposeTable, [PromoteAllScalars = true]), 
  unpivotedOtherCols = Table.UnpivotOtherColumns(promoteHeaders, {}, "Attribute", "Value"), 
  getBirdsOnly = Table.AddColumn(
    unpivotedOtherCols, 
    "Birds", 
    each if Text.Contains([Attribute], "Birds") then [Value] else null
  ), 
  getQuantitiesOnly = Table.AddColumn(
    getBirdsOnly, 
    "Quantity", 
    each if Text.Contains([Attribute], "Quantity") then [Value] else null
  ), 
  filldownBirdsCol = Table.FillDown(getQuantitiesOnly, {"Birds"}), 
  filterNulls = Table.SelectRows(filldownBirdsCol, each ([Birds] <> "") and ([Quantity] <> null)), 
  removeOtherCols = Table.RemoveColumns(filterNulls, {"Attribute", "Value"}), 
  fixDatatypes = Table.TransformColumnTypes(
    removeOtherCols, 
    {{"Birds", type text}, {"Quantity", Int64.Type}}
  ), 
  groupingRows = Table.Group(
    fixDatatypes, 
    {"Birds"}, 
    {{"Quantity", each List.Sum([Quantity]), Int32.Type}}
  ), 
  sortedRows = Table.Sort(groupingRows, {{"Birds", Order.Ascending}})
in
  sortedRows

Solving the challenge of Sum Bird Quantity Alphabetically with Excel

Excel solution 1 for Sum Bird Quantity Alphabetically, proposed by Bo Rydobon 🇹🇭: 
=LET(z,SORT(WRAPROWS(TOCOL(B1:G8,3,1),2)),a,TAKE(z,,1),u,UNIQUE(a),HSTACK(u,MAP(u,LAMBDA(v,SUM(IF(a=v,z))))))
Excel solution 2 for Sum Bird Quantity Alphabetically, proposed by Rick Rothstein: 
=LET(d,TOCOL(B1:F8),b,SORT(UNIQUE(FILTER(d,ISTEXT(d)))),HSTACK(b,MAP(b,LAMBDA(x,SUMIF(B1:F7,x,B2:F8)))))

Long version (single range)...
=LET(d,TOCOL(B1:F8),b,SORT(UNIQUE(FILTER(d,ISTEXT(d)))),HSTACK(b,MAP(b,LAMBDA(x,SUMIF(B1:F8,x,INDEX(B1:F8,2,))))))
Excel solution 3 for Sum Bird Quantity Alphabetically, proposed by محمد حلمي: 
=LET(r,WRAPROWS(TOCOL(B1:F8,1,1),2),l,TAKE(r,,1),e,SORT(UNIQUE(l)),VSTACK(HSTACK(A1,A2), HSTACK(e,MAP(e,LAMBDA(a,SUM((l=a)*DROP(r,,1)))))))
Excel solution 4 for Sum Bird Quantity Alphabetically, proposed by محمد حلمي: 
=LET(
d,TOCOL(B1:F8,1),
r,FILTER(d,ISTEXT(d)),
VSTACK(HSTACK(A1,A2),
SORT(HSTACK(UNIQUE(r),
MAP(UNIQUE(r),LAMBDA(a,SUM(--(r=a)*
FILTER(d,ISNUMBER(d)))))))))
Excel solution 5 for Sum Bird Quantity Alphabetically, proposed by 🇰🇷 Taeyong Shin: 
=LET(t,WRAPCOLS(TOCOL(B1:F8,,1),2),c,TAKE(t,1),r,UNIQUE(TOCOL(c,1)),SORT(HSTACK(r,MMULT(N(r=c),--TOCOL(DROP(t,1))))))

=LET(F,LAMBDA(fn,TOCOL(IFS(fn(B1:F8),B1:F8),2)),GROUPBY(F(ISTEXT),F(ISNUMBER),SUM,,0))
Excel solution 6 for Sum Bird Quantity Alphabetically, proposed by Kris Jaganah: 
=LET(a,TOROW(UNIQUE(A1:A8)),b,WRAPROWS(TOCOL(B1:F8,3,TRUE),2),c,SORT(UNIQUE(TAKE(b,,1))),d,MAP(c,LAMBDA(x,SUM((x=TAKE(b,,1))*TAKE(b,,-1)))),VSTACK(a,HSTACK(c,d)))
Excel solution 7 for Sum Bird Quantity Alphabetically, proposed by Alejandro Campos: 
=VSTACK(
 {"Birds", "Quantity"},
 LET(
 d, TOCOL(B2:F9),
 b, FILTER(d, ISTEXT(d)),
 t, FILTER(d, ISNUMBER(d)),
 GROUPBY(b, t, SUMA, , 0)))
Excel solution 8 for Sum Bird Quantity Alphabetically, proposed by Hussein SATOUR: 
=LET(a, TOCOL(B1:F8), b, FILTER(a, ISTEXT(a)), c, FILTER(a, ISNUMBER(a)), SORT(HSTACK(UNIQUE(b), MAP(UNIQUE(b), LAMBDA(x, SUM(FILTER(c, b=x))))), 1))
Excel solution 9 for Sum Bird Quantity Alphabetically, proposed by Oscar Mendez Roca Farell: 
=LET(_e, TOCOL(B1:F8),_b, FILTER(_e, _e>""),_q, FILTER(_e, N(+_e)>0),_u, UNIQUE(_b),ORDER(HSTACK(_u, BYROW(_u, LAMBDA(i, SUM(SI(_b=i, _q)))))))
Excel solution 10 for Sum Bird Quantity Alphabetically, proposed by Sunny Baggu: 
=LET(_Birds,TOCOL(IF(ISTEXT(B1:F8),B1:F8,x),3),
_qty,TOCOL(IF(ISNUMBER(B1:F8),B1:F8,x),3),
_ub,SORT(UNIQUE(_Birds)),
HSTACK(_ub,MAP(_ub,LAMBDA(a,SUM(FILTER(_qty,_Birds=a))))))
Excel solution 11 for Sum Bird Quantity Alphabetically, proposed by Sunny Baggu: 
=LET(_tbl,WRAPROWS(TOCOL(B1:F8,3,1),2),
_col1,TAKE(_tbl,,1),_col2,TAKE(_tbl,,-1),
DROP(REDUCE("",SORT(UNIQUE(_col1)),LAMBDA(a,v,VSTACK(a,HSTACK(v,SUM(FILTER(_col2,_col1=v,0)))))),1))
Excel solution 12 for Sum Bird Quantity Alphabetically, proposed by Md. Zohurul Islam: 
=LET(z,B1:F8,sq,MOD(SEQUENCE(ROWS(z)),2),
a,TOCOL(FILTER(z,sq=1),3),
b,TOCOL(FILTER(z,sq=0),3),
d,REDUCE({"Birds","Quantity"},SORT(UNIQUE(a)),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(FILTER(b,a=y)))))),d)
Excel solution 13 for Sum Bird Quantity Alphabetically, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=VSTACK(TOROW(UNIQUE(A1:A8));HSTACK(LET(m;TOCOL(TOROW(IF(ISTEXT(B1:F8);B1:F8;"");1;TRUE));SORT(UNIQUE(FILTER(m;m<>""));;1));MAP(LET(m;TOCOL(TOROW(IF(ISTEXT(B1:F8);B1:F8;"");1;TRUE));SORT(UNIQUE(FILTER(m;m<>""));;1));LAM&BDA(v;SUM(MAP(LET(q;TOCOL(TOROW(IF(ISTEXT(B1:F8);B1:F8;"");1;TRUE));FILTER(q;q<>""));LET(w;TOCOL(TOROW(IF(ISNUMBER(B1:F8);B1:F8;"");1;TRUE));FILTER(w;w<>""));LAMBDA(x;y;IFERROR(XLOOKUP(v;x;y);0))))))))
Excel solution 14 for Sum Bird Quantity Alphabetically, proposed by Gerson Pineda: 
=SORT(LET(_l,TOCOL(B1:F8),_t,FILTER(_l,_l>""),REDUCE({"Birds","Quantity"},UNIQUE(_t),LAMBDA(ii,i,VSTACK(ii,HSTACK(i,SUM((i=_t)*FILTER(_l,N(_l)))))))))
Excel solution 15 for Sum Bird Quantity Alphabetically, proposed by Gabriel Raigosa: 
=LET(dat,TOCOL(B1:F8),Bi,SORT(UNIQUE(FILTER(dat,ISTEXT(dat)))),HSTACK(Bi,BYROW(Bi,LAMBDA(x,SUM(FILTER(DROP(dat,5),DROP(dat,-5)=x)))))) 

▶️ES:
=LET(dat,ENCOL(B1:F8),Bi,ORDENAR(UNICOS(FILTRAR(dat,ESTEXTO(dat)))),APILARH(Bi,BYROW(Bi,LAMBDA(x,SUMA(FILTRAR(EXCLUIR(dat,5),EXCLUIR(dat,-5)=x))))))
Excel solution 16 for Sum Bird Quantity Alphabetically, proposed by Dinc Doga: 
=LET(
 _e, TOCOL(B1:F8),
 _b, FILTER(_e, _e>""),
 _q, FILTER(_e, N(_e)>0),
 _u, UNIQUE(_b),
 quantities, BYROW(_u, LAMBDA(i, SUM(IF(_b=i, _q)))),
 VSTACK({"Birds", "Quantity"}, SORT(HSTACK(_u, quantities), 1, 1))
)
Excel solution 17 for Sum Bird Quantity Alphabetically, proposed by Dinc Doga: 
=LET(
 d, TOCOL(B1:F8, 1),
 r, FILTER(d, ISTEXT(d)),
 nums, FILTER(d, ISNUMBER(d)),
 quantities, MAP(UNIQUE(r), LAMBDA(a, SUM(FILTER(nums, r=a)))),
 VSTACK({"Birds","Quantity"}, SORT(HSTACK(UNIQUE(r), quantities)))
)
Excel solution 18 for Sum Bird Quantity Alphabetically, proposed by Dinc Doga: 
=VSTACK(
 {"Birds","Quantity"},
 LET(
 d, TOCOL(B1:F8, 3),
 b, FILTER(d, ISTEXT(d)),
 t, FILTER(d, ISNUMBER(d)),
 apH, HSTACK(b, t),
 GROUPBY(b, t, LAMBDA(a, SUM(a)))
 )
)

&&

Leave a Reply