Home » Company Totals for Items/Amount

Company Totals for Items/Amount

Prepare the result table from problem table. Insert the Total Items and Total Amount for all companies.

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

Solving the challenge of Company Totals for Items/Amount with Power Query

Power Query solution 1 for Company Totals for Items/Amount, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.Combine(
    Table.Group(
      Source, 
      "Company", 
      {
        "T", 
        each 
          let
            n = Table.ColumnNames(_), 
            b = Table.Combine(
              Table.TransformRows(
                _, 
                each Table.FillDown(
                  Table.FromColumns(List.Transform(Record.ToList(_), each Text.Split(_, " / ")), n), 
                  n
                )
              )
            ), 
            c = Table.TransformColumnTypes(
              b, 
              List.Zip({n, List.Transform(Record.ToList(b{0}), each Value.Type(Value.FromText(_)))})
            )
          in
            Table.FromRows(
              Table.ToRows(c)
                & {
                  {
                    c[Company]{0}, 
                    "Total Itmes", 
                    Table.RowCount(c), 
                    "Total Amount", 
                    List.Sum(c[PO_Amount])
                  }
                }, 
              n
            )
      }
    )[T]
  )
in
  Ans
Power Query solution 2 for Company Totals for Items/Amount, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  c = Table.ToColumns(Source), 
  s = Table.FromColumns(
    List.Transform(
      List.Positions(c), 
      (p) =>
        List.Combine(
          List.Transform(
            List.Positions(c{p}), 
            (r) =>
              if p < 2 then
                List.Repeat({c{p}{r}}, List.Count(Text.Split(c{p + 2}{r}, " / ")))
              else
                List.Transform(Text.Split(c{p}{r}, " / "), each try Number.From(_) otherwise _)
          )
        )
    ), 
    Table.ColumnNames(Source)
  ), 
  S = Table.Sort(
    Table.Group(
      s, 
      {"Company"}, 
      {
        {"PO_No", each "Total Items"}, 
        {"PO_Line_No", each Table.RowCount(_)}, 
        {"PO_Items", each "Total Amount"}, 
        {"PO_Amount", each List.Sum([PO_Amount])}
      }
    )
      & s, 
    {"Company", "PO_Items"}
  )
in
  S
Power Query solution 3 for Company Totals for Items/Amount, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Col = List.Skip(Table.ColumnNames(Source)), 
  Group = Table.Group(
    Source, 
    {"Company"}, 
    {
      {
        "All", 
        each 
          let
            a = Table.ExpandTableColumn(
              Table.SelectColumns(
                Table.AddColumn(
                  _, 
                  "Custom", 
                  each 
                    let
                      b = List.Transform(
                        List.RemoveFirstN(Record.ToList(_), 1), 
                        each Text.Split(_, " / ")
                      ), 
                      c = List.Transform(
                        b, 
                        each List.Transform(
                          {0 .. List.Count(_) - 1}, 
                          (x) => try Number.From(_{x}) otherwise _{x}
                        )
                      ), 
                      d = Table.FromColumns(c, Col)
                    in
                      d
                ), 
                {"Custom"}
              ), 
              "Custom", 
              Col
            ), 
            b = Table.FillDown(a, {"PO_No"}), 
            c = {"Total Items", List.Count(b[PO_No]), "Total Amount", List.Sum(b[PO_Amount])}, 
            d = Table.FromRows(Table.ToRows(b) & {c}, Col)
          in
            d
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "All", Col)
in
  Sol
Power Query solution 4 for Company Totals for Items/Amount, proposed by Luan Rodrigues: 
let
Fonte = Tabela1,
tab = Table.AddColumn(Fonte, "Personalizar", each 
[a = List.Transform(Record.FieldValues(_), (x)=> Text.Split(x," / ")),
b = Table.Combine(List.Transform(List.Zip({Table.ColumnNames(Fonte),a}), (x)=> Table.FromColumns({x{1}},{x{0}}) )),
c = Table.FillDown(Table.PromoteHeaders(Table.FromColumns(List.Transform(Table.ToColumns(Table.DemoteHeaders(b)),List.RemoveNulls))),{"Company","PO_No"})][c])[[Personalizar]],
exp = Table.ExpandTableColumn(tab, "Personalizar", Table.ColumnNames(tab[Personalizar]{0})),
gp = Table.Group(exp, {"Company"}, {{"Contagem", each 
[a = Table.ColumnNames(_),
b = hashtag#table({a{0},a{1},a{2},a{3},a{4}},{{_[Company]{0},"Total Items",List.Count(_[PO_Line_No]),"Total Items",List.Sum(List.Transform(_[PO_Amount],Number.From))}}),
c = Table.Combine({_,b}) 
][c]}})[[Contagem]],
res = Table.ExpandTableColumn(gp, "Contagem", Table.ColumnNames(gp[Contagem]{0}))
in
 res
Power Query solution 5 for Company Totals for Items/Amount, proposed by Hussein SATOUR: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  SplitPoLine = Table.ExpandListColumn(
    Table.TransformColumns(
      Source, 
      {
        {
          "PO_Line_No", 
          Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "PO_Line_No"
  ), 
  AddPoItem = Table.AddColumn(
    SplitPoLine, 
    "Po Item", 
    each Text.Split([PO_Items], " / "){Number.From([PO_Line_No]) - 1}
  ), 
  AddPoAmount = Table.AddColumn(
    AddPoItem, 
    "Po Amount", 
    each Text.Split([PO_Amount], " / "){Number.From([PO_Line_No]) - 1}
  ), 
  RemCols = Table.RemoveColumns(AddPoAmount, {"PO_Items", "PO_Amount"}), 
  Type1 = Table.TransformColumnTypes(
    RemCols, 
    {{"PO_Line_No", Int64.Type}, {"Po Amount", Int64.Type}}
  ), 
  GroupCom = Table.Group(
    Type1, 
    {"Company"}, 
    {
      {"PO_Line_No", each Table.RowCount(_), Int64.Type}, 
      {"Po Amount", each List.Sum([Po Amount]), type nullable number}
    }
  ), 
  Append = Table.Combine({Type1, GroupCom}), 
  Replac1 = Table.ReplaceValue(Append, null, "Total  
  Items", Replacer.ReplaceValue, {"PO_No"}), 
  Replac2 = Table.ReplaceValue(
    Replac1, 
    null, 
    "Total  
  Amount", 
    Replacer.ReplaceValue, 
    {"Po Item"}
  ), 
  Sorting = Table.Sort(
    Replac2, 
    {{"Company", Order.Ascending}, {"PO_No", Order.Ascending}, {"PO_Line_No", Order.Ascending}}
  )
in
  Sorting
Power Query solution 6 for Company Totals for Items/Amount, proposed by Alexis Olson: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  SplitCols = List.LastN(Table.ColumnNames(Source), 3), 
  Custom1 = Table.AddColumn(
    Source, 
    "Split", 
    (row) =>
      Table.FromColumns(
        List.Transform(SplitCols, each Text.Split(Record.Field(row, _), " / ")), 
        SplitCols
      )
  ), 
  SelectCols = Table.SelectColumns(Custom1, {"Company", "PO_No", "Split"}), 
  Expand = Table.ExpandTableColumn(SelectCols, "Split", SplitCols, SplitCols), 
  ChangeTypes = Table.TransformColumnTypes(
    Expand, 
    {
      {"Company", type text}, 
      {"PO_No", type text}, 
      {"PO_Line_No", Int64.Type}, 
      {"PO_Items", type text}, 
      {"PO_Amount", Int64.Type}
    }
  ), 
  GroupCompany = Table.Group(
    ChangeTypes, 
    {"Company"}, 
    {
      {"PO_No", each "Total Items", type text}, 
      {"PO_Line_No", each Table.RowCount(_), Int64.Type}, 
      {"PO_Items", each "Total Amount", type text}, 
      {"PO_Amount", each List.Sum([PO_Amount]), Int64.Type}
    }
  ), 
  AppendRows = Table.Sort(
    Table.Combine({ChangeTypes, GroupCompany}), 
    {{"Company", Order.Ascending}, {"PO_No", Order.Ascending}}
  )
in
  AppendRows
Power Query solution 7 for Company Totals for Items/Amount, proposed by Rafael González B.: 
let
 Source = Excel.CurrentWorkbook(){0}[Content],
 GT = Table.AddColumn(Source, "SP", each 
 let 
 a = _,
 b = Record.TransformFields(a, 
 {
 {"PO_Line_No", each Text.Split(_, "/ ")}, 
 {"PO_Items", each Text.Split(_, "/ ")}, 
 {"PO_Amount", each Text.Split(_, "/ ")}
 }),
 c = Record.AddField(b, "Zip", List.Zip({b[PO_Line_No], b[PO_Items], b[PO_Amount]})),
 d = Record.SelectFields(c, {"PO_No", "Zip"}),
 e = Table.FromRecords({d})
 in 
 e),
Power Query solution 8 for Company Totals for Items/Amount, proposed by Sandeep Marwal: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  S1 = Table.Transpose(Source), 
  S2 = Table.ToColumns(S1), 
  S3 = List.Transform(S2, each List.Transform(_, each try Text.Split(_, "/") otherwise {_})), 
  S4 = Table.Combine(
    List.Transform(
      S3, 
      each Table.FromColumns(_, {"Company", "PO_No", "PO_Line_No", "PO_Items", "PO_Amount"})
    )
  ), 
  S5 = Table.FillDown(S4, {"Company", "PO_No"}), 
  S6 = Table.TransformColumnTypes(
    S5, 
    {
      {"Company", type text}, 
      {"PO_No", type text}, 
      {"PO_Line_No", Int64.Type}, 
      {"PO_Amount", Int64.Type}, 
      {"PO_Items", type text}
    }
  ), 
  S7 = Table.Group(S6, {"Company"}, {{"Count", each _}}), 
  S8 = Table.TransformColumns(
    S7, 
    {
      "Count", 
      each Table.InsertRows(
        _, 
        Table.RowCount(_), 
        {
          [
            Company    = _[Company]{0}, 
            PO_No      = "Total Items", 
            PO_Line_No = Table.RowCount(_), 
            PO_Items   = "Total Amount", 
            PO_Amount  = List.Sum(_[PO_Amount])
          ]
        }
      )
    }
  ), 
  S9 = Table.ExpandTableColumn(
    S8, 
    "Count", 
    {"Company", "PO_No", "PO_Line_No", "PO_Items", "PO_Amount"}, 
    {"Company.1", "PO_No", "PO_Line_No", "PO_Items", "PO_Amount"}
  ), 
  S10 = Table.RemoveColumns(S9, {"Company.1"})
in
  S10
Power Query solution 9 for Company Totals for Items/Amount, proposed by Sanket Doijode: 
let
  Source = Excel.Workbook(File.Contents("Path_of_excel_workbook"), null, true), 
  Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], 
  #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]), 
  #"Grouped Rows" = Table.Group(
    #"Promoted Headers", 
    {"Company", "PO_No"}, 
    {
      {
        "Custom", 
        each _, 
        type table [
          Company = text, 
          PO_No = text, 
          PO_Line_No = text, 
          PO_Items = text, 
          PO_Amount = text
        ]
      }
    }
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Grouped Rows", 
    "Custom.1", 
    each Table.PromoteHeaders(
      Table.Transpose(
        Table.SplitColumn(
          Table.Transpose(Table.DemoteHeaders(Table.RemoveColumns([Custom], {"Company", "PO_No"}))), 
          "Column2", 
          Splitter.SplitTextByAnyDelimiter({"/ "})
        )
      )
    )
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Added Custom1", 
    "Custom.2", 
    each Table_With_Total_Function([Custom.1])
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Custom", "Custom.1"}), 
  #"Expanded Custom.2" = Table.ExpandTableColumn(
    #"Removed Columns", 
    "Custom.2", 
    {"PO_Line_No", "PO_Items", "PO_Amount"}, 
    {"PO_Line_No", "PO_Items", "PO_Amount"}
  )
in
  #"Expanded Custom.2"

Solving the challenge of Company Totals for Items/Amount with Excel

Excel solution 1 for Company Totals for Items/Amount, proposed by Bo Rydobon 🇹🇭: 
=REDUCE(A1:E1,UNIQUE(A2:A6),LAMBDA(a,v,LET(p,B2:B6,
d,DROP(REDUCE(0,FILTER(p,v=A2:A6),LAMBDA(b,x,VSTACK(b,TRANSPOSE(TEXTSPLIT(TEXTJOIN("|",,XLOOKUP(x,p,B2:E6))," / ","|",,,x))))),1),
VSTACK(a,VSTACK(IFNA(HSTACK(v,IFERROR(--d,d)),v),HSTACK(v,"Total Items",ROWS(d),"Total Amount",SUM(--DROP(d,,3))))))))
Excel solution 2 for Company Totals for Items/Amount, proposed by محمد حلمي: 
=LET(r,A2:A6,c,REDUCE(0,r,LAMBDA(a,d,LET(
u,TRANSPOSE(TEXTSPLIT(
CONCAT(OFFSET(d,,1,,4)&"-")," / ","-",1)),i,IFNA(u,@u),
VSTACK(a,IFNA(HSTACK(d,IFERROR(--i,i)),d))))),
REDUCE(A1:E1,UNIQUE(r),LAMBDA(a,d,LET(
v,FILTER(c,TAKE(c,,1)=d),
VSTACK(a,VSTACK(v,HSTACK(d,"Total Items",
ROWS(v),"Total Amount",SUM(TAKE(v,,-1)))))))))
Excel solution 3 for Company Totals for Items/Amount, proposed by Oscar Mendez Roca Farell: 
=REDUCE(A1:E1, UNIQUE(A2:A6), LAMBDA(i, x, LET(_f,FILTER(B2:E6, A2:A6=x),_m, DROP(REDUCE("",SEQUENCE(ROWS(_f)), LAMBDA(j, y VSTACK(j, TRANSPOSE(TEXTSPLIT(TEXTJOIN("|" , ,INDEX(_f, y, )), " / ","|" , , ,INDEX(_f, y, 1)))))), 1), VSTACK(i, IFNA(HSTACK(x, VSTACK(_m, TEXTSPLIT(TEXTJOIN("|", ,"Total "&{"Items";"Amount"}&"|"&IF({1;0}, ROWS(_m),SUM(--TAKE(_m, ,-1)))), "|"))), x)))))
Excel solution 4 for Company Totals for Items/Amount, proposed by Sunny Baggu: 
=LET(
 _r, DROP(
 REDUCE(
 "",
 SEQUENCE(ROWS(A2:E6)),
 LAMBDA(e, d,
 VSTACK(
 e,
 LET(
 _a, IFNA(DROP(REDUCE("", SEQUENCE(COLUMNS(A1:E1)), LAMBDA(a, v, HSTACK(a, TEXTSPLIT(INDEX(INDEX(A2:E6, d, ), , v), , " / ", 1)))), , 1), ""),
 _b, TRANSPOSE(SCAN("", TRANSPOSE(_a), LAMBDA(x, y, IF(y = "", x, y)))),
 _b
 )
 )
 )
 ),
 1
 ),
 tbl, IF(ISNUMBER(_r * 1), _r * 1, _r),
 REDUCE(
 A1:E1,
 UNIQUE(TAKE(tbl, , 1)),
 LAMBDA(m, n, VSTACK(m, LET(_t1, FILTER(tbl, TAKE(tbl, , 1) = n), VSTACK(_t1, HSTACK(n, "Total Items", ROWS(_t1), "Total Amount", SUM(TAKE(_t1, , -1)))))))
 )
)
Excel solution 5 for Company Totals for Items/Amount, proposed by Md Ismail Hosen: 
=LAMBDA(MainData, LET(_Header, CHOOSEROWS(MainData, 1), _OnlyData, DROP(MainData, 1), _Fx_OneRow, LAMBDA(OneRow, LET(_PrepareThirdToOnward, DROP(REDUCE("", DROP(OneRow, , 2), LAMBDA(a,v, HSTACK(a, TEXTSPLIT(v, , " / ")))), , 1), _PrepareFirstTwoCol, IFERROR(REDUCE(TAKE(OneRow, , 2), SEQUENCE(ROWS(_PrepareThirdToOnward) - 1), LAMBDA(a,v, VSTACK(a, TAKE(OneRow, , 2)))), TAKE(OneRow, , 2)), _Result, HSTACK(_PrepareFirstTwoCol, _PrepareThirdToOnward), _Result)), _Fx_ForOneGroup, LAMBDA(MainData,FilterFor, LET(_FilteredData, FILTER(_OnlyData, CHOOSECOLS(_OnlyData, 1) = FilterFor), _OutputForOneGroup, DROP(REDUCE("", SEQUENCE(ROWS(_FilteredData)), LAMBDA(a,v, VSTACK(a, _Fx_OneRow(CHOOSEROWS(_FilteredData, v))))), 1), _TotalItems, ROWS(_OutputForOneGroup), _TotalAmounts, SUM(CHOOSECOLS(_OutputForOneGroup, -1) * 1), _Result, VSTACK(_OutputForOneGroup, HSTACK(FilterFor, "Total Items", _TotalItems, "Total Amount", _TotalAmounts)), _Result)), _UniqueCompany, UNIQUE(CHOOSECOLS(_OnlyData, 1)), _Result, REDUCE(_Header, _UniqueCompany, LAMBDA(a,v, VSTACK(a, _Fx_ForOneGroup(MainData, v)))), _Result))(A1:E6)

Solving the challenge of Company Totals for Items/Amount with Python

Python solution 1 for Company Totals for Items/Amount, proposed by Aman Mashetty: 
df = pd.read_excel("PQ_Challenge_104.xlsx",usecols = 'A:E')
# Split columns and explode
df['PO_Line_No'] = df['PO_Line_No'].str.split(' / ')
df['PO_Items'] = df['PO_Items'].str.split(' / ')
df['PO_Amount'] = df['PO_Amount'].str.split(' / ')
df = df.explode(['PO_Line_No', 'PO_Items', 'PO_Amount'])
df['PO_Amount'] = pd.to_numeric(df['PO_Amount'])
# Calculate total items and total amount for each company
totals = df.groupby('Company').agg(
 Total_Items=('PO_Items', 'size'),
 Total_Amount=('PO_Amount', 'sum')).reset_index()
result = pd.concat(&[df, totals], ignore_index=True)
result = result.fillna('')
result = result.sort_values(by=['Company', 'PO_No']).reset_index(drop=True)
result.loc[result['PO_No'] == '', 'PO_No'] = 'Total Items'
result.loc[result['PO_No'] == 'Total Items', 'PO_Items'] = result.loc[result['PO_No'] == 'Total Items', 'Total_Items']
result.loc[result['PO_No'] == 'Total Items', 'PO_Amount'] = result.loc[result['PO_No'] == 'Total Items', 'Total_Amount']
result.loc[result['PO_No'] == 'Total Items', 'PO_Line_No'] = 'Total Amount'
result = result.drop(columns=['Total_Items', 'Total_Amount'])
print(result)

&&

Leave a Reply