Summarise the table as shown with a total row after each sales person’s data.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 266
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Summarise the table with total with Power Query
Power Query solution 1 for Summarise the table with total, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Sort(A, {{"Sales Person", 0}, {"Item", 0}}),
C = Table.Combine(
Table.Group(
B,
"Sales Person",
{
"All",
(v) =>
[
a = Table.Group(
v,
{"Sales Person", "Item"},
{
{"Total Orders", each List.Count([Date])},
{"First Order Date", each List.Min([Date])},
{"Last Order Date", each List.Max([Date])}
}
),
b = Table.ToColumns(a),
c = a
& Table.FromRows(
{{b{0}{0} & " Total", null, List.Sum(b{2}), List.Min(b{3}), List.Max(b{4})}},
Table.ColumnNames(a)
)
][c]
},
0
)[All]
)
in
CPower Query solution 2 for Summarise the table with total, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Combine(
Table.Sort(
Table.Group(
Source,
"Sales Person",
{
{
"A",
each
let
a = _,
b = Table.Group(
a,
"Item",
{
{"Sales Person", each [Sales Person]{0}},
{"Total Orders", each Table.RowCount(_)},
{"First Order Date", each [Date]{0}},
{"Last Order Date", each List.Last([Date])}
}
),
c = {
{null},
{a[Sales Person]{0} & " Total"},
{List.Sum(b[Total Orders])},
{List.Min(b[First Order Date])},
{List.Max(b[First Order Date])}
},
d = Table.Sort(b, "Item") & Table.FromColumns(c, Table.ColumnNames(b))
in
d
}
}
),
"Sales Person"
)[A]
),
Sol = Table.ReorderColumns(
Grp,
List.Reverse(List.Skip(Table.ColumnNames(Source))) & List.Skip(Table.ColumnNames(Grp), 2)
)
in
SolPower Query solution 3 for Summarise the table with total, proposed by Luan Rodrigues:
let
Fonte = Table.Group(Tabela1, {"Sales Person","Item"}, {{"tab", each
let
a = if Table.RowCount(_) > 1 then Table.FromRows({{List.Min(_[Date]),List.Max(_[Date])}},{"First Order Date","Last Order Date"}) else
Table.FromRows({_[Date]&_[Date]},{"First Order Date","Last Order Date"}),
b = Table.AddColumn(a,"Total Orders", (x)=> Table.RowCount(_) )
in b }}),
exp = Table.ExpandTableColumn(Fonte, "tab", Table.ColumnNames(Fonte[tab]{0}) ),
group = Table.Group(exp, {"Sales Person"}, {
{"tab", each Table.Sort(_,{"Item"}) &
hashtag#table(Table.ColumnNames(_),{{_[Sales Person]{0}& " Total",null,List.Min(_[First Order Date]) ,List.Max(_[Last Order Date]),List.Sum(_[Total Orders]) }} ) }})[tab],
res = Table.Combine(group)
in
res
Power Query solution 4 for Summarise the table with total, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupRows = Table.Group(Source, {"Sales Person"}, {{"All", each
Table.Group(_, {"Sales Person","Item"}, {{"Tot Orders", each Table.RowCount(_)}, {"First Ord", each List.Min([Date])}, {"Last Ord", each List.Max([Date])}})}}),
AddTots = Table.AddColumn(GroupRows, "Custom", each let
a = [All]
in
[All] &
hashtag#table(
{"Sales Person", "Item", "Tot Orders", "First Ord", "Last Ord"},
{{[Sales Person]&" Total", "", List.Sum(a[Tot Orders]), List.Min(a[First Ord]), List.Max(a[Last Ord])}})),
RemovCols = Table.RemoveColumns(AddTots,{"Sales Person", "All"}),
Expand = Table.ExpandTableColumn(RemovCols, "Custom", {"Sales Person", "Item", "Tot Orders", "First Ord", "Last Ord"}),
Sort = Table.Sort(Expand,{{"Sales Person", Order.Ascending}, {"Item", Order.Ascending}})
in Sort
Power Query solution 5 for Summarise the table with total, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData266"]}[Content],
CN = {"Sales Person", "Item", "Total Orders", "First Order", "Last Order"},
LFx = {(t) => Table.RowCount(t), (t) => List.Min(t[Date]), (t) => List.Max(t[Date])},
ChgType = Table.TransformColumnTypes(Source, {"Date", type date}),
Group = Table.Group(
ChgType,
CN{0},
{
{
"D",
each
let
_G = Table.Group(_, {CN{0}, CN{1}}, List.Zip({{CN{2}, CN{3}, CN{4}}, LFx}))
in
Table.Sort(_G, "Item")
},
{
"ST",
each Table.FromRows(
{{[Sales Person]{0} & "Total", null, LFx{0}(_), LFx{1}(_), LFx{2}(_)}},
CN
)
}
}
),
CombineST = Table.CombineColumns(Group, {"D", "ST"}, Table.Combine, "G"),
CombineAll = Table.Combine(Table.Sort(CombineST, CN{0})[G])
in
CombineAllPower Query solution 6 for Summarise the table with total, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ColNames = {"Sales Person", "Item", "Total Orders", "First Order Date", "Last Order Date"},
Fun = (x as list) =>
Table.Group(
Source,
x,
{
{ColNames{2}, each Table.RowCount(_)},
{ColNames{3}, each List.Min([Date])},
{ColNames{4}, each List.Max([Date])}
}
),
Res = [
A = Table.TransformColumns(Fun({ColNames{0}}), {ColNames{0}, each _ & " Total"}),
B = Fun({ColNames{0}, ColNames{1}}),
C = Table.ReorderColumns(Table.Sort(A & B, {ColNames{0}, ColNames{1}}), ColNames)
][C]
in
ResPower Query solution 7 for Summarise the table with total, proposed by Ankur Sharma:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChngdTyp = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item", type text}, {"Sales Person", type text}}),
GroupRows1 = Table.Group(ChngdTyp, {"Sales Person", "Item"}, {{"Total Orders", each Table.RowCount(_)}, {"First Order Date", each List.Min([Date])}, {"Last Order Date", each List.Max([Date])}}),
GroupRows2 = Table.Group(ChngdTyp, {"Sales Person"}, {{"Total Orders", each List.Count([Date])}, {"First Order Date", each List.Min([Date])}, {"Last Order Date", each List.Max([Date])}}),
Append = Table.Combine({GroupRows1, GroupRows2}),
Sort = Table.Sort(Append, {{"Sales Person", Order.Ascending}, {each List.PositionOf({"A", "B", "C", "D", null}, [Item]), Order.Ascending}})
in
Sort
Best Wishes!
Power Query solution 8 for Summarise the table with total, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Sales Person", "Item"},
{
{"Total Orders", each Table.RowCount(_)},
{"First Order Date", each List.Min([Date])},
{"Last Order Date", each List.Max([Date])}
}
),
fx = (sp) => Table.SelectRows(Group, (f) => f[Sales Person] = sp),
TotalRowAdd = Table.Sort(
Group
& Table.FromRows(
List.Transform(
List.Distinct(Group[Sales Person]),
each {
_ & " Total",
"",
List.Sum(fx(_)[Total Orders]),
List.Min(fx(_)[First Order Date]),
List.Max(fx(_)[Last Order Date])
}
),
Table.ColumnNames(Group)
),
{"Sales Person", "Item"}
),
Result = Table.TransformColumns(
TotalRowAdd,
List.Transform(
List.Select(Table.ColumnNames(TotalRowAdd), (x) => Text.Contains(x, "Date")),
each {_, each DateTime.ToText(_, "M-dd-yy")}
)
)
in
ResultPower Query solution 9 for Summarise the table with total, proposed by Peter Krkos:
PowerQuery solution:
Table.Sort(Table.Combine(Table.Group(Source, {"Sales Person"}, {{"T", each
[ GroupedRows2 = Table.Combine(Table.Group(_, {"Item"}, {{"T2", each
{{ _{0}[Sales Person], _{0}[Item], Table.RowCount(_), List.Min([Date]), List.Max([Date])}}
), type table}})[T2]),
SortedRows = Table.Sort(GroupedRows2,{{"Item", Order.Ascending}}),
Ad_TotalRow = Table.InsertRows(SortedRows, Table.RowCount(SortedRows),
{ SortedRows{0} &
[ Sales Person = SortedRows{0}[Sales Person] & " " & "Total",
Item = null,
Total Orders = List.Sum(SortedRows[Total Orders]),
Last Order Date = List.Max(SortedRows[Last Order Date])
] } )
][Ad_TotalRow], type table}})[T]), {{"Sales Person", Order.Ascending}})
Power Query solution 10 for Summarise the table with total, proposed by Alexandre Garcia:
let
U = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
H = List.Transform,
P = {"Sales Person", "Item", "Total Orders", "First Order Date", "Last Order Date"},
L = List.Sort(List.Distinct(U[Sales Person])),
C = Table.Pivot(U, L, "Sales Person", "Date", each let x = H(_, Date.From) in if _ <> {} then {List.Count(_), List.Min(x), List.Max(x)} else {}),
M = List.Accumulate(L, {},(s,c)=> s & {Table.FromRows(List.RemoveNulls(H(List.Zip({C[Item],Table.Column(C,c)}), each if List.IsEmpty(_{1}) then null else {c,_{0}} & _{1})),P)}),
S = Table.Combine(H(M, (x)=> x & hashtag#table(P, {{x[Sales Person]{0} & " Total", null} & H({{2,List.Sum}, {3,List.Min}, {4, List.Max}}, each _{1}(Table.ToColumns(x){_{0}}))})))
in S
Power Query solution 11 for Summarise the table with total, proposed by Erdit Qendro:
let
Source = Excel.CurrentWorkbook(){[Name = "TB"]}[Content],
GRows = Table.Group(
Source,
{"Sales Person", "Item"},
{
{"Total Orders", each Table.RowCount(_), Int64.Type},
{"First Order Date", each List.Min([Date]), type nullable datetime},
{"Last Order Date", each List.Max([Date]), type nullable datetime}
}
),
GRows1 = Table.Group(
GRows,
{"Sales Person"},
{
{
"AllRows",
each _,
type table [
Sales Person = nullable text,
Item = nullable text,
First Date = nullable datetime,
Last Date = nullable datetime,
Total Orders = number
]
}
}
),
TotalRow = Table.AddColumn(
GRows1,
"TotalRow",
each [
Sales Person = List.First(Table.Column([AllRows], "Sales Person")) & " Total",
Item = "",
Total Orders = List.Sum(Table.Column([AllRows], "Total Orders")),
First Order Date = List.Min(Table.Column([AllRows], "First Order Date")),
Last Order Date = List.Max(Table.Column([AllRows], "Last Order Date"))
]
),
MergedTable = Table.AddColumn(
TotalRow,
"Custom",
each Table.InsertRows([AllRows], Table.RowCount([AllRows]), {[TotalRow]})
),
CombineTable = Table.Combine(MergedTable[Custom]),
SortTable = Table.Sort(
CombineTable,
{{"Sales Person", Order.Ascending}, {"Item", Order.Ascending}}
)
in
SortTablePower Query solution 12 for Summarise the table with total, proposed by Krupesh Bhansali:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}}),
Details = Table.Group(
#"Changed Type",
{"Sales Person", "Item"},
{
{"Total Order", each Table.RowCount(_), Int64.Type},
{"First Order Date", each List.Min([Date]), type date},
{"Last Order DAte", each List.Max([Date]), type date}
}
),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"Sales Person"},
{
{"Total Order", each Table.RowCount(_), Int64.Type},
{"First Order Date", each List.Min([Date]), type date},
{"Last Order DAte", each List.Max([Date]), type date}
}
),
Total = Table.TransformColumns(#"Grouped Rows", {{"Sales Person", each _ & " Total", type text}}),
Custom1 = Total & Details,
#"Reordered Columns" = Table.ReorderColumns(
Custom1,
{"Sales Person", "Item", "Total Order", "First Order Date", "Last Order DAte"}
),
#"Sorted Rows" = Table.Sort(
#"Reordered Columns",
{{"Sales Person", Order.Ascending}, {"Item", Order.Ascending}}
)
in
#"Sorted Rows"Power Query solution 13 for Summarise the table with total, proposed by Le Ngoc Tinh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
CT = Table.TransformColumnTypes(Source, {{"Date", type date}}),
GR = Table.Group(
CT,
{"Sales Person"},
{
"T",
(g) =>
let
tbl = Table.Sort(
Table.Group(
g,
{"Sales Person", "Item"},
{
{"Total Orders", each List.Count([Date])},
{"First Order Date", each List.Min([Date])},
{"Last Order Date", each List.Max([Date])}
}
),
{"Item", 0}
)
in
Table.InsertRows(
tbl,
Table.RowCount(tbl),
{
[
Sales Person = tbl[Sales Person]{0} & " Total",
Item = null,
Total Orders = List.Sum(tbl[Total Orders]),
First Order Date = List.Min(tbl[First Order Date]),
Last Order Date = List.Max(tbl[Last Order Date])
]
}
)
}
),
SORT = Table.Sort(GR, {{"Sales Person", Order.Ascending}}),
RE = Table.Combine(SORT[T])
in
RESolving the challenge of Summarise the table with total with Excel
Excel solution 1 for Summarise the table with total, proposed by Bo Rydobon 🇹🇭:
=LET(g,
DROP(
DROP(
GROUPBY(
SORTBY(
B1:C20,
{2,
1}
),
A1:A20,
HSTACK(
ROWS,
MIN,
MAX
),
3,
2
),
1
),
-1
),
IF((INDEX(
g,
,
2
)="")*(SEQUENCE(
,
5
)=1),
g&" Total",
IF(
g="date",
{0,
0,
"Total Orders",
"First Order Date",
"Last Order Date"},
g
)))Excel solution 2 for Summarise the table with total, proposed by Kris Jaganah:
=LET(
p,
C2:C20,
REDUCE(
{"Sales Person",
"Item",
"Total Orders",
"First Order Date",
"Last Order Date"},
SORT(
UNIQUE(
p
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
DROP(
GROUPBY(
HSTACK(
p,
B2:B20
),
A2:A20,
HSTACK(
COUNT,
MIN,
MAX
& ),
,
1,
,
p=y
),
1
),
IF(
a="Total",
TAKE(
a,
1,
1
)&" "&a,
a
)
)
)
)
)
)Excel solution 3 for Summarise the table with total, proposed by Duy Tùng:
=LET(
a,
DROP(
DROP(
GROUPBY(
HSTACK(
C2:C20,
B2:B20
),
A2:A20,
HSTACK(
ROWS,
MIN,
MAX
),
,
2
),
-1
),
1
),
HSTACK(
TAKE(
a,
,
1
)&IF(
INDEX(
a,
,
2
)>"",
"",
" Total"
),
DROP(
a,
,
1
)
)
)Excel solution 4 for Summarise the table with total, proposed by Sunny Baggu:
=LET(
arr, SORTBY(SORT(A2:C20, {3, 2}, ), {3, 2, 1}),
_u, UNIQUE(TAKE(arr, , 1)),
REDUCE(
HSTACK(C1, B1, "Total Orders", "First Order Date", "Last Order Date"),
_u,
LAMBDA(g, h,
VSTACK(
g,
LET(
_b, FILTER(TAKE(arr, , -2), TAKE(arr, , 1) = h),
_c, UNIQUE(TAKE(_b, , 1)),
_v, DROP(
REDUCE(
"",
_c,
LAMBDA(x, y,
VSTACK(
x,
LET(
_a, FILTER(TAKE(_b, , -1), TAKE(_b, , 1) = y),
_r, ROWS(_a),
_d, IF(_r = 1, HSTACK(TAKE(_a, 1), TAKE(_a, 1)), HSTACK(TAKE(_a, 1), TAKE(_a, -1))),
HSTACK(y, _r, _d)
) ) ) ), 1),
_u, IFNA(HSTACK(h, _v), h),
VSTACK(_u, HSTACK(h & " Total", "", SUM(INDEX(_u, , 3)), TAKE(SORT(TOCOL(TAKE(_u, , -2))), {1, -1}, ))) ) ) ) ))Excel solution 5 for Summarise the table with total, proposed by Md. Zohurul Islam:
=LET(
u,
A2:B20,
v,
C2:C20,
unq,
SORT(
UNIQUE(
v
)
),
hdr,
HSTACK(
C1,
B1,
"Total Orders",
"First Order Date",
"Last Order Date"
),
w,
LAMBDA(
a,
b,
DROP(
GROUPBY(
a,
b,
HSTACK(
COUNT,
MIN,
MAX
),
0,
1
),
1
)
),
z,
REDUCE(
hdr,
unq,
LAMBDA(
x,
y,
LET(
a,
FILTER(
u,
v=y
),
b,
w(
TAKE(
a,
,
-1
),
DROP(
a,
,
-1
)
),
c,
IF(
TAKE(
b,
,
1
)="Total",
y&" "&"Total",
y
),
d,
HSTACK(
c,
b
),
e,
IF(
d="Total",
"",
d
),
f,
VSTACK(
x,
e
),
f
)
)
),
z
)Excel solution 6 for Summarise the table with total, proposed by Pieter de B.:
=LET(
a,
A2:C20,
c,
CHOOSECOLS,
h,
HSTACK,
g,
GROUPBY(
c(
a,
3,
2
),
c(
a,
1
),
h(
ROWS,
MIN,
MAX
),
,
2
),
d,
DROP(
g,
-1
),
IF(
SEQUENCE(
ROWS(
g
)-1
)-1,
IF(
c(
d,
2,
1,
3,
4,
5
)="",
d&" Total",
d
),
HSTACK(
C1,
B1,
"Total Orders",
{"First",
"Last"}&" Order"
)
)
)Excel solution 7 for Summarise the table with total, proposed by Hamidi Hamid:
=LET(
as,
HSTACK(
C2:C20,
B2:B20
),
x,
DROP(
GROUPBY(
as,
A2:A20,
COUNTA,
,
2
),
-1
),
y,
DROP(
DROP(
GROUPBY(
as,
A2:A20,
HSTACK(
MIN,
MAX
),
,
2
),
-1
),
1,
2
),
h,
HSTACK(
x,
y
),
k,
IF(
CHOOSECOLS(
h,
2
)="",
TAKE(
h,
,
1
)&" "&"Total",
TAKE(
h,
,
1
)
),
w,
HSTACK(
k,
DROP(
h,
,
1
)
),
w
)Excel solution 8 for Summarise the table with total, proposed by Asheesh Pahwa:
=LET(
sp,
C2:C20,
u,
SORT(
UNIQUE(
C2:C20
)
),
d,
A2:A20,
itm,
B2:B20,
REDUCE(
E1:I1,
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
HSTACK(
d,
itm
),
sp=y
),
t,
TAKE(
f,
,
-1
),
ui,
SORT(
UNIQUE(
t
)
),
d,
DROP(
REDUCE(
"",
ui,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
_f,
FILTER(
TAKE(
f,
,
1
),
t=v
),
c,
COUNTA(
_f
),
HSTACK(
v,
c,
TAKE(
_f,
1
),
TAKE(
_f,
-1
)
)
)
)
)
),
1
),
s,
SUM(
INDEX(
d,
,
2
)
),
mn,
MIN(
INDEX(
d,
,
3
)
),
mx,
MAX(
INDEX(
d,
,
4
)
),
VSTACK(
IFNA(
HSTACK(
y,
d
),
y
),
HSTACK(
y&" Total",
"",
s,
mn,
mx
)
)
)
)
)
)
)Excel solution 9 for Summarise the table with total, proposed by ferhat CK:
=DROP(
REDUCE(
0,
SORT(
UNIQUE(
C2:C20
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
TAKE(
SORT(
FILTER(
A2:C20,
C2:C20=y
),
2
),
,
2
),
g,
DROP(
GROUPBY(
TAKE(
f,
,
-1
),
TAKE(
f,
,
1
),
HSTACK(
COUNTA,
MIN,
MAX
)
),
1
),
r,
IFERROR(
SEQUENCE(
ROWS(
g
)-1
)/0,
y
),
IFNA(
HSTACK(
r,
IF(
g="Total",
"",
g
)
),
y&" Total"
)
)
)
)
),
1
)Excel solution 10 for Summarise the table with total, proposed by Ankur Sharma:
=DROP(DROP(GROUPBY(HSTACK(C2:C20, B2:B20), A2:A20, HSTACK(COUNT, MIN, MAX), , 2), 1), -1)Excel solution 11 for Summarise the table with total, proposed by Meganathan Elumalai:
=LET(
I,
INDEX,
f,
"m-dd-yy",
g,
DROP(
DROP(
GROUPBY(
HSTACK(
C2:C20,
B2:B20
),
A2:A20,
HSTACK(
COUNT,
LAMBDA(
n,
TEXT(
MIN(
n
),
f
)
),
LAMBDA(
n,
TEXT(
MAX(
n
),
f
)
)
),
,
2
),
1
),
-1
),
HSTACK(
IF(
I(
g,
,
2
)="",
I(
g,
,
1
)&" Total",
I(
g,
,
1
)
),
DROP(
g,
,
1
)
)
)Excel solution 12 for Summarise the table with total, proposed by Imam Hambali:
=LET(
d,
DROP,
cc,
CHOOSECOLS,
a,
d(
d(
GROUPBY(
HSTACK(
C2:C20,
B2:B20
),
A2:A20,
HSTACK(
COUNTA,
MIN,
MAX
),
0,
2
),
-1
),
1
),
VSTACK(
{"Sales Person",
"Item",
"Total Orders",
"First Order Date",
"Last Order Date"},
HSTACK(
IF(
cc(
a,
2
)="",
cc(
a,
1
)&" Total",
cc(
a,
1
)
),
d(
a,
,
1
)
)
)
)Excel solution 13 for Summarise the table with total, proposed by Ezel K.:
="";
G&" Total";
G)));
DROP(
XX;
;
1
)))Solving the challenge of Summarise the table with total with Python
Python solution 1 for Summarise the table with total, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "PQ_Challenge_266.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=20)
test = pd.read_excel(path, usecols="E:I", nrows=17).rename(columns=lambda col: col.split('.')[0])
R1 = input.groupby(['Sales Person', 'Item']).agg(
Total_Orders=('Date', 'count'),
First_Order_Date=('Date', 'min'),
Last_Order_Date=('Date', 'max')
).reset_index()
R2 = input.groupby('Sales Person').agg(
Total_Orders=('Date', 'count'),
First_Order_Date=('Date', 'min'),
Last_Order_Date=('Date', 'max')
).reset_index()
R2['Item'] = np.NaN
R2['Sales Person'] = R2['Sales Person'] + " Total"
result = pd.concat([R1, R2]).sort_values(by=['Sales Person', 'Item']).reset_index(drop=True)
result = result[['Sales Person', 'Item', 'Total_Orders', 'First_Order_Date', 'Last_Order_Date']]
result.columns = result.columns.str.replace('_', ' ')
print(result.equals(test)) # True
Python solution 2 for Summarise the table with total, proposed by Luan Rodrigues:
import pandas as pd
file = r"PQ_Challenge_266.xlsx"
df = pd.read_excel(file,usecols="A:C")
grp = df.groupby(["Sales Person","Item"]).apply(lambda x: [len(x['Date']),min(x['Date']),max(x['Date'])]).reset_index()
del grp[0]
grp = grp.groupby("Sales Person").apply(lambda x: pd.concat([x, pd.DataFrame({
"Sales Person": [x['Sales Person'].iloc[0] + " Total"],
"Item": [""],
"Total Orders": [x["Total Orders"].sum()],
"Last Order Date": [x["Last Order Date"].max()]
})])).reset_index(drop=True)
print(grp)
Solving the challenge of Summarise the table with total with Python in Excel
Python in Excel solution 1 for Summarise the table with total, proposed by Alejandro Campos:
df = xl("A1:C20", headers=True)
df["Date"] = pd.to_datetime(df["Date"], format="%d/%m/%Y")
def summarize(group):
summary = group.groupby('Item').agg(Total_Orders=('Item', 'size'),
First_Order_Date=('Date', 'min'),
Last_Order_Date=('Date', 'max')).reset_index()
return pd.concat([summary.assign(**{'Sales Person': group['Sales Person'].iloc[0]}),
pd.DataFrame({'Sales Person': [group['Sales Person'].iloc[0] + ' Total'],
'Item': [''], 'Total_Orders': [summary['Total_Orders'].sum()],
'First_Order_Date': [summary['First_Order_Date'].min()],
'Last_Order_Date': [summary['Last_Order_Date'].max()]})])
result = df.groupby('Sales Person').apply(summarize).reset_index(
drop=True)[['Sales Person', 'Item', 'Total_Orders', 'First_Order_Date', 'Last_Order_Date']]
Python in Excel solution 2 for Summarise the table with total, proposed by Francesco Bianchi 🇮🇹:
df=xl("A1:C20", headers=True)
g1 = df.groupby(['Sales Person','Item']).agg(
Total_Orders = ('Date','count'),
First_Order_Date=('Date','min'),
Last_Order_Date= ('Date','max')
).reset_index()
g2 = g1.groupby('Sales Person')
def add_total_row(group):
return pd.concat([group,
pd.DataFrame([{
'Sales Person': group['Sales Person'].unique()[0],
'Item': '',
'Total_Orders': group['Total_Orders'].sum(),
'First_Order_Date': group['First_Order_Date'].min(),
'Last_Order_Date': group['Last_Order_Date'].max()
}])]
)
df_res = pd.DataFrame()
for person in g2['Sales Person'].unique():
g2_group = g2.get_group(person[0])
g2_group_with_total = add_total_row(g2_group)
df_res= pd.concat([df_res,g2_group_with_total])
df_res['Sales Person'] = df_res.apply(lambda x: x['Sales Person'] + ' Total' if x['Item'] == '' else x['Sales Person'], axis=1)
df_res.columns = df_res.columns.str.replace('_', ' ')
df_res.reset_index(drop=True)
Solving the challenge of Summarise the table with total with R
R solution 1 for Summarise the table with total, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_266.xlsx"
input = read_excel(path, range = "A1:C20")
test = read_excel(path, range = "E1:I18")
R1 = input %>%
summarise(`Total Orders` = n(),
`Last Order Date` = max(Date),
.by = c(`Sales Person`, Item))
R2 = input %>%
summarise(`Total Orders` = n(),
`Last Order Date` = max(Date),
Item = NA,
.by = `Sales Person`) %>%
mutate(`Sales Person` = paste0(`Sales Person`, " Total"))
result = bind_rows(R1, R2) %>%
arrange(`Sales Person`, Item) %>%
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
&