Insert Quarterly total line after Mar, Jun, Jul and Dec.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 646
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Insert Quarterly Totals with Power Query
Power Query solution 1 for Insert Quarterly Totals, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
_ = Table.FromRows(
List.TransformMany(
List.Split(Table.ToRows(Source), 3),
each _ & {{"Quarter Total", List.Sum(List.Zip(_){1})}},
(i, _) => _
),
Value.Type(Source)
)
in
_Power Query solution 2 for Insert Quarterly Totals, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Combine(
List.Transform(
Table.Split(A, 3),
(v) => Table.InsertRows(v, 3, {[Data = "Quarter Total", Value = List.Sum(v[Value])]})
)
)
in
BPower Query solution 3 for Insert Quarterly Totals, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Col = Table.AddColumn(
Origen,
"A",
each try Date.QuarterOfYear(Date.From([Data] & "2024")) otherwise 1
),
Grp = Table.Combine(
Table.Group(
Col,
{"A"},
{
{
"B",
each
let
a = Table.RemoveColumns(_, "A"),
b = Table.ToRows(a) & {{"Quarter Total", List.Sum([Value])}},
c = Table.FromRows(b, Table.ColumnNames(a))
in
c
}
}
)[B]
)
in
GrpPower Query solution 4 for Insert Quarterly Totals, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
List.Transform(
{0, 3, 6, 9},
each [
a = Table.Range(Source, _, 3),
b = Table.InsertRows(a, 3, {[Data = "Quarter Total", Value = List.Sum(a[Value])]})
][b]
)
)
in
ResultPower Query solution 5 for Insert Quarterly Totals, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
List.Transform(
Table.Split(Source, 3),
each Table.InsertRows(_, 3, {[Data = "Quarter Total", Value = List.Sum([Value])]})
)
)
in
ResultPower Query solution 6 for Insert Quarterly Totals, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
List.Transform(
Table.Split(Source, 3),
each Table.Combine({_, hashtag#table(Table.ColumnNames(_), {{"Quarter Total", List.Sum([Value])}})})
)
)
in
Result
Power Query solution 7 for Insert Quarterly Totals, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
List.Transform(
List.Split(Table.ToRows(Source), 3),
each Table.FromRows(
_ & {{"Quarter Total", List.Sum(List.Transform(_, (x) => x{1}))}},
Table.ColumnNames(Source)
)
)
)
in
ResultPower Query solution 8 for Insert Quarterly Totals, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.Split(S, 3),
Fx = (x) =>
let
b = x
& #table(
Table.ColumnNames(x),
{{"Quarter Total"} & List.Transform(List.Skip(Table.ToColumns(x)), List.Sum)}
)
in
b,
Sol = Table.Combine(List.Transform(a, each Fx(_)))
in
SolPower Query solution 9 for Insert Quarterly Totals, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, "Data", {"tbl", Fun1}, 0, Fun2),
Fun1 = each [
A = [Data = "Quarter Total", Value = List.Sum([Value])],
B = Table.InsertRows(_, Table.RowCount(_), {A})
][B],
Fun2 = (x, y) =>
Number.From(Date.QuarterOfYear(Date.From(x & "1")) <> Date.QuarterOfYear(Date.From(y & "1"))),
Res = Table.Combine(Group[tbl])
in
ResPower Query solution 10 for Insert Quarterly Totals, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
fx = (f as text) as number => Date.QuarterOfYear(Date.FromText(f & "1")),
Result = Table.Combine(Table.Group(Source,"Data",{{"New", each _ & hashtag#table(Table.ColumnNames(Source),{{"Qtr Total", List.Sum(_[Value])}}) }},0, (x,y) => Value.Compare(fx(x), fx(y)))[New])
in
Result
Power Query solution 11 for Insert Quarterly Totals, proposed by Rafael González B.:
let
Source = Question_Table,
Grouping = Table.Group(Source, "Data",
{{"All", each _ & hashtag#table({"Data", "Value"}, {{"Total Quater", List.Sum(_[Value])}})}}, 0 ,
(x, y) =>
let
Fx_Q = (d) => Date.QuarterOfYear(Date.From("1" & d)),
a = Number.From(Fx_Q(y) > Fx_Q(x))
in
a)[All]
in
Table.Combine(Grouping)
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 12 for Insert Quarterly Totals, proposed by Peter Krkos:
PowerQuery solution:
= Table.Combine(
List.Transform(Table.Split(Source, 3), (x)=>
Table.InsertRows(x, 3, { [Data = "Quarter Total", Value = List.Sum(x[Value])] })
))Power Query solution 13 for Insert Quarterly Totals, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
List.Transform(
Table.Split(Source, 3),
each Table.Transpose(
Table.DemoteHeaders(
Table.AddColumn(
Table.PromoteHeaders(Table.Transpose(_)),
"Quarter Total",
each List.Sum(Record.ToList(_))
)
),
{"Data", "Value"}
)
)
)
in
ResultPower Query solution 14 for Insert Quarterly Totals, proposed by Ernesto Vega Castillo:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DataType = Table.TransformColumnTypes(Source, {{"Value", type number}}),
Split = Table.FromList(
Table.Split(DataType, 3),
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
Total = Table.AddColumn(Split, "QuarterTotal", each #"QuarterTotalFuct"([Column1])),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Total, {}, "Attribute", "Value")[Value],
Sol = Table.Combine(#"Unpivoted Columns")
in
SolSolving the challenge of Insert Quarterly Totals with Excel
Excel solution 1 for Insert Quarterly Totals, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
A3:A14,
m,
MONTH(
d&1
),
x,
DROP(
GROUPBY(
HSTACK(
MONTH(
m*10
),
m,
d
),
B3:B14,
SUM,
,
2
),
-1,
2
),
IF(
x="",
"Quarter Total",
x
)
)Excel solution 2 for Insert Quarterly Totals, proposed by Rick Rothstein:
=LET(f,LAMBDA(r,t,TOCOL(VSTACK(WRAPCOLS(r,3),t),,1)),HSTACK(f(A3:A14,IF({1,2,3,4},"Quarter Total")),f(B3:B14,BYCOL(WRAPCOLS(B3:B14,3),SUM))))Excel solution 3 for Insert Quarterly Totals, proposed by John V.:
=LET(
d,
A3:A14,
m,
MONTH(
d&1
),
g,
DROP(
GROUPBY(
HSTACK(
MONTH(
10*m
),
m,
d
),
B3:B14,
SUM,
,
2
),
-1,
2
),
IF(
g="",
"Quarter Total",
g
)
)Excel solution 4 for Insert Quarterly Totals, proposed by 🇰🇷 Taeyong Shin:
=LET(d,A2:A14,m,MONTH(1&d),g,DROP(GROUPBY(HSTACK(CEILING(m/3,1),m,d),B2:B14,SUM,3,2),-1,2),IF(g="","Quarter Total",g))Excel solution 5 for Insert Quarterly Totals, proposed by 🇰🇷 Taeyong Shin:
=LET(w,WRAPROWS(B3:B14,3),n,MMULT(w,{1;1;1}),F,LAMBDA(x,y,TOCOL(HSTACK(x,y))),HSTACK(F(WRAPROWS(A3:A14,3),IF(n,"QuarterTotal")),F(w,n)))Excel solution 6 for Insert Quarterly Totals, proposed by Kris Jaganah:
=REDUCE({"Data","Value"},{1;2;3;4},LAMBDA(x,y,VSTACK(x,LET(a,FILTER(A3:B14,INT(SEQUENCE(12,,,1/3))=y),VSTACK(a,HSTACK("Quarter Total",SUM(a)))))))Excel solution 7 for Insert Quarterly Totals, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_data, A3:A14,
_value, B3:B14,
_mnth, MONTH(_data & 1),
_qtr, QUOTIENT(_mnth - 1, 3),
_grp, GROUPBY(HSTACK(_qtr, _mnth, _data), _value, SUM, 0, 2),
_drp, DROP(_grp, -1, 2),
_rtrn, IF(_drp = "", "Qtr Total", _drp),
_rtrn
)Excel solution 8 for Insert Quarterly Totals, proposed by Timothée BLIOT:
=LET(
A,
A3:A14,
B,
B3:B14,
C,
CEILING(
SEQUENCE(
12
)/3,
1
),
D,
DROP(
GROUPBY(
HSTACK(
C,
SEQUENCE(
12
),
A
),
B,
SUM,
,
2
),
-1,
2
),
IF(
D="",
"Quarter Total",
D
)
)Excel solution 9 for Insert Quarterly Totals, proposed by Hussein SATOUR:
=LET(
d,
A3:A14,
a,
DROP(
GROUPBY(
HSTACK(
ROUNDUP(
SEQUENCE(
12
)/3,
0
),
MONTH(
1&d
),
d
),
B3:B14,
SUM,
,
2
),
-1,
2
),
IF(
a="",
"Quarter Total",
a
)
)Excel solution 10 for Insert Quarterly Totals, proposed by Duy Tùng:
=REDUCE(D2:E2,
SEQUENCE(
4
),
LAMBDA(x,
y,
LET(a,
OFFSET(A3,
(y-1)*3,
,
3,
2),
VSTACK(
x,
VSTACK(
a,
IF(
{1,
0},
"Quarter Total",
SUM(
DROP(
a,
,
1
)
)
)
)
))))
Or:
=LET(a,
A3:A14,
b,
B3:B14,
u,
DROP(GROUPBY(HSTACK(INT((MONTH(
1&a
)-1)/3)+1,
XMATCH(
a,
a
),
a),
b,
SUM,
,
2),
-1,
2),
IF(
u="",
"Quarter Total",
u
))Excel solution 11 for Insert Quarterly Totals, proposed by Sunny Baggu:
=LET(
_d,
TOCOL(
VSTACK(
WRAPCOLS(
A3:A14,
3
),
IF(
SEQUENCE(
,
4
),
"Quarter Total"
)
),
,
1
),
_a,
WRAPCOLS(
B3:B14,
3
),
_b,
BYCOL(
_a,
LAMBDA(
a,
SUM(
a
)
)
),
_c,
TOCOL(
VSTACK(
_a,
_b
),
,
1
),
HSTACK(
_d,
_c
)
)Excel solution 12 for Insert Quarterly Totals, proposed by LEONARD OCHEA 🇷🇴:
=LET(
V,
VSTACK,
F,
LAMBDA(
F,
x,
LET(
n,
ROWS(
x
),
IF(
n=3,
V(
x,
HSTACK(
"Quarter Total",
SUM(
TAKE(
x,
,
-1
)
)
)
),
V(
F(
F,
TAKE(
x,
n/2
)
),
F(
F,
DROP(
x,
n/2
)
)
)
)
)
),
F(
F,
A3:B14
)
)Excel solution 13 for Insert Quarterly Totals, proposed by Abdallah Ally:
=REDUCE(
{"Data",
"Value"},
SEQUENCE(
4,
,
,
3
)-1,
LAMBDA(
x,
y,
LET(
a,
OFFSET(
A3,
y,
& ,
3,
2
),
VSTACK(
x,
a,
HSTACK(
"Quarter Total",
SUM(
TAKE(
a,
,
-1
)
)
)
)
)
)
)Excel solution 14 for Insert Quarterly Totals, proposed by Anshu Bantra:
=LET(
data_,
A3:B14,
mnth_,
MONTH(
1&INDEX(
data_,
,
1
)
),
qtrs_,
CEILING(
mnth_/3,
1
),
vals_,
DROP(
GROUPBY(
HSTACK(
qtrs_,
mnth_,
INDEX(
data_,
,
1
)
),
INDEX(
data_,
,
2
),
SUM,
0,
2
),
-1,
2
),
VSTACK(
{"Data",
"Value"},
IF(
vals_="",
"Quarter Totals",
vals_
)
)
)Excel solution 15 for Insert Quarterly Totals, proposed by Md. Zohurul Islam:
=LET(u,A3:A14,v,B3:B14,w,A2:B2,
p,CEILING(MONTH(--(u&1))/12*4,1),
q,REDUCE(w,UNIQUE(p),LAMBDA(x,y,LET(
a,FILTER(HSTACK(u,v),p=y),
b,SUM(FILTER(v,p=y)),
c,HSTACK("Quarter Total",b),
d,VSTACK(x,a,c),d))),
q)Excel solution 16 for Insert Quarterly Totals, proposed by Pieter de B.:
=LET(a,A3:A14,r,ROW(a),s,r-@r,g,DROP(GROUPBY(HSTACK(INT(s/3),s,a),B3:B14,SUM,,2),-1,2),IF(g="","Quarter Total",g))Excel solution 17 for Insert Quarterly Totals, proposed by Hamidi Hamid:
=LET(
y,
WRAPROWS(
B3:B14,
3
),
g,
TOCOL(
HSTACK(
y,
BYROW(
y,
SUM
)
)
),
x,
WRAPROWS(
A3:A14,
3
),
f,
TOCOL(
HSTACK(
x,
BYROW(
x,
LAMBDA(
a,
"Quarter Total"
)
)
)
),
HSTACK(
f,
g
)
)Excel solution 18 for Insert Quarterly Totals, proposed by Asheesh Pahwa:
=LET(r,ROUNDUP(SEQUENCE(12)/3,0),REDUCE(D2:E2,UNIQUE(r),LAMBDA(x,y,VSTACK(x,LET(f,FILTER(A3:B14,r=y),VSTACK(f,HSTACK("Quarter Total",SUM(TAKE(f,,-1)))))))))Excel solution 19 for Insert Quarterly Totals, proposed by ferhat CK:
=LET(
a,
WRAPCOLS(
A3:A14,
3
),
b,
WRAPCOLS(
B3:B14,
3
),
REDUCE(
A2:B2,
SEQUENCE(
4
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
c,
CHOOSECOLS(
b,
y
),
VSTACK(
HSTACK(
CHOOSECOLS(
a,
y
),
c
),
HSTACK(
"Quarter Total",
SUM(
c
)
)
)
)
)
)
)
)Excel solution 20 for Insert Quarterly Totals, proposed by Ankur Sharma:
=LET(d, VALUE(1 & "-" & A3:A14 & "-" & 2025),
m, ROUNDUP(MONTH(d)/3, 0),
g, DROP(GROUPBY(HSTACK(m, d), B3:B14, SUM, , 2, 2), -1, 1),
o, TAKE(g, , 1), s, TAKE(g, , -1),
om, IF(o = "", "Quarter Total", o),
HSTACK(TEXT(om, "mmm"), s))Excel solution 21 for Insert Quarterly Totals, proposed by JvdV –:
=REDUCE(
A2:B2,
{0,
3,
6,
9},
LAMBDA(
x,
y,
LET(
z,
TAKE(
DROP(
A3:B14,
y
),
3
),
VSTACK(
x,
z,
HSTACK(
"Quarter Total",
SUM(
z
)
)
)
)
)
)Excel solution 22 for Insert Quarterly Totals, proposed by Imam Hambali:
=LET(
mn, ROUNDUP(MONTH("01-"&A3:A14)/3,0),
gb, GROUPBY(mn, B3:B14,SUM,0,0),
u, VSTACK(HSTACK(mn, B3:B14,A3:A14), HSTACK(gb, IF(CHOOSECOLS(gb,1), "Quarter Total"))),
VSTACK({"Data","Value"}, CHOOSECOLS(SORT(u,1,1),3,2))
)Excel solution 23 for Insert Quarterly Totals, proposed by Milan Shrimali:
=LET(
A,
LET(
MNTH,
A3:A14,
SAL,
B3:B14,
RNG,
HSTACK(
MNTH,
SAL,
BYROW(
MNTH,
LAMBDA(
X,
ROUNDUP(
MONTH(
DATEVALUE(
1&X
)
)/3,
0
)
)
)
),
REDUCE(
"",
UNIQUE(
CHOOSECOLS(
RNG,
3
)
),
LAMBDA(
X,
Y,
VSTACK(
X,
LET(
A,
FILTER(
FILTER(
RNG,
CHOOSECOLS(
RNG,
3
)=Y
),
{1,
1,
0}
),
VSTACK(
A,
HSTACK(
"QUARTER TOTAL",
SUM(
CHOOSECOLS(
A,
2
)
)
)
)
)
)
)
)
),
FILTER(
A,
CHOOSECOLS(
A,
1
)<>""
)
)Excel solution 24 for Insert Quarterly Totals, proposed by El Badlis Mohd Marzudin:
=LET(
a,
A3:A14,
b,
ROWS(
a
),
c,
DROP(
GROUPBY(
HSTACK(
INT(
SEQUENCE(
b,
,
0
)/3
)+1,
SEQUENCE(
b
),
a
),
B3:B14,
SUM,
,
2
),
-1,
2
),
IF(
c="",
"Quarter Total",
c
)
)Excel solution 25 for Insert Quarterly Totals, proposed by Gabriel Pugliese:
=LET(
m,
TOCOL(
VSTACK(
WRAPCOLS(
TEXT(
SEQUENCE(
12
)*29,
"[$-0809]mmm"
),
3
),
REPT(
"Quarter Total",
SEQUENCE(
,
4,
1,
0
)
)
),
,
1
),
q,
WRAPCOLS(
B3:B14,
3
),
qq,
BYCOL(
q,
SUM
),
t,
TOCOL(
VSTACK(
q,
qq
),
,
1
),
HSTACK(
m,
t
)
)Excel solution 26 for Insert Quarterly Totals, proposed by Fredrick Nwanyanwu:
=LET(
a,
WRAPROWS(
A3:A14,
3
),
b,
WRAPROWS(
B3:B14,
3
),
c,
BYROW(
b,
SUM
),
d,
BYROW(
a,
LAMBDA(
e,
"Quarter Total"
)
),
f,
TOCOL(
HSTACK(
a,
d
)
),
h,
TOCOL(
HSTACK(
b,
c
)
),
r,
HSTACK(
f,
h
),
r
)Solving the challenge of Insert Quarterly Totals with Python
Python solution 1 for Insert Quarterly Totals, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "646 Insert Quarterly Total Line.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=12)
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=17).rename(columns=lambda x: x.split('.')[0])
def insert_quarterly_totals(df):
quarters = {'Q1': ['Jan', 'Feb', 'Mar'], 'Q2': ['Apr', 'May', 'Jun'],
'Q3': ['Jul', 'Aug', 'Sep'], 'Q4': ['Oct', 'Nov', 'Dec']}
result = pd.concat([df[df['Data'].isin(months)]._append(
{'Data': 'Quarter Total', 'Value': df[df['Data'].isin(months)]['Value'].sum()}, ignore_index=True)
for months in quarters.values()])
return result.reset_index(drop=True)
input_with_totals = insert_quarterly_totals(input)
print(all(input_with_totals == test)) # True
Python solution 2 for Insert Quarterly Totals, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_646 - Insert Quarterly Total Line.xlsx'
df = pd.read_excel(io=file_path, usecols='A:B', skiprows=1, nrows=12)
# Perform data manipulation
dfs = []
for i in range(0, len(df), 3):
dfs.append(df.iloc[i : i + 3].reset_index(drop=True))
dfs[-1].loc[3] = ['Quarter Total', dfs[-1].Value.sum()]
df = pd.concat(objs=dfs, ignore_index=True)
df
Solving the challenge of Insert Quarterly Totals with Python in Excel
Python in Excel solution 1 for Insert Quarterly Totals, proposed by Alejandro Campos:
df = xl("A2:B14", headers=True)
def insert_quarterly_totals(df):
new_rows, qsum = [], 0
for _, row in df.iterrows():
new_rows.append(row)
qsum += row["Value"]
if row["Data"] in ["Mar", "Jun", "Sep", "Dec"]:
new_rows.append(pd.Series({"Data": f"{row['Data']} Qtr Total", "Value": qsum}))
qsum = 0
return pd.DataFrame(new_rows).reset_index(drop=True)
df = insert_quarterly_totals(df)
Python in Excel solution 2 for Insert Quarterly Totals, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:B14", True)
df["Q"] = pd.to_datetime(df["Data"], format="%b").dt.quarter
result = sum(
[
[*group.values.tolist(), ["Quarter Total", group["Value"].sum(), ""]]
for _, group in df.groupby("Q")
],
[],
)
result = pd.DataFrame(result, columns=["Data", "Value", "Q"])[["Data", "Value"]]
result
Python in Excel solution 3 for Insert Quarterly Totals, proposed by Anshu Bantra:
import pandas as pd
df = pd.DataFrame({
'Data': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
'Value': [47, 90, 41, 75, 92, 81, 51, 23, 34, 71, 72, 59]
})
df['Date'] = pd.to_datetime(df['Data'], format='%b')
df.set_index('Date', inplace=True)
# Calculate quarterly totals
quarterly_totals = df.resample('QE').sum()
# Combine original data with quarterly totals
combined_df = pd.concat([df, quarterly_totals]).sort_index()
# Add Quarter Total
combined_df['Data'] = combined_df['Data'].apply(lambda x: 'Quarter Total' if len(x)> 3 else x )
combined_df.reset_index(inplace=True, drop=True)
combined_df
Solving the challenge of Insert Quarterly Totals with R
R solution 1 for Insert Quarterly Totals, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/646 Insert Quarterly Total Line.xlsx"
input = read_excel(path, range = "A2:B14")
test = read_excel(path, range = "D2:E18")
result = input %>%
mutate(Quarter = rep(1:4, each = 3))
qt = result %>%
summarise(Data = "Quarter Total", Value = sum(Value), .by = Quarter) %>%
bind_rows(result) %>%
arrange(Quarter, grepl("Total", Data)) %>%
select(-Quarter)
all.equal(qt, test)
# [1] TRUE
&&