Summarize the table as shown by summing up Sales against each month. In a quarter, first 2 months have 4 weeks but 3rd month will have 5 weeks.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 230
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum Sales By Month with Power Query
Power Query solution 1 for Sum Sales By Month, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRows(
List.Accumulate(
List.TransformMany(List.Split(Table.ToColumns(Source), 2), List.Zip, (i, _) => _{1} ?? _{0}),
{},
(b, n) =>
let
l = List.Last(b),
f = Byte.From(n is number)
in
List.RemoveLastN(b, f) & {{{n, 0}, {l{0}, l{1} + n}}{f}}
),
{"Month", "Sale"}
)
in
SPower Query solution 2 for Sum Sales By Month, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.SelectColumns(A, List.Select(Table.ColumnNames(A), each Text.Contains(_, "Sale"))),
C = Table.AddIndexColumn(B, "Id", 1, 1 / 5.1),
D = Table.TransformColumns(C, {"Id", each Number.IntegerDivide(_, 1)}),
E = Table.UnpivotOtherColumns(D, {"Id"}, "A", "V"),
F = Table.Sort(E, {{"A", 0}, {"Id", 0}}),
G = Table.AddColumn(F, "P", each Text.From([Id]) & [A]),
H = Table.AddColumn(
G,
"Month",
each Text.Start(Date.MonthName((List.PositionOf(List.Distinct(G[P]), [P]) + 1) * 28.5), 3)
),
I = Table.Group(H, {"Month"}, {"Sale", each List.Sum([V])})
in
IPower Query solution 3 for Sum Sales By Month, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content]
meta [Table = "A1:H17", Header = false],
Skip = Table.Skip(Source),
Group = Table.Group(
Skip,
"Column2",
{
"L",
each [
S = Table.Skip(_, 1),
Tc = Table.ToColumns(S),
Alt = List.Alternate(Tc, 1, 1),
T = List.Transform(Alt, List.Sum),
Fr = Record.FieldValues(_{0}),
Rn = List.RemoveNulls(Fr),
R = List.Zip({Rn, T})
][R]
},
0,
(x, y) => Number.From(y = null)
),
Combine = List.Combine(Group[L]),
Table = Table.FromRows(Combine, {"Month", "Sales"}),
Return = Table.Sort(Table, each Date.From([Month] & "1"))
in
ReturnPower Query solution 4 for Sum Sales By Month, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl = Table.Combine(
List.Transform(
List.Split(Table.ToColumns(Source), 2),
each Table.FromColumns(_, {"Month", "A"})
)
),
Sol = Table.Group(
Tbl,
"Month",
{{"Sales", each List.Sum([A])}},
0,
(a, b) => Number.From(not Text.Contains(b, "Week"))
)
in
SolPower Query solution 5 for Sum Sales By Month, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
grp = Table.Group(
Fonte,
{"Month"},
{
{
"tab",
each
let
a = List.Transform(List.Split(Table.ToColumns(_), 2), Table.FromColumns),
b = List.Transform(
a,
each Table.FromRows(
List.Zip({{_[Column1]{0}}, {List.Sum(_[Column2])}}),
{"Month", "Sales"}
)
)
in
b
}
},
0,
(a, b) => Number.From(not Text.StartsWith(b[Month], "Week"))
)[tab],
res = Table.Combine(List.Transform(List.Zip(grp), Table.Combine))
in
resPower Query solution 6 for Sum Sales By Month, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform1 = List.Transform(
Table.ToRows(Source),
each List.Transform(_, (x) => try if Text.Start(x, 1) = "W" then null else x otherwise x)
),
FromRows = Table.FromRows(Transform1, Table.ColumnNames(Source)),
ColsToFill = List.Select(Table.ColumnNames(Source), each Text.Start(_, 1) = "M"),
ToRows = Table.ToRows(Table.FillDown(FromRows, ColsToFill)),
Transform2 = List.Transform(ToRows, each List.Transform(List.Split(_, 2), each {_{0}, _{1}})),
FromRecords = Table.FromRows(List.Combine(Transform2), {"Month", "Sale"}),
Group = Table.Group(FromRecords, "Month", {"Sale", each List.Sum([Sale])}),
Result = Table.Sort(Group, each Date.From("1" & [Month]))
in
ResultPower Query solution 7 for Sum Sales By Month, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Transform,
R = List.Range,
a = Table.ToColumns(S),
b = List.Combine(L(List.Alternate(a, 1, 1, 1), each {_{0}} & {_{5}} & {_{10}})),
c = L(List.Alternate(a, 1, 1), List.RemoveNulls),
d = L(c, each {R(_, 0, 4)} & {R(_, 4, 4)} & {R(_, 8)}),
e = List.Combine(L(d, each L(_, List.Sum))),
f = Table.FromColumns({b, e}),
Sol = Table.RenameColumns(
f,
List.Zip({Table.ColumnNames(f), List.FirstN(Table.ColumnNames(S), 2)})
)
in
SolPower Query solution 8 for Sum Sales By Month, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData230"]}[Content],
Transform = Table.FromRows(
List.Combine(List.Transform(List.Split(Table.ToColumns(Source), 2), List.Zip)),
{"Month", "Sale"}
),
Group = Table.Group(
Transform,
"Month",
{"Sale", each List.Sum([Sale])},
GroupKind.Local,
(p, c) => Number.From(not (Text.StartsWith(c, "Week")))
)
in
GroupPower Query solution 9 for Sum Sales By Month, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.FromColumns(
{
List.Combine(List.Alternate(Table.ToColumns(S), 1, 1, 1)),
List.Combine(List.Alternate(Table.ToColumns(S), 1, 1))
},
{"M", "Sales"}
),
b = Table.AddColumn(a, "Month", each if Text.Contains([M], "Week") then null else [M]),
c = Table.FillDown(b, {"Month"}),
d = Table.Group(c, {"Month"}, {{"Sales", each List.Sum([Sales])}})
in
dPower Query solution 10 for Sum Sales By Month, proposed by Yaroslav Drohomyretskyi:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Zip = List.Zip(
{S[Month] & S[Month2] & S[Month4] & S[Month6], S[Sale] & S[Sale3] & S[Sale5] & S[Sale7]}
),
HSTACK = Table.Transpose(Table.FromColumns(Zip), {"Month", "Sale"}),
Clean = Table.FillDown(
Table.TransformColumns(HSTACK, {{"Month", each if Text.StartsWith(_, "Week") then null else _}}),
{"Month"}
),
Group = Table.Group(Clean, {"Month"}, {{"Sale", each List.Sum([Sale]), type nullable number}})
in
GroupPower Query solution 11 for Sum Sales By Month, proposed by Ahmed Ariem:
let
f1 = (w) =>
[
a = Table.ReplaceValue(
w,
(x) => x,
(x) => x,
(x, y, z) => if Text.Contains(x, "W") then null else x,
Table.ColumnNames(w)
),
b = Table.FillDown(a, {"Month", "Month2", "Month4", "Month6"}),
c = Table.SelectRows(b, each ([Sale] <> null))
][c],
f2 = (x) =>
Table.FromRows(
List.Combine(List.TransformMany(Table.ToRows(x), (w) => List.Split(w, 2), (a, b) => {b})),
{"Month", "Sales"}
),
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Replace = f2(f1(Source)),
Transform = Table.TransformColumnTypes(Replace, {{"Sales", type number}}),
Group = Table.Group(
Transform,
{"Month"},
{{"Sales", each List.Sum([Sales]), type nullable number}}
)
in
GroupPower Query solution 12 for Sum Sales By Month, proposed by Alexandre Garcia:
let
a = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
b = List.Accumulate(List.Split(Table.ColumnNames(a),2), hashtag#table({}, {}),(s,c)=> s & Table.FromRows(Table.ToRows(Table.SelectColumns(a, c)),{"Month","x"})),
c = List.Select(b[Month], each Text.Length(_) = 3),
d = Table.Group(b, "Month", {"Sale", each List.Sum([x]) },0,(x,y)=> Byte.From (List.ContainsAny({y},c)))
in
d
Power Query solution 13 for Sum Sales By Month, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToCols = Table.Combine(
List.Transform(List.Split(Table.ToColumns(Source), 2), each Table.FromColumns(_))
),
Result = Table.Group(
ToCols,
{"Column2"},
{{"Month", each _[Column1]{0}}, {"Sales", each List.Sum(_[Column2])}},
GroupKind.Local,
(p, q) => Number.From(q[Column2] = null)
)[[Month], [Sales]]
in
ResultPower Query solution 14 for Sum Sales By Month, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToList = Table.ToColumns(Source),
Zip = List.Combine(List.Transform(List.Split(ToList, 2), each List.Zip({_{0}, _{1}}))),
ToTable = Table.FromRows(Zip, {"Months", "Sale"}),
FillDownMonth = Table.AddColumn(ToTable, "Month", each if [Sale] = null then [Months] else null),
FilledDown = Table.FillDown(FillDownMonth, {"Month"}),
Result = Table.Group(
FilledDown,
{"Month"},
{{"Sale", each List.Sum([Sale]), type nullable number}}
)
in
ResultPower Query solution 15 for Sum Sales By Month, proposed by Gertjan Davies:
let
Source = Problem,
T2C = Table.ToColumns(Source),
MonthParts = List.Combine(List.Alternate(T2C, 1, 1, 1)),
ValueParts = List.Combine(List.Alternate(T2C, 1, 1)),
FromRows = Table.FromRows(List.Zip({MonthParts, ValueParts})),
MonthColumn = Table.AddColumn(
FromRows,
"Month",
each if not Text.StartsWith([Column1], "Week") then [Column1] else null,
type text
),
Fill = Table.FillDown(MonthColumn, {"Month"}),
Group = Table.Group(Fill, {"Month"}, {{"Sale", each List.Sum([Column2]), type nullable number}})
in
GroupSolving the challenge of Sum Sales By Month with Excel
Excel solution 1 for Sum Sales By Month, proposed by Bo Rydobon 🇹🇭:
=LET(x,
SCAN(
,
TOCOL(
A2:H17,
,
1
),
LAMBDA(
a,
v,
IF(
v<"w",
v,
a
)
)
),
m,
FILTER(
x,
x>""
),
DROP(GROUPBY(HSTACK(--(1&m),
m),
FILTER(
x,
x<=""
),
SUM,
,
0),
,
1))Excel solution 2 for Sum Sales By Month, proposed by Rick Rothstein:
=HSTACK(TEXT(28*SEQUENCE(12),"mmm"),BYROW(WRAPROWS(TOCOL(CHOOSECOLS(B3:H17,1,3,5,7),,1),5),SUM))Excel solution 3 for Sum Sales By Month, proposed by Julian Poeltl:
=LET(
T,
A2:H17,
G,
GROUPBY(
SCAN(
,
TOCOL(
CHOOSECOLS(
T,
SEQUENCE(
4,
,
,
2
)
),
,
1
),
LAMBDA(
A,
B,
IF(
LEFT(
B
)<>"W",
B,
A
)
)
),
TOCOL(
CHOOSECOLS(
T,
SEQUENCE(
4,
,
2,
2
)
),
,
1
),
SUM,
,
0,
1
),
SORTBY(
G,
MONTH(
1&TAKE(
G,
,
1
)
)
)
)Excel solution 4 for Sum Sales By Month, proposed by Oscar Mendez Roca Farell:
=LET(
d,
A2:H17,
s,
SCAN(
,
TOCOL(
IFS(
d>"A",
d
),
2,
1
),
LAMBDA(
i,
x,
IF(
LEN(
x
)>3,
i,
x
)
)
),
DROP(
GROUPBY(
HSTACK(
MONTH(
s&1
),
s
),
TOCOL(
--d,
2,
1
),
SUM,
,
0
),
,
1
)
)Excel solution 5 for Sum Sales By Month, proposed by Duy Tùng:
=LET(v,
A2:H17,
a,
WRAPROWS(
TOCOL(
v
),
2
),
b,
UNIQUE(
SORT(
FILTER(
TAKE(
a,
,
1
),
TAKE(
a,
,
-1
)=0
)
)
),
HSTACK(b,
MAP(b,
LAMBDA(x,
SUM(OFFSET(A1,
SUM((v=x)*ROW(
v
)),
SUM((v=x)*COLUMN(
v
)),
5))))))Excel solution 6 for Sum Sales By Month, proposed by Sunny Baggu:
=LET(
_a,
TOCOL(
FILTER(
A2:H17,
B2:B17 = ""
),
3,
1
),
_b,
TOCOL(
FILTER(
SCAN(
"",
A2:H17,
LAMBDA(
a,
v,
IF(
v = "",
a,
v
)
)
),
A2:H2 = ""
),
,
1
),
_c,
SCAN(
"",
IF(
ISTEXT(
_b
),
_b,
""
),
LAMBDA(
x,
y,
IF(
y = "",
x,
y
)
)
),
HSTACK(_a,
MAP(_a,
LAMBDA(a,
& SUM(TOCOL((_c = a) * _b,
3)))))
)Excel solution 7 for Sum Sales By Month, proposed by LEONARD OCHEA 🇷🇴:
=LET(t,
A2:H17,
F,
LAMBDA(
[x],
TOCOL(
CHOOSECOLS(
t,
2*SEQUENCE(
COLUMNS(
t
)/2
)-1+x
),
,
1
)
),
i,
F(
1
),
m,
SCAN(
,
IF(
i,
i,
F()
),
LAMBDA(
a,
b,
IF(
ISNUMBER(
b
),
a,
b
)
)
),
DROP(GROUPBY(HSTACK(--(1&m),
m),
i,
SUM,
,
0),
,
1))Excel solution 8 for Sum Sales By Month, proposed by Md. Zohurul Islam:
=LET(
a,
VSTACK(
A2:B17,
C2:D17,
E2:F17,
G2:H17
),
b,
TAKE(
a,
,
1
),
c,
TAKE(
a,
,
-1
),
d,
SCAN(
c,
b,
LAMBDA(
x,
y,
IF(
y=0,
x,
y
)
)
),
e,
SCAN(
,
d,
LAMBDA(
x,
y,
IF(
LEFT(
y
)="W",
0,
y
)
)
),
f,
SCAN(
,
e,
LAMBDA(
x,
y,
IF(
y=0,
x,
y
)
)
),
g,
UNIQUE(
f
),
h,
SEQUENCE(
COUNTA(
g
)
),
j,
XLOOKUP(
f,
g,
h
),
k,
HSTACK(
j,
f
),
m,
GROUPBY(
k,
c,
SUM,
0,
0
),
rng,
DROP(
m,
,
1
),
header,
{"Month",
"Sale"},
Result,
VSTACK(
header,
rng
),
Result
)Excel solution 9 for Sum Sales By Month, proposed by Pieter de B.:
=LET(x,
LAMBDA(
y,
TOCOL(
IFS(
B2:H17,
y
),
2,
1
)
),
y,
x(
A2:G17
),
z,
GROUPBY(SCAN(0,
RIGHT(
y
),
LAMBDA(a,
b,
a+(b="1"))),
x(
B2:H17
),
SUM,
,
0),
HSTACK(
TEXT(
TAKE(
z,
,
1
)&-1,
"mmm"
),
DROP(
z,
,
1
)
))Excel solution 10 for Sum Sales By Month, proposed by Hamidi Hamid:
=LET(
n,
TOCOL(
TRANSPOSE(
CHOOSECOLS(
A2:H17,
SEQUENCE(
,
4,
2,
2
)
)*1
)
),
w,
TOCOL(
WRAPCOLS(
TOROW(
CHOOSECOLS(
CHOOSEROWS(
A2:H17,
SEQUENCE(
3,
,
1,
5
)
),
SEQUENCE(
,
4,
1,
2
)
)
),
4
)
),
t,
XLOOKUP(
CHOOSECOLS(
A2:H17,
SEQUENCE(
,
4,
1,
2
)
),
w,
w,
""
),
d,
TOCOL(
TRANSPOSE(
t
)
),
g,
SCAN(
,
d,
LAMBDA(
a,
b,
IF(
b<>"",
b,
a
)
)
),
cc,
MAP(
UNIQUE(
g
),
LAMBDA(
a,
SUM(
FILTER(
HSTACK(
g,
n
),
TAKE(
HSTACK(
g,
n
),
,
1
)=a,
)
)
)
),
HSTACK(
UNIQUE(
g
),
cc
)
)Excel solution 11 for Sum Sales By Month, proposed by ferhat CK:
=LET(
a,
VSTACK(
A2:B17,
C2:D17,
E2:F17,
G2:H17
),
b,
SCAN(
"",
TAKE(
a,
,
1
),
LAMBDA(
x,
y,
IF(
LEN(
y
)=3,
y,
x
)
)
),
c,
GROUPBY(
b,
TAKE(
a,
,
-1
),
SUM,
,
0
),
SORTBY(
c,
DATEVALUE(
TAKE(
c,
,
1
)&"1"
),
1
)
)Excel solution 12 for Sum Sales By Month, proposed by Jaroslaw Kujawa:
=LET(b ;
REDUCE(0 ;
LET(z ;
A2:H17 ;
v ;
TOCOL(
IF(
ISBLANK(
z
) ;
"" ;
z
) ;
;
1
) ;
FILTER(v ;
(v="")+ISNUMBER(
v
))) ;
LAMBDA(
a ;
x ;
IF(
""<>x ;
a+x ;
VSTACK(
a ;
0
)
)
)) ;
seq ;
SEQUENCE(
ROWS(
b
)
) ;
HSTACK(
TEXT(
"1/"&DROP(
seq ;
-1
) ;
"MMM"
) ;
DROP(
IFERROR(
INDEX(
b ;
seq
)-INDEX(
b ;
seq+1
) ;
INDEX(
b ;
seq
)
) ;
1
)
))Excel solution 13 for Sum Sales By Month, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
x,
TOCOL(
TRANSPOSE(
IF(
LEN(
A2:H17
)=3*NOT(
ISNUMBER(
A2:H17
)
),
A2:H17,
""
)
)
),
HSTACK(
FILTER(
x,
x<>""
),
MAP(
SEQUENCE(
COUNTA(
FILTER(
x,
x<>""
)
)
),
LAMBDA(
a,
SUM(
IF(
a=LET(
j,
IF(
LEN(
A2:A17
)=3,
1,
0
),
LET(
i,
IF(
j=0,
SCAN(
0,
j,
LAMBDA(
a,
b,
a+b
)
),
0
),
HSTACK(
i,
IF(
i=0,
0,
i+{3,
6,
9}
)
)
)
),
CHOOSECOLS(
IF(
ISNUMBER(
A2:H17
),
A2:H17,
0
),
{2,
4,
6,
8}
),
0
)
)
)
)
)
)Excel solution 14 for Sum Sales By Month, proposed by Imam Hambali:
=LET(
a,
LAMBDA(
x,
TOCOL(
CHOOSECOLS(
A2:H17,
SEQUENCE(
,
COLUMNS(
A2:H17
)/2,
x,
2
)
),
,
TRUE
)
),
b,
HSTACK(
SCAN(
"",
IF(
LEFT(
a(
1
)
)<>"W",
a(
1
)
),
LAMBDA(
x,
y,
IF(
y=FALSE,
x,
y
)
)
),
a(
2
)
),
c,
GROUPBY(
CHOOSECOLS(
b,
1
),
CHOOSECOLS(
b,
2
),
SUM,
0,
0
),
SORTBY(
c,
DATEVALUE(
CHOOSECOLS(
c,
1
)&"1"
)
)
)Excel solution 15 for Sum Sales By Month, proposed by Peter Bartholomew:
= LET(
week,
TOCOL(
FILTER(
source,
header="Month"
),
,
1
),
sale,
TOCOL(
FILTER(
source,
header="Sale"
),
,
TRUE
),
month,
SCAN(
0,
week,
LAMBDA(
acc,
w,
IF(
LEFT(
w,
4
)<>"week",
acc+1,
acc
)
)
),
GROUPBY(
month,
sale,
SUM,
,
0
)
)Excel solution 16 for Sum Sales By Month, proposed by Luis Enrique Charca Ponce:
=LET(
raw,
A2:H17,
periods,
SCAN(
"",
TOCOL(
CHOOSECOLS(
raw,
SEQUENCE(
4,
,
1,
2
)
),
,
TRUE
),
LAMBDA(
a,
v,
IF(
LEFT(
v,
4
)="Week",
a,
v
)
)
),
amounts,
TOCOL(
CHOOSECOLS(
raw,
SEQUENCE(
4,
,
2,
2
)
),
,
TRUE
),
total,
GROUPBY(
periods,
amounts,
SUM,
,
0
),
months,
UNIQUE(
periods
),
HSTACK(
months,
VLOOKUP(
months,
total,
2,
FALSE
)
)
)Solving the challenge of Sum Sales By Month with Python
Python solution 1 for Sum Sales By Month, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "PQ_Challenge_230.xlsx"
input = pd.read_excel(path, usecols="A:H", nrows=17).rename(columns=lambda x: x.split('.')[0])
test = pd.read_excel(path, usecols="J:K", nrows=12).rename(columns=lambda x: x.split('.')[0])
splits = [input.iloc[:, i:i+2] for i in range(0, input.shape[1], 2)]
df = pd.concat(splits, axis=0, ignore_index=True)
df['Week'] = df['Month']
df['Month'] = df.apply(lambda row: row['Week'] if pd.isna(row['Sale']) else np.NaN, axis=1)
df['Month'] = df['Month'].ffill()
summary = df.groupby('Month', as_index=False)['Sale'].sum()
check = test.merge(summary, on='Month', how='left')
check['Check'] = check['Sale_x'] == check['Sale_y']
print(all(check['Check'])) # True
Solving the challenge of Sum Sales By Month with Python in Excel
Python in Excel solution 1 for Sum Sales By Month, proposed by Alejandro Campos:
months = ['Jan'] * 4 + ['Feb'] * 4 + ['Mar'] * 5 +
['Apr'] * 4 + ['May'] * 4 + ['Jun'] * 5 +
['Jul'] * 4 + ['Aug'] * 4 + ['Sep'] * 5 +
['Oct'] * 4 + ['Nov'] * 4 + ['Dec'] * 5
weeks = ['Week1', 'Week2', 'Week3', 'Week4'] * 3 + ['Week5'] +
['Week1', 'Week2', 'Week3', 'Week4'] * 3 + ['Week5'] +
['Week1', 'Week2', 'Week3', 'Week4'] * 3 + ['Week5'] +
['Week1', 'Week2', 'Week3', 'Week4'] * 3 + ['Week5']
sales = [
]
df_table = pd.DataFrame({
'Months': months,
'Weeks': weeks,
'Sales': sales
})
df_table["Months"] = pd.Categorical(df_table["Months"], categories=[
'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'
], ordered=True)
monthly_sales_summary = df_table.groupby("Months")["Sales"].sum().reset_index()
monthly_sales_summary
Solving the challenge of Sum Sales By Month with R
R solution 1 for Sum Sales By Month, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_230.xlsx"
input = read_excel(path, range = "A1:H17")
test = read_excel(path, range = "J1:K13")
df = input %>%
split.default(., ceiling(seq_along(.) / 2)) %>%
map_dfr(~ .x, .id = NULL) %>%
mutate(Week = Month) %>%
mutate(Month = ifelse(is.na(Sale), Week, NA_character_)) %>%
fill(Month) %>%
summarise(Sale = sum(Sale, na.rm = TRUE), .by = Month)
all.equal(df, test, check.attributes = FALSE)
#> [1] TRUE
&&