Group the monthly sales values into three categories based on the following logic and provide one of the result tables: Group 1: 1st to 10th of the month Group 2: 11th to 20th of the month Group 3: 21st to the end of the month. Note: In the future, new data for more dates will be added. Hence, the solution should be dynamic/ scalable. When the ranges in the formula are adjusted to factor in more data, Your solution should return the correct result
📌 Challenge Details and Links
Challenge Number: 133
Challenge Difficulty: ⭐⭐
Designed by: Ankur Sharma
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Custom Grouping! Part 7 with Power Query
Power Query solution 1 for Custom Grouping! Part 7, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
G = each
let
d = Date.Day(_)
in
DateTime.ToText(_, "yyyyMM") & {"1", "2", "3"}{List.PositionOf({d < 11, d < 21, true}, true)},
P = Table.Group(
Source,
"Date",
{"T", each List.Sum([Sales])},
0,
(b, n) => Byte.From(G(b) <> G(n))
)[T],
S = Table.FromColumns({{1 .. List.Count(P)}, P}, {"Group", "Total Sales"})
in
SPower Query solution 2 for Custom Grouping! Part 7, proposed by Brian Julius:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Date", Date.Type}
),
AddMon = Table.AddColumn(Source, "Mon", each Date.Month([Date])),
AddGroup = Table.AddColumn(
AddMon,
"Group",
each [a = Date.Day([Date]), b = if a <= 10 then 1 else if a <= 20 then 2 else 3][b]
),
Group = Table.SelectColumns(
Table.Group(AddGroup, {"Mon", "Group"}, {{"Total Sales", each List.Sum([Sales])}}),
"Total Sales"
),
Clean = Table.ReorderColumns(Table.AddIndexColumn(Group, "Group", 1, 1), {"Group", "Total Sales"})
in
CleanPower Query solution 3 for Custom Grouping! Part 7, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData"]}[Content],
AddGrpValue = Table.AddColumn(
Source,
"GroupValue",
each DateTime.ToText([Date], "yyyyMM")
& Text.From(Number.IntegerDivide(Date.Day([Date]), 10) + 1)
),
Group = Table.Group(AddGrpValue, "GroupValue", {"G", each List.Sum([Sales])}, GroupKind.Local),
Result = Table.FromColumns({{1 .. Table.RowCount(Group)}, Group[G]}, {"Group", "Total Sales"})
in
ResultPower Query solution 4 for Custom Grouping! Part 7, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Column = Table.AddColumn(
Source,
"Group",
each [
Dt = Date.From([Date]),
Dy = Date.Day(Dt),
I = Number.IntegerDivide(Dy, 10.45),
S = Date.StartOfMonth(Dt) + #duration(I * 10, 0, 0, 0),
E = if I = 2 then Date.EndOfMonth(Dt) else S + #duration(9, 0, 0, 0),
R = Date.ToText(S, "d", "en-us") & " - " & Date.ToText(E, "d", "en-us")
][R]
),
Return = Table.Group(Column, "Group", {"Sales", each List.Sum([Sales])})
in
ReturnPower Query solution 5 for Custom Grouping! Part 7, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group1 = Table.Combine(Table.Group(Source, "Date", {{"A", each
let
a = _,
b = Table.AddColumn(a, "Idx", each
if Date.Day([Date])<=10 then 1 else
if Date.Day([Date])<=20 then 2 else 3)
in b
}},0)[A]),
Sol = Table.Group(Group1, {"Idx"},
{{"Group", each Text.From(Date.From([Date]{0}))&"-"&Text.From(Date.From(
let
a = List.Last([Date]),
b = if Date.Day(a)<=10 then
hashtag
#date(Date.Year(a),Date.Month(a),10) else
if Date.Day(a)<=20 then
hashtag
#date(Date.Year(a),Date.Month(a),20) else
Date.EndOfMonth(a)
in b))},
{"Total Sales", each List.Sum([Sales])}},0)[[Group], [Total Sales]]
in
Sol Power Query solution 6 for Custom Grouping! Part 7, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.TransformColumns(
A,
{"Date", each Number.RoundUp(Date.Day(_) / 10, 0) * 100 + Date.Month(_)}
),
C = Table.Group(B, {"Date"}, {"Total Sales", each List.Sum([Sales])}),
D = Table.AddIndexColumn(C, "Group", 1)[[Group], [Total Sales]]
in
DPower Query solution 7 for Custom Grouping! Part 7, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.TransformColumns(
A,
{
"Date",
each [
a = Date.Day(_),
b = List.Select({21, 11, 1}, each a >= _){0},
c = Date.EndOfMonth(_),
d = Date.Year(_),
e = Date.Month(_),
f = (x) => Date.ToText(x, [Format = "d/M/yyyy"]),
g = f(#date(d, e, b))
& " -"
& f(#date(d, e, if b = 1 then 10 else if b = 11 then 20 else Date.Day(c)))
][g]
}
),
C = Table.Group(B, {"Date"}, {"Total Sales", each List.Sum([Sales])})
in
CPower Query solution 8 for Custom Grouping! Part 7, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.TransformColumnTypes(S, {{"Date", type date}}),
B = Table.AddIndexColumn(A, "I", 1, 1),
C = Table.AddColumn(
B,
"Result",
each
if Date.Day([Date]) < 11 then
"1/" & Date.ToText([Date], "M/yyyy-") & "10/" & Date.ToText([Date], "M/yyyy")
else if Date.Day([Date]) < 21 then
"11/" & Date.ToText([Date], "M/yyyy-") & "20/" & Date.ToText([Date], "M/yyyy")
else
"21/" & Date.ToText([Date], "M/yyyy-") & Date.ToText(Date.EndOfMonth([Date]), "d/M/yyyy")
),
D = Table.Group(C, {"Result"}, {{"Total Sales", each List.Sum([Sales]), type number}}),
E = Table.AddIndexColumn(D, "Result2", 1, 1, Int64.Type),
F = Table.ReorderColumns(E, {"Result", "Result2", "Total Sales"})
in
FPower Query solution 9 for Custom Grouping! Part 7, proposed by Ahmed Ariem:
let
f = (x)=> [
a = Table.AddColumn(x, "tmp", each Number.RoundUp(Date.Day([Date])/10,0)+Date.Month([Date])*10+Date.Year([Date])*1000),
b = Table.Group(a, "tmp", {"Value", each List.Sum([Sales])})][b],
source= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Types = f( Table.TransformColumnTypes(source,{{"Date", type date}, {"Sales", Int64.Type}})),
to =Table.AddIndexColumn(Types, "Group", 1, 1, Int64.Type) [[Group],[Value]]
in
to
Solving the challenge of Custom Grouping! Part 7 with Excel
Excel solution 1 for Custom Grouping! Part 7, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
B3:B40,
GROUPBY(
MATCH(
DAY(
d
),
{1,
11,
21}
)+MONTH(
d
)*3-3,
C3:C40,
SUM,
,
0
)
)
=LET(
d,
B3:B40,
e,
{1,
11,
21},
n,
MATCH(
DAY(
d
),
e
),
m,
TEXT(
d,
"/m/e"
),
DROP(
GROUPBY(
HSTACK(
n+MONTH(
d
)*3,
INDEX(
e,
n
)&m&-CHOOSE(
n,
10,
20,
DAY(
EOMONTH(
+d,
0
)
)
)&m
),
C3:C40,
SUM,
,
0
),
,
1
)
)Excel solution 2 for Custom Grouping! Part 7, proposed by 🇰🇷 Taeyong Shin:
=GROUPBY(CEILING(
MAP(
DAY(
B3:B40
),
LAMBDA(
x,
MIN(
x,
30
)
)
)/10,
1
)+(3*(MONTH(
B3:B40
)-1)),
C3:C40,
SUM,
,
0)
Result2
=LET(
d,
B3:B40,
c,
MAP(
CEILING(
MAP(
DAY(
d
),
LAMBDA(
x,
MIN(
x,
30
)
)
)/10,
1
),
d,
LAMBDA(
x,
y,
TEXTJOIN(
"-",
,
SWITCH(
x,
1,
{1,
10},
2,
{11,
20},
HSTACK(
21,
DAY(
EOMONTH(
y,
0
)
)
)
)&TEXT(
y,
"/m/e"
)
)
)
),
DROP(
GROUPBY(
HSTACK(
XMATCH(
c,
c
),
c
),
C3:C40,
SUM,
,
0
),
,
1
)
)Excel solution 3 for Custom Grouping! Part 7, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_dt,
B3:B40, _sls,
C3:C40, _day,
DAY(
_dt
), _quo,
QUOTIENT(
_day,
10.45
), _strt,
EOMONTH(
+_dt,
-1
) + _quo * 10 + 1, _end,
IF(_quo = 2,
EOMONTH(
+_dt,
0
),
EOMONTH(
+_dt,
-1
) + (_quo + 1) * 10), _rowhdr,
BYROW( HSTACK(
_strt,
_end
), LAMBDA(
a,
TEXTJOIN(
"-",
1,
TEXT(
a,
"dd/mm/yyyy"
)
)
) ), _grp,
GROUPBY(
_rowhdr,
_sls,
SUM,
0,
0
), _rtrn,
SORTBY(
_grp,
--TEXTBEFORE(
TAKE(
_grp,
,
1
),
"-"
)
), _rtrn
)Excel solution 4 for Custom Grouping! Part 7, proposed by Oscar Mendez Roca Farell:
=LET(b,
B3:B42,
e,
EOMONTH(
+b,
-1
)+1+10*INT((DAY(
b
)-1)/10),
t,
TEXT(
b,
"/m/e"
),
d,
DAY(
e
)&t&-DAY(MAP(e,
LAMBDA(a,
LET(f,
EOMONTH(
a,
0
),
m,
MIN(
f,
a+10
),
m-(mExcel solution 5 for Custom Grouping! Part 7, proposed by Julian Poeltl:
=LET(D,
B3:B40,
Q,
C3:C40,
M,
MONTH(
D
),
Y,
YEAR(
D
),
U,
UNIQUE(
M&","&Y
),
UY,
TEXTAFTER(
U,
","
),
UM,
TEXTBEFORE(
U,
","
),
H,
DATE(
UY,
UM,
HSTACK(
10,
20
)
),
E,
TOCOL(
HSTACK(
H,
EOMONTH(
TAKE(
H,
,
1
),
0
)
)
),
REDUCE(HSTACK(
"Group",
"Total Sales"
),
E,
LAMBDA(A,
B,
VSTACK(A,
LET(S,
IF(
DAY(
B
)>20,
DATE(
YEAR(
B
),
MONTH(
B
),
21
),
B-9
),
HSTACK(TEXT(
S,
"D/M/YYYY"
)&"-"&TEXT(
B,
"D/M/YYYY"
),
SUM(FILTER(Q,
(D>=S)*(D<=B)))))))))Excel solution 6 for Custom Grouping! Part 7, proposed by Kris Jaganah:
=LET(
a,
B3:B40,
b,
C3:C40,
c,
CEILING(
DAY(
a
)/10,
1
),
d,
LAMBDA(
x,
y,
z,
TEXT(
DATE(
YEAR(
a
),
MONTH(
a
),
SWITCH(
c,
1,
x,
2,
y,
z
)
),
"dd/m/yyy"
)
),
e,
d(
1,
11,
21
)&"-"&d(
10,
20,
DAY(
EOMONTH(
--a,
0
)
)
),
f,
GROUPBY(
e,
b,
SUM,
,
0
),
SORTBY(
f,
--TEXTSPLIT(
TAKE(
f,
,
1
),
"-"
)
)
)Excel solution 7 for Custom Grouping! Part 7, proposed by Kris Jaganah:
=LET(
a,
B3:B40,
b,
C3:C40,
c,
XMATCH(
DAY(
a
),
{1;11;21},
-1
)&MONTH(
a
),
d,
GROUPBY(
XMATCH(
c,
UNIQUE(
c
)
),
b,
SUM,
,
0
),
d
)Excel solution 8 for Custom Grouping! Part 7, proposed by Imam Hambali:
=LET(
gp,
{1,
1,
10;2,
11,
20;3,
21,
99},
dt,
B3:B40,
ydt,
YEAR(
dt
),
mdt,
MONTH(
dt
),
ddt,
DAY(
dt
),
sls,
C3:C40,mindt,
0+(TEXT(
MIN(
dt
),
"yyyy-mm"
)&"-01"),
fdt,
DATE(
YEAR(
dt
),
MONTH(
dt
),
1
),
ddiff,
DATEDIF(
mindt,
fdt,
"M"
),group,
ROUNDUP(
ddt/10,
0
),
groupc,
group+(3*ddiff),
result1,
GROUPBY(
groupc,
sls,
SUM,
0,
0
),l,
LAMBDA(
x,
XLOOKUP(
group,
TAKE(
gp,
,
1
),
CHOOSECOLS(
gp,
x
)
)
),
datemin,
DATE(
ydt,
mdt,
l(
2
)
),datemax,
IF(
l(
3
)=99,
BYROW(
dt,
LAMBDA(
x,
EOMONTH(
x,
0
)
)
),
DATE(
ydt,
mdt,
l(
3
)
)
),aggr2,
DROP(
GROUPBY(
groupc,
HSTACK(
datemin,
datemax,
sls
),
HSTACK(
MIN,
MIN,
SUM
),
0,
0
),
1,
1
),result2,
HSTACK(
TEXT(
CHOOSECOLS(
aggr2,
1
),
"dd/mm/yyy"
)&"-"&TEXT(
CHOOSECOLS(
aggr2,
2
),
"dd/mm/yyyy"
),
CHOOSECOLS(
aggr2,
3
)
),result2
)Excel solution 9 for Custom Grouping! Part 7, proposed by Sunny Baggu:
=LET(
rng,
TAKE(
B3:C49,
SUM(
N(
C3:C49 <> ""
)
)
), _m,
MONTH(
TAKE(
rng,
,
1
)
), _d,
ROUNDUP(
DAY(
TAKE(
rng,
,
1
)
) / 10,
0
), _um,
UNIQUE(
_m
), _ud,
UNIQUE(
_d
), _g,
SEQUENCE(
ROWS(
_um
) * ROWS(
_ud
)
), _s,
TOCOL(
DROP(
REDUCE(
"", SEQUENCE(
ROWS(
_um
)
), LAMBDA(x,
y, VSTACK(x,
BYCOL(TAKE(
rng,
,
-1
) * (_m = y) * ((_d = TOROW(
_ud
))),
LAMBDA(
a,
SUM(
a
)
)))
)
), 1
)
), HSTACK(
_g,
_s
)
)Excel solution 10 for Custom Grouping! Part 7, proposed by Ankur Sharma:
=LET(
a,
TblSales[Date], Dt_1,
IF(
ROUNDUP(
DAY(
a
)/10,
0
) > 3,
3,
ROUNDUP(
DAY(
a
)/10,
0
)
), Dt_2,
DATE(
YEAR(
a
),
MONTH(
a
),
Dt_1
), Gr_1,
SEQUENCE(
COUNT(
UNIQUE(
Dt_2
)
)
), Ans_1,
GROUPBY(
Dt_2,
C3:C40,
SUM,
,
0
), HSTACK(
Gr_1,
TAKE(
Ans_1,
,
-1
)
)
)Excel solution 11 for Custom Grouping! Part 7, proposed by Asheesh Pahwa:
=LET(
dt,
B3:B48,
sl,
C3:C48,
m,
MONTH(
dt
),
u,
UNIQUE(
m
),
d,
ROUNDUP(
DAY(
dt
)/10,
0
),
_u,
UNIQUE(
d
),
r,
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B3:C48,
m=y
),
xl,
XLOOKUP(
TAKE(
f,
,
1
),
dt,
d
),
DROP(
REDUCE(
"",
UNIQUE(
xl
),
LAMBDA(
a,
v,
VSTACK(
a,
SUM(
FILTER(
TAKE(
f,
,
-1
),
xl=v
)
)
)
)
),
1
)
)
)
)
),
1
), HSTACK(
SEQUENCE(
ROWS(
r
)
),
r
)
)Excel solution 12 for Custom Grouping! Part 7, proposed by Craig Hatmaker:
=GROUPBY(
( MONTH( tblData[Date]) - 1) * ROWS( tblGroups) +
XMATCH(DAY(tblData[Date]), tblGroups[Day], -1),
tblData[Sales], SUM, , 0 )Excel solution 13 for Custom Grouping! Part 7, proposed by Eddy Wijaya:
=LET( s,
SEQUENCE(
365,
,
DATE(
2024,
1,
1
)
), sc,
SCAN(
0,
s,
LAMBDA(
a,
v,
IF(
DAY(
v
)>=31,
a,
ROUNDUP(
DAY(
v
)/10,
0
)
)
)
), dm,
GROUPBY(
MONTH(
s
),
sc,
MAX
), dummyMonth,
SCAN(
-3,
TAKE(
dm,
,
-1
),
LAMBDA(
a,
v,
a+v
)
), all,
BYROW(
HSTACK(
sc,
XLOOKUP(
MONTH(
s
),
TAKE(
dm,
,
1
),
dummyMonth
)
),
SUM
), t,
--B3:C40, r_2,
UNIQUE(
BYROW(
TEXT(
XLOOKUP(
all,
all,
s,
,
,
{1,
-1}
),
"d/m/yyyy"
),
LAMBDA(
r,
TEXTJOIN(
" -",
,
r
)
)
)
), res,
GROUPBY(
XLOOKUP(
TAKE(
t,
,
1
),
s,
all
),
TAKE(
t,
,
-1
),
SUM,
,
0
), res_b,
HSTACK(
INDEX(
r_2,
TAKE(
res,
,
1
)
),
TAKE(
res,
,
-1
)
),
//Result2
res
)Excel solution 14 for Custom Grouping! Part 7, proposed by ferhat CK:
=LET(ay,
MONTH(
B3:B40
),
gn,
DAY(
B3:B40
),
n,
(YEAR(
B3:B40
)-2024)*36,
cc,
XLOOKUP(
gn,
{10,
20,
31},
{1,
2,
3},
,
1
),
b,
IF(
ay=1,
cc+n,
cc+ay+1
)+n,GROUPBY(
b,
C3:C40,
SUM,
,
0
))Excel solution 15 for Custom Grouping! Part 7, proposed by Hussein SATOUR:
=LET(
D,
DAY,
C,
CEILING,
M,
MONTH,
E,
YEAR,
H,
HSTACK,
L,
B3:B40,
R,
GROUPBY(
M(
L
)&C(
D(
L
),
10
),
H(
L,
C3:C40
),
H(
LAMBDA(
x,
LET(
y,
MIN(
x
),
C(
D(
y
),
10
)-9&"/"&M(
y
)&"/"&E(
y
)&"-"&IF(
D(
y
)=21,
TEXT(
EOMONTH(
y,
0
),
"d/m/e"
),
C(
D(
y
),
10
)&"/"&M(
y
)&"/"&E(
y
)
)
)
),
SUM
),
,
0
),
H(
SEQUENCE(
ROWS(
R
)-1
),
DROP(
R,
1,
1
)
)
)Excel solution 16 for Custom Grouping! Part 7, proposed by Pierluigi Stallone:
=LET( sales,
OFFSET(
$C$3,
0,
0,
COUNTA(
$C:$C
)-1
), dates,
OFFSET(
$B$3,
0,
0,
COUNTA(
$B:$B
)-1
), period,
IF(
DAY(
dates
)<=10,
"1",
IF(
DAY(
dates
)<20,
"2",
"3"
)
)&MONTH(
dates
)&YEAR(
dates
), uniquePeriods,
UNIQUE(
period
), summedSales,
BYROW(
uniquePeriods,
LAMBDA(
r,
SUM(
FILTER(
sales,
period=r
)
)
)
), HSTACK(
SEQUENCE(
COUNTA(
uniquePeriods
)
),
summedSales
))