From dataset in column A, create the table under Answer Expected. 1H = Jan to Jun and 2H = Jul to Dec. Sort it on the basis of Year followed by Half. If you are on Insider Beta version of Microsoft 365, then I will encourage you to use GROUPBY / PIVOTBY function.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 340
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Create half-yearly sorted table from data with Power Query
Power Query solution 1 for Create half-yearly sorted table from data, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Date],
M = List.Min,
X = List.Max,
Y = Date.Year,
L = {"H1", "H2"},
H = each L{Number.From(Date.Month(_) > 6)},
S = Table.FromRows(
List.TransformMany(
{Y(M(Source)) .. Y(X(Source))},
(i) => L,
(i, o) =>
let
s = List.Select(Source, each (Y(_) = i) and H(_) = o)
in
{i, o, M(s), X(s)}
),
{"Year", "Half", "Min Date", "Max Date"}
)
in
SPower Query solution 2 for Create half-yearly sorted table from data, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Sort(
Table.Group(
Table.AddColumn(
Table.AddColumn(Source, "Year", each Date.Year([Date])),
"Half",
each {"H1", "H2"}{Number.From(Date.Month([Date]) > 6)}
),
{"Year", "Half"},
{{"Min Date", each List.Min([Date])}, {"Max Date", each List.Max([Date])}}
),
{"Year", "Half"}
)
in
SPower Query solution 3 for Create half-yearly sorted table from data, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToDate = Table.TransformColumnTypes(Source, {{"Date", type date}}),
Yr = Table.AddColumn(ToDate, "Year", each Date.Year([Date])),
Half = Table.AddColumn(Yr, "Half", each Text.From(Number.RoundUp(Date.Month([Date]) / 6)) & "H"),
Group = Table.Group(
Half,
{"Year", "Half"},
{
{"Min Date", each List.Min([Date]), type nullable date},
{"Max Date", each List.Max([Date]), type nullable date}
}
),
Sort = Table.Sort(Group, {{"Year", Order.Ascending}, {"Half", Order.Ascending}})
in
SortPower Query solution 4 for Create half-yearly sorted table from data, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Year = Table.AddColumn(Source, "Year", each Date.Year([Date])),
Half = Table.AddColumn(Year, "Half", each if Date.Month([Date]) <= 6 then "1H" else "2H"),
Sol = Table.Sort(
Table.Group(
Half,
{"Year", "Half"},
{{"Min Date", each List.Min([Date])}, {"Max Date", each List.Max([Date])}}
),
{"Year", "Half"}
)
in
SolPower Query solution 5 for Create half-yearly sorted table from data, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Year = Table.AddColumn(Source, "Year", each Date.Year([Date])),
Data = Table.Group(
Year,
{"Year"},
{
{
"All",
each
let
a = List.Sort([Date]),
b = List.Transform(a, each if Date.Month(_) <= 6 then "1H" else "2H"),
c = List.Zip({b, a}),
d = List.Transform(List.Select(c, (x) => x{0} = "1H"), each _{1}),
e = {"1H"} & {List.Min(d)} & {List.Max(d)},
f = List.Transform(List.Select(c, (x) => x{0} = "2H"), each _{1}),
g = {"2H"} & {List.Min(f)} & {List.Max(f)}
in
Table.FromRows({e, g}, {"Half", "Min Date", "Max Date"})
}
}
),
Sol = Table.Sort(Table.ExpandTableColumn(Data, "All", Table.ColumnNames(Data[All]{0})), "Year")
in
SolPower Query solution 6 for Create half-yearly sorted table from data, proposed by Luan Rodrigues:
let
Fonte = Tabela1[Date],
tab = Table.FromRows(
List.Transform(Fonte, (x) => {Date.Year(x)} & {if Date.Month(x) <= 6 then "1H" else "2H"} & {x}),
{"Year", "Half", "Date"}
),
gp = Table.Group(
tab,
{"Year", "Half"},
{{"Min Date", each List.Min([Date])}, {"Max Date", each List.Max([Date])}}
),
res = Table.Sort(gp, {{"Year", 0}, {"Half", 0}})
in
resPower Query solution 7 for Create half-yearly sorted table from data, proposed by Brian Julius:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Date", Date.Type}
),
AddHalf = Table.AddColumn(
Table.AddColumn(Source, "Half", each if Date.Month([Date]) < 7 then "!H" else "2H"),
"Year",
each Date.Year([Date]),
Int64.Type
),
GroupYearHalf = Table.Group(
AddHalf,
{"Year", "Half"},
{
{"Min", each List.Min([Date]), type nullable date},
{"Max", each List.Max([Date]), type nullable date}
}
),
Sort = Table.Sort(GroupYearHalf, {{"Year", Order.Ascending}, {"Half", Order.Ascending}})
in
SortPower Query solution 8 for Create half-yearly sorted table from data, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.AddColumn(
Table.AddColumn(Origen, "Year", each Date.Year([Date])),
"Half",
each Date.QuarterOfYear([Date])
),
b = Table.TransformColumns(a, {"Half", each if _ = 1 or _ = 2 then 1 else 2}),
c = Table.TransformColumns(
Table.Sort(b, {{"Year", 0}, {"Half", 0}}),
{"Half", each Text.From(_) & "H"}
),
d = Table.Group(c, {"Year", "Half"}, {{"D", each _}}),
e = Table.AddColumn(d, "Min Date", each Table.Min([D], "Date")[Date]),
Sol = Table.RemoveColumns(Table.AddColumn(e, "Max Date", each Table.Max([D], "Date")[Date]), "D")
in
SolSolving the challenge of Create half-yearly sorted table from data with Excel
Excel solution 1 for Create half-yearly sorted table from data, proposed by Bo Rydobon 🇹🇭:
=LET(d,
A2:A20,
y,
SORT(
--UNIQUE(
YEAR(
d
)&-CEILING(
MONTH(
d
),
6
)&-1
)
),
VSTACK({"Year",
"Half",
"Min Date",
"Max Date"},
HSTACK(YEAR(
y
),
(MONTH(
y
)>6)+1&"H",
XLOOKUP(
EDATE(
y,
{-5,
1}
)-{0,
1},
d,
d,
,
{1,
-1}
))))
=LET(d,
A2:A20,
VSTACK({"Year",
"Half",
"Min Date",
"Max Date"},
DROP(GROUPBY(HSTACK(YEAR(
d
),
(MONTH(
d
)>6)+1&"H"),
d,
HSTACK(
MIN,
MAX
),
0,
0),
1)))Excel solution 2 for Create half-yearly sorted table from data, proposed by John V.:
=LET(d,
A2:A20,
VSTACK(
{"Year",
"Half",
"Min Date",
"Max Date"}
),
DROP(GROUPBY(HSTACK(YEAR(
d
),
1+(MONTH(
d
)>6)&"H"),
d,
HSTACK(
MIN,
MAX
),
,
0),
1)))Excel solution 3 for Create half-yearly sorted table from data, proposed by محمد حلمي:
=LET(
c,
SORT(
A2:A20
),
v,
YEAR(
c
),
REDUCE(C2:F2,
UNIQUE(
v
),
LAMBDA(a,
y,
LET(i,
DATE(
y,
7,
),
r,
v=y,
VSTACK(a,
IFNA(
HSTACK(y,
{1;2}&"H",
VSTACK(
TAKE(FILTER(c,
r*(c<=i)),
{1,
-1}),
TAKE(FILTER(c,
r*(c>i)),
{1,
-1}))),
y))))))Excel solution 4 for Create half-yearly sorted table from data, proposed by 🇰🇷 Taeyong Shin:
=LET(
dt,
A2:A20,
func,
LAMBDA(
fn,
LAMBDA(
x,
TEXT(
fn(
x
),
"dd-mmm-e"
)
)
),
GROUPBY(HSTACK(YEAR(
dt
),
(MONTH(
dt
) > 6) + 1 & "H"),
dt,
HSTACK(
func(
MIN
),
func(
MAX
)
),
,
0)
)Excel solution 5 for Create half-yearly sorted table from data, proposed by Kris Jaganah:
=LET(
a,
A2:A20,
VSTACK(
{"Year",
"Half",
"Min Date",
"Max Date"},
DROP(
GROUPBY(
HSTACK(
YEAR(
a
),
IF(
MONTH(
a
)<7,
1,
2
)&"H"
),
a,
HSTACK(
MIN,
MAX
),
0,
0
),
1
)
)
)Excel solution 6 for Create half-yearly sorted table from data, proposed by Julian Poeltl:
=LET(D,A2:A20,Y,SORT(YEAR(D)),M,MONTH(D),UY,UNIQUE(Y),YH,CHOOSEROWS(UY,ROUNDUP(SEQUENCE(6)/2,0)),Half,HSTACK({1,2}&"H"),Headers,HSTACK(YH,VSTACK(Half,Half,Half)),MIN,BYROW(Headers,LAMBDA(A,LET(Y,CHOOSECOLS(A,1),H,CHOOSECOLS(A,2),MM,IFS(H="1H",1,H="2H",7),MG,IFS(H="1H",6,H="2H",12),MINIFS(D,D,">="&DATE(Y,MM,1),D,"<="&DATE(Y,MG,1))))),MAX,BYROW(Headers,LAMBDA(A,LET(Y,CHOOSECOLS(A,1),H,CHOOSECOLS(A,2),MM,IFS(H="1H",1,H="2H",7),MG,IFS(H="1H",6,H="2H",12),MAXIFS(D,D,">="&DATE(Y,MM,1),D,"<="&DATE(Y,MG,31))))),VSTACK(HSTACK("Year","Half","Min Date","Max Date"),HSTACK(Headers,MIN,MAX)))Excel solution 7 for Create half-yearly sorted table from data, proposed by Timothée BLIOT:
=LET(A,
A2:A20,
VSTACK({"Year",
"Half",
"Min Date",
"Max Date"},
DROP(GROUPBY(HSTACK(YEAR(
A
),
"H"&--(MONTH(
A
)/6>1)+1),
A,
HSTACK(
MIN,
MAX
),
0,
0,
),
1)))
Reduce/filter solution:
=LET(A,
A2:A20,
B,
YEAR(
A
),
C,
"H"&--(MONTH(
A
)/6>1)+1,
D,
SORT(
UNIQUE(
HSTACK(
B,
C
)
),
{1,
2}
),
E,
REDUCE({"Year",
"Half",
"Min Date",
"Max Date"},
SEQUENCE(
ROWS(
D
)
),
LAMBDA(w,
v,
LET(S,
FILTER(A,
(B=INDEX(
D,
v,
1
))*(C=INDEX(
D,
v,
2
))),
VSTACK(
w,
HSTACK(
INDEX(
D,
v
),
MIN(
S
),
MAX(
S
)
)
)) )),
E)Excel solution 8 for Create half-yearly sorted table from data, proposed by Hussein SATOUR:
=TEXTSPLIT(CONCAT(LET(d,
A2:A20,
y,
YEAR(
d
),
MAP(TOCOL(
SORT(
UNIQUE(
y
)
) & {"1H",
"2H"}
),
LAMBDA(z,
LET(e,
LEFT(
z,
4
),
f,
RIGHT(
z,
2
),
x,
FILTER(d,
(y=--e) * (IF(
f="1H",
MONTH(
d
)<7,
MONTH(
d
)>6
))),
TEXTJOIN(
",",
,
e,
f,
TEXT(
MIN(
x
),
"dd-mmm-e"
),
TEXT(
MAX(
x
),
"dd-mmm-e"
)&"/"
)))))),
",",
"/",
1)Excel solution 9 for Create half-yearly sorted table from data, proposed by Duy Tùng:
=LET(a,A2:A20,DROP(GROUPBY(HSTACK(YEAR(a),(MONTH(a)>6)+1&"H"),a,HSTACK(MIN,MAX),0,0),1))Excel solution 10 for Create half-yearly sorted table from data, proposed by Sunny Baggu:
=LET(
_d,
A2:A20,
_ud,
SORT(
UNIQUE(
TEXT(
_d,
"yyy"
)
)
),
_y,
TOCOL(
IF(
SEQUENCE(
,
2
),
_ud
)
),
_h,
TOCOL(
IF(
SEQUENCE(
,
ROWS(
_ud
)
),
{"1H"; "2H"}
),
,
1
),
HSTACK(
_y,
_h,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
_ud
)
),
LAMBDA(x,
y,
VSTACK(
x,
LET(
_min,
FILTER(_d,
(TEXT(
_d,
"yyy"
) = INDEX(
_ud,
y,
)) * (--TEXT(
_d,
"m"
) <= 6)),
_max,
FILTER(_d,
(TEXT(
_d,
"yyy"
) = INDEX(
_ud,
y,
)) * (--TEXT(
_d,
"m"
) > 6)),
VSTACK(
TAKE(
SORT(
_min
),
{1,
-1}
),
TAKE(
SORT(
_max
),
{1,
-1}
)
)
)
)
)
),
1
)
)
)Excel solution 11 for Create half-yearly sorted table from data, proposed by Abdallah Ally:
=LET(a,
A2:A20,
b,
YEAR(
a
),
c,
IF(
MONTH(
a
)<7,
"1H",
"2H"
),
d,
MAP(b,
c,
LAMBDA(x,
y,
MIN(FILTER(a,
(b=x)*(c=y))))),
e,
MAP(b,
c,
LAMBDA(x,
y,
MAX(FILTER(a,
(b=x)*(c=y))))),
SORT(
UNIQUE(
HSTACK(
b,
c,
d,
e
)
),
{1,
2}
))Excel solution 12 for Create half-yearly sorted table from data, proposed by Abdallah Ally:
=LET(
a,
A2:A20,
b,
YEAR(
a
)&"_"&IF(
MONTH(
a
)<7,
"1H",
"2H"
),
REDUCE(
{"Year",
"Half",
"Min Date",
"Max Date"},
SORT(
UNIQUE(
b
)
),
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
TEXTSPLIT(
y,
"_"
),
MIN(
FILTER(
a,
& b=y
)
),
MAX(
FILTER(
a,
b=y
)
)
)
)
)
)
)Excel solution 13 for Create half-yearly sorted table from data, proposed by Bhavya Gupta:
=LET(
d,
A2:A20,
GROUPBY(
HSTACK(
YEAR(
d
),
IF(
MONTH(
d
)<7,
"1H",
"2H"
)
),
d,
HSTACK(
MIN,
MAX
),
0,
0,
{1,
2}
)
)Excel solution 14 for Create half-yearly sorted table from data, proposed by 🇵🇪 Ned Navarrete C.:
=LET(r,
SORT(
A2:A20
),
y,
YEAR(
r
),
s,
IF(
MONTH(
r
)<=6,
"1H",
"2H"
),
m,
TOCOL(
UNIQUE(
y
)&"*"&TOROW(
UNIQUE(
s
)
)
),
REDUCE(C2:F2,
m,
LAMBDA(c,
v,
LET(yn,
--LEFT(
v,
4
),
sn,
RIGHT(
v,
2
),
f,
FILTER(r,
(y=yn)*(s=sn)),
VSTACK(
c,
HSTACK(
yn,
sn,
MIN(
f
),
MAX(
f
)
)
)))))Excel solution 15 for Create half-yearly sorted table from data, proposed by Charles Roldan:
=DROP(
REDUCE(
{0,
0},
{1;2;3;4;5;6},
LAMBDA(
a,
b,
VSTACK(
a,
TAKE(
SORT(
FILTER(
A2:A20,
b=MATCH(
A2:A20,
{44197;44378;44562;44743;44927;45108}
)
)
),
{1,
-1}
)
)
)
),
1
)Excel solution 16 for Create half-yearly sorted table from data, proposed by Pieter de Bruijn:
=LET(d,
SORT(
A2:A20
),
y,
YEAR(
d
),
h,
1+(MONTH(
d
)>6)&"H",
HSTACK(VSTACK(
{"Year",
"Half"},
UNIQUE(
HSTACK(
y,
h
)
)
),
REDUCE({"Min",
"Max"}&" Date",
UNIQUE(
h&y
),
LAMBDA(a,
b,
LET(c,
TOCOL(d/(b=h&y),
2),
VSTACK(
a,
HSTACK(
MIN(
c
),
MAX(
c
)
)
))))))
or
=LET(d,
A2:A20,
y,
YEAR(
d
),
h,
1+(MONTH(
d
)>6)&"H",
u,
UNIQUE(
h&y
),
x,
LAMBDA(u,
TOCOL(d/(u=(
h&y
)),
2)),
SORT(
HSTACK(
UNIQUE(
HSTACK(
y,
h
)
),
DROP(
REDUCE(
"",
u,
LAMBDA(
a,
b,
VSTACK(
a,
HSTACK(
MIN(
x(
b
)
),
MAX(
x(
b
)
)
)
)
)
),
1
)
),
{1,
2}
))
or
=LET(d,
SORT(
A2:A20
),
y,
YEAR(
d
)&"|"&1+(MONTH(
d
)>6),
u,
UNIQUE(
y
),
REDUCE({"Year",
"Half",
"Min Date",
"Max Date"},
SEQUENCE(
ROWS(
u
)
),
LAMBDA(a,
b,
VSTACK(a,
LET(z,
INDEX(
u,
b
),
HSTACK(TEXTSPLIT(
z,
"|"
),
TEXTAFTER(
z,
"|"
),
CHOOSECOLS(TOROW(d/(z=y),
2),
1,
-1)))))))
or using GROUPBY:
=GROUPBY(HSTACK(YEAR(
A2:A20
),
1+(MONTH(
A2:A20
)>6)&"H"),
A2:A20,
HSTACK(
MIN,
MAX
),
,
0)Excel solution 17 for Create half-yearly sorted table from data, proposed by Mihai Radu O:
LET(
a,
A2:A20,
y,
YEAR(
a
),
h,
IF(
MONTH(
a
) < 7,
"1H",
"2H"
),
tbl,
DROP(
GROUPBY(
HSTACK(
y,
h
),
a,
HSTACK(
MIN,
MAX
),
0,
0
),
1
),
hd,
HSTACK(
"Year",
"Half",
"Min Date",
"MaxDate"
),
VSTACK(
hd,
tbl
)
)Excel solution 18 for Create half-yearly sorted table from data, proposed by Abdelrahman Omer, MBA, PMP:
=LET(a,
A2:A20,
b,
YEAR(
a
),
c,
MONTH(
a
),
d,
((c<7)*1+(c>6)*2)&"H",
e,
HSTACK(
SORT(
VSTACK(
UNIQUE(
b
),
UNIQUE(
b
)
)
),
VSTACK(
UNIQUE(
d
),
UNIQUE(
d
),
UNIQUE(
d
)
)
),
VSTACK(C2:F2,
HSTACK(e,
BYROW(e,
LAMBDA(x,
MIN(FILTER(a,
(b=INDEX(
x,
,
1
)*(d=INDEX(
x,
,
2
))))))),
BYROW(e,
LAMBDA(x,
MAX(FILTER(a,
(b=INDEX(
x,
,
1
)*(d=INDEX(
x,
,
2
))))))))))Excel solution 19 for Create half-yearly sorted table from data, proposed by Rayan S.:
=VSTACK(
{"Year",
"Half",
"Min Date",
"Max Date"},
LET(
arr,
A2:A20,
y,
YEAR(
arr
),
m,
MONTH(
arr
),
x,
y & "_" & SWITCH(
TRUE,
m < 7,
"1H",
"2H"
),
u,
SORT(
UNIQUE(
x
)
),
HSTACK(
TEXTBEFORE(
u,
"_"
),
TEXTAFTER(
u,
"_"
),
TEXT(
MAP(
u,
LAMBDA(
a,
MIN(
FILTER(
arr,
x = a
)
)
)
),
"dd-mmm-yyyy"
),
TEXT(
MAP(
u,
LAMBDA(
a,
MAX(
FILTER(
arr,
x = a
)
)
)
),
"dd-mmm-yyyy"
)
)
)
)Excel solution 20 for Create half-yearly sorted table from data, proposed by Michael Hengst:
=SORTIEREN(EINDEUTIG(LET(v_date;A2:A20;v_half;AUFRUNDEN(MONAT(v_date)/6;0);v_half_start;DATUM(JAHR(v_date);v_half*6-5;1);v_half_end;DATUM(JAHR(v_date);v_half*6+1;0);HSTAPELN(JAHR(v_date);v_half&"H";MINWENNS(v_date;v_date;">="&v_half_start;v_date;"<="&v_half_end);MAXWENNS(v_date;v_date;">="&v_half_start;v_date;"<="&v_half_end))));{1;2};{1;1})
English version (untested):
=SORT(UNIQUE(LET(v_date;A2:A20;v_half;ROUNDUP(MONTH(v_date)/6;0);v_half_start;DATE(YEAR(v_date);v_half*6-5;1);v_half_end;DATE(YEAR(v_date);v_half*6+1;0);HSTACK(YEAR(v_date);v_half&"H";MINIFS(v_date;v_date;">="&v_half_start;v_date;"<="&v_half_end);MAXIFS(v_date;v_date;">="&v_half_start;v_date;"<="&v_half_end))));{1;2};{1;1})Solving the challenge of Create half-yearly sorted table from data with Python
Python solution 1 for Create half-yearly sorted table from data, proposed by Luke Jarych:
import pandas as pd
import datetime as dt
df = pd.read_excel("Date Min Max.xlsx", usecols=['Date'])
df['Half'] = df['Date'].dt.month // 7 + 1
df['Half'] = 'H' + df['Half'].astype(str)
df['Year'] = df['Date'].dt.year
result_df = df.groupby(['Year', 'Half']).agg(
Min_Date=('Date', 'min'),
Max_Date=('Date', 'max')
).reset_index()
result_df
Solving the challenge of Create half-yearly sorted table from data with Python in Excel
Python in Excel solution 1 for Create half-yearly sorted table from data, proposed by John V.:
Hi everyone!
One [Python] option could be:
d['Y'] = d['Date'].dt.year
d['H'] = d['Date'].apply(lambda x: '2H' if x.month > 6 else '1H')
g = d.groupby(['Y', 'H']).agg({'Date': ['min', 'max']}).reset_index()
g.columns = ['Year', 'Half', 'Min Date', 'Max Date']
g
Blessings!
Solving the challenge of Create half-yearly sorted table from data with R
R solution 1 for Create half-yearly sorted table from data, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Date Min Max.xlsx", range = "A1:A20")
test = read_excel("Date Min Max.xlsx", range = "C2:F8")
result = input %>%
mutate(Year = year(Date),
Half = str_c(semester(Date),"H")) %>%
group_by(Year, Half) %>%
summarise(`Min Date` = min(Date),
`Max Date` = max(Date)) %>%
ungroup()
&&