From the provided “transactions” table, filter the first transaction for each
📌 Challenge Details and Links
Challenge Number: 144
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of First Date Of Months! with Power Query
Power Query solution 1 for First Date Of Months!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRecords(
Table.Group(
Source,
"Date",
{"R", Table.First},
0,
(b, n) => Byte.From(Date.Month(b) <> Date.Month(n))
)[R]
)
in
SPower Query solution 2 for First Date Of Months!, proposed by Zoran Milokanović:
let
Source = Table.ToRecords(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]),
S = Table.FromRecords(
List.TransformMany(
Source,
each {{}, {_}}{
Byte.From(
_
= List.Select(
Source,
(r) =>
let
f = each Date.StartOfMonth([Date])
in
f(r) = f(_)
){0}
)
},
(i, _) => _
)
)
in
SPower Query solution 3 for First Date Of Months!, proposed by Brian Julius:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Date", Date.Type}
),
AddMonth = Table.AddColumn(Source, "Month", each Date.Month([Date])),
Group = Table.RemoveColumns(
Table.Group(AddMonth, {"Month"}, {{"All", each Table.FirstN(_, 1)}}),
"Month"
),
Exp = Table.ExpandTableColumn(
Group,
"All",
{"Date", "Product", "Qty"},
{"Date", "Product", "Qty"}
)
in
ExpPower Query solution 4 for First Date Of Months!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
grp = Table.Group(
Fonte,
{"Date"},
{{"Contagem", each _[Qty]{0}}},
0,
(a, b) => Number.From(Date.Month(Date.From(a[Date])) <> Date.Month(Date.From(b[Date])))
)
in
grpPower Query solution 5 for First Date Of Months!, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.AddColumn(S,"M", each Date.Month([Date])),
b = Table.Group(a,{"M"},{"G", each Table.FirstN(Table.RemoveColumns(_,"M"),1)}),
Sol = Table.Combine(b[G])
in
SolPower Query solution 6 for First Date Of Months!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Date",
{"A", each Table.Min(_, "Date")},
1,
(x, y) => Value.Compare(Date.Month(x), Date.Month(y))
),
Return = Table.FromRecords(Group[A])
in
ReturnPower Query solution 7 for First Date Of Months!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Combine(Table.Group(Source, "Date", {{"A", each Table.FromRows({Table.ToRows(_){0}},
Table.ColumnNames(_))}},0, (a,b)=>Number.From(Date.Month(b)<>Date.Month(a)))[A])
in
SolPower Query solution 8 for First Date Of Months!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Month = Table.AddColumn(Source, "Custom", each Date.Month([Date])),
Sol = Table.Combine(Table.Group(Month, {"Custom"}, {{"A", each
let
a = List.RemoveLastN(List.Transform(Table.ToRows(_){0}, each _)),
b = Table.FromRows({a}, Table.ColumnNames(Source))
in b}})[A])
in
SolPower Query solution 9 for First Date Of Months!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(A, "Month", each Date.Month([Date])),
C = Table.Combine(
Table.Group(B, {"Month"}, {"All", each Table.FirstN(_, 1)[[Date], [Product], [Qty]]})[All]
)
in
CPower Query solution 10 for First Date Of Months!, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(Source, "Period", each DateTime.ToText([Date], [Format = "yyyy_MM"])),
Group = Table.Group(AddCol, "Period", {"MinDates", each List.Min([Date])}),
Select = Table.SelectRows(Source, each List.Contains(Group[MinDates], [Date])),
Result = Table.TransformColumnTypes(Table.Distinct(Select, "Date"), {"Date", type date})
in
ResultPower Query solution 11 for First Date Of Months!, proposed by Nelson Mwangi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
MonthCol = Table.AddColumn(Source, "Month", each Date.Month([Date]), Int64.Type),
Group = Table.Group(
MonthCol,
{"Month"},
{{"First", each List.Min([Date]), type datetime}, {"All", each _}}
),
TblFromLst = Table.FromList(
Table.AddColumn(Group, "Record", each [All]{0}[[Date], [Product], [Qty]])[Record],
each Record.FieldValues(_),
{"Date", "Product", "Qty"}
)
in
TblFromLstPower Query solution 12 for First Date Of Months!, proposed by Nelson Mwangi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
MonthCol = Table.AddColumn(Source, "Month", each Date.Month([Date]), Int64.Type),
Group = Table.Group(MonthCol, {"Month"}, {"All", each _{0}}),
Expand = Table.RemoveColumns(
Table.ExpandRecordColumn(Group, "All", {"Date", "Product", "Qty"}),
{"Month"}
)
in
ExpandPower Query solution 13 for First Date Of Months!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddMonth = Table.AddColumn(Source, "Month", each Date.StartOfMonth([Date])),
Group = Table.Group(AddMonth, {"Month"}, {{"x", each _{0}[[Date], [Product], [Qty]]}}),
Result = Table.FromRecords(Group[x])
in
ResultPower Query solution 14 for First Date Of Months!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.TransformColumnTypes(
Source,
{{"Date", type date}, {"Product", type text}, {"Qty", Int64.Type}}
),
B = Table.AddColumn(A, "Month Name", each Date.MonthName([Date]), type text),
C = Table.Group(B, {"Month Name"}, {{"T", each _}}),
D = Table.AddColumn(C, "T2", each Table.First([T])),
E = Table.SelectColumns(D, {"T2"}),
F = Table.ExpandRecordColumn(
E,
"T2",
{"Date", "Product", "Qty", "Month Name"},
{"Date", "Product", "Qty", "Month Name"}
),
G = Table.RemoveColumns(F, {"Month Name"})
in
GPower Query solution 15 for First Date Of Months!, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index", {{"Date", type date}}),
#"Inserted Month Name" = Table.AddColumn(
#"Changed Type",
"Month Name",
each Date.MonthName([Date]),
type text
),
#"Grouped Rows" = Table.Group(
#"Inserted Month Name",
{"Month Name"},
{
{
"New",
each _,
type table [
Date = nullable date,
Product = text,
Qty = number,
Index = number,
Month Name = text
]
}
}
),
Custom1 = Table.AddColumn(#"Grouped Rows", "First", each Table.FirstN([New], 1)),
#"Removed Columns" = Table.RemoveColumns(Custom1, {"New", "Month Name"}),
#"Expanded First" = Table.ExpandTableColumn(
#"Removed Columns",
"First",
{"Date", "Product", "Qty"},
{"Date", "Product", "Qty"}
)
in
#"Expanded First"Power Query solution 16 for First Date Of Months!, proposed by Alison Pezzott:
let
source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
selectedRows = Table.SelectRows(
source,
(r) => r[Date] = List.Min(List.Select(source[Date], (i) => Date.StartOfMonth(i) = Date.StartOfMonth(r[Date])))
),
duplicatedRemoved = Table.Distinct(selectedRows,{"Date"})
in
duplicatedRemoved
```Power Query solution 17 for First Date Of Months!, proposed by Vida Vaitkunaite:
let
SourceAndMonth = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
"Month",
each Date.Month([Date])
),
Group = Table.Group(SourceAndMonth, {"Month"}, {{"All", each Table.FirstN(_, 1), type table}}),
Expand = Table.RemoveColumns(
Table.ExpandTableColumn(Group, "All", {"Date", "Product", "Qty"}, {"Date", "Product", "Qty"}),
{"Month"}
)
in
ExpandSolving the challenge of First Date Of Months! with Excel
Excel solution 1 for First Date Of Months!, proposed by Aditya Kumar Darak 🇮🇳:
=DROP(
GROUPBY(
MONTH(
C3:C27
),
C3:E27,
SINGLE,
0,
0
),
,
1
)
=CHOOSEROWS(
C3:E27,
XMATCH(
UNIQUE(
MONTH(
C3:C27
)
),
MONTH(
C3:C27
)
)
)Excel solution 2 for First Date Of Months!, proposed by Julian Poeltl:
=LET(
D,
C3:C27,
A,
D3:D27,
Q,
E3:E27,
M,
MONTH(
D
)&YEAR(
D
),
U,
UNIQUE(
M
),
REDUCE(
C2:E2,
U,
LAMBDA(
A,
B,
VSTACK(
A,
TAKE(
SORT(
FILTER(
C3:E27,
M=B
)
),
1
)
)
)
)
)Excel solution 3 for First Date Of Months!, proposed by Kris Jaganah:
=DROP(
GROUPBY(
MONTH(
C2:C27
),
C2:E27,
SINGLE,
3,
0
),
,
1
)Excel solution 4 for First Date Of Months!, proposed by Imam Hambali:
=FILTER(
C3:E27,
MONTH(
C3:C27
)<>DROP(
VSTACK(
0,
MONTH(
C3:C27
)
),
-1
)
)Excel solution 5 for First Date Of Months!, proposed by Sunny Baggu:
=LET(
_m, MONTH(C3:C27),
_um, UNIQUE(_m) + N("🌻"),
CHOOSEROWS(
C3:E27,
MAP(_um, LAMBDA(a, XMATCH(TRUE, _m = a)))
)
)Excel solution 6 for First Date Of Months!, proposed by Sunny Baggu:
=LET(
_m, MONTH(C3:C27),
_um, UNIQUE(_m) + N("📅"),
CHOOSEROWS(C3:E27, XMATCH(_um, _m))
)Excel solution 7 for First Date Of Months!, proposed by Asheesh Pahwa:
=LET(m,MAP(UNIQUE(MONTH(C3:C27)),LAMBDA(x,LET(f,IF(MONTH(C3:C27)=x,C3:E27,""),TEXTBEFORE(TEXTJOIN("-",1,f),"-",3)))),DROP(REDUCE("",m,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,"-")))),1))Excel solution 8 for First Date Of Months!, proposed by Asheesh Pahwa:
=LET(dt,
MONTH((C3:C27)),
u,
UNIQUE(
dt
),
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
TAKE(
FILTER(
C3:E27,
dt=y
),
1
)
)
)
),
1
))Excel solution 9 for First Date Of Months!, proposed by Eddy Wijaya:
=DROP(
REDUCE(
0,
UNIQUE(
MONTH(
C3:C27
)
),
LAMBDA(
a,
v,
VSTACK(
a,
TAKE(
FILTER(
C3:E27,
v=MONTH(
C3:C27
)
),
1
)
)
)
),
1
)Excel solution 10 for First Date Of Months!, proposed by Fausto Bier:
=REDUCE(G2:I2,UNIQUE(MONTH(C3:C27)),LAMBDA(a,v,STACK.VERT(a,XLOOKUP(DATE(2024,v,1),C3:C27,C3:E27,,1))))Excel solution 11 for First Date Of Months!, proposed by ferhat CK:
=LET(
a,
LAMBDA(
x,
XLOOKUP(
UNIQUE(
MONTH(
C3:C27
)
),
MONTH(
C3:C27
),
x
)
),
HSTACK(
a(
C3:C27
),
a(
D3:D27
),
a(
E3:E27
)
)
)Excel solution 12 for First Date Of Months!, proposed by Hamidi Hamid:
=LET(
z,
C3:C27,
x,
DROP(
REDUCE(
0,
TOCOL(
MAP(
UNIQUE(
MONTH(
z
)
),
D3:D27,
LAMBDA(
a,
b,
TAKE(
FILTER(
TEXT(
z,
"dd/mm/e"
)&"-"&D3:D27&"-"&E3:E27,
MONTH(
z
)=a
),
1
)
)
),
3
),
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
"-",
)
)
)
),
1
),
VSTACK(
C2:E2,
x
)
)Excel solution 13 for First Date Of Months!, proposed by Hussein SATOUR:
=LET(
a,
MONTH(
C3:C27
),
CHOOSEROWS(
C3:E27,
XMATCH(
UNIQUE(
a
),
a
)
)
)Excel solution 14 for First Date Of Months!, proposed by Md. Zohurul Islam:
=LET(rng,C3:E27,p,MONTH(C3:C27),q,UNIQUE(p),s,REDUCE(C2:E2,q,LAMBDA(x,y,LET(a,FILTER(rng,p=y),b,SORT(a,1,1),c,TAKE(b,1),d,VSTACK(x,c),d))),s)Excel solution 15 for First Date Of Months!, proposed by Nicolas Micot:
=FILTRE(
C3:E27;
SCAN(
1;
C3:C27;
LAMBDA(
l_valeur;
l_tableau;
SIERREUR(
SI(
MOIS(
l_tableau
)<>MOIS(
DECALER(
l_tableau;
-1;
0
)
);
1;
l_valeur+1
);
1
)
)
)=1
)Excel solution 16 for First Date Of Months!, proposed by Pieter de B.:
=REDUCE(
C2:E2,
UNIQUE(
MONTH(
C3:C27
)
),
LAMBDA(
x,
y,
VSTACK(
x,
TAKE(
FILTER(
C3:E27,
MONTH(
C3:C27
)=y
),
1
)
)
)
)
Or
=LET(i,
LAMBDA(
j,
INDEX(
C3:E27,
,
j
)
),
X,
LAMBDA(y,
XLOOKUP(--(UNIQUE(
MONTH(
i(
1
)
)
)&-1),
i(
1
),
y,
,
1)),
HSTACK(
X(
i(
1
)
),
X(
i(
2
)
),
X(
i(
3
)
)
))Excel solution 17 for First Date Of Months!, proposed by Rick Rothstein:
=LET(
m,
MONTH(
C3:C27
),
INDEX(
C3:E27,
XMATCH(
UNIQUE(
m
),
m
),
{1,
2,
3}
)
)Excel solution 18 for First Date Of Months!, proposed by Tomasz Jakóbczyk:
=VSTACK(
C3:E3,
FILTER(
C4:E27,
MONTH(
C4:C27
)>MONTH(
C3:C26
)
)
)Solving the challenge of First Date Of Months! with Python
Python solution 1 for First Date Of Months!, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-144 First transaction in each month.xlsx"
input = pd.read_excel(path, usecols="C:E", skiprows=1, nrows=26)
test = pd.read_excel(path, usecols="G:I", skiprows=1, nrows=4).rename(columns=lambda x: x.split('.')[0])
result = input.assign(month=input['Date'].dt.month).groupby('month').head(1).drop(columns='month').reset_index(drop=True)
print(result.equals(test)) # True
Python solution 2 for First Date Of Months!, proposed by Luan Rodrigues:
import pandas as pd
file = "CH-144 First transaction in each month.xlsx"
df = pd.read_excel(file,usecols="C:E",skiprows=1)
df['month'] = df['Date'].apply(lambda x: f'{x.month:02d}')
df_grp = df.groupby(['month'])[['Date','Product','Qty']].first().reset_index().iloc[:,1:]
print(df_grp)Python solution 3 for First Date Of Months!, proposed by Abdallah Ally:
import pandas as pd
# Load the Excel file
file_path = 'CH-144 First transaction in each month.xlsx'
df = pd.read_excel(file_path, usecols='C:E', skiprows=1)
# Perform data manipulation
df['Month'] = df['Date'].dt.strftime('%Y_%m')
min_dates = df.groupby('Month')['Date'].min().reset_index()
df = df[df['Date'].isin(min_dates['Date'])]
df = df.drop_duplicates(subset='Date', ignore_index=True).drop(columns='Month')
# Display the result
dfSolving the challenge of First Date Of Months! with Python in Excel
Python in Excel solution 1 for First Date Of Months!, proposed by Alejandro Campos:
df = xl("C2:E27", headers=True)
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
filtered_df = df.sort_values('Date').groupby(
df['Date'].dt.to_period('M')).first().reset_index(drop=True)
filtered_dfSolving the challenge of First Date Of Months! with R
R solution 1 for First Date Of Months!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-144 First transaction in each month.xlsx"
input = read_excel(path, range = "C2:E27")
test = read_excel(path, range = "G2:I6")
result = input %>%
mutate(month = month(Date)) %>%
filter(row_number() == 1, .by = month) %>%
select(-month)
all.equal(result, test, check.attributes = FALSE)
# [1] TRUESolving the challenge of First Date Of Months! with Google Sheets
Google Sheets solution 1 for First Date Of Months!, proposed by Milan Shrimali:
GOOGLE SHEETS
=let(data,C3:E18,mnth,byrow(choosecols(data,1),lambda(x,month(x))),byrow(UNIQUE(mnth),lambda(X,chooserows(sort(filter(data,month(choosecols(data,1))=x),1,1),1))))Google Sheets solution 2 for First Date Of Months!, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1899387542#gid=1899387542