From the question table, extract the last transaction per product for each month.
📌 Challenge Details and Links
Challenge Number: 158
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Filter Last Transaction! with Power Query
Power Query solution 1 for Filter Last Transaction!, proposed by Zoran Milokanović:
let
Source = each Table.SelectRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content], _),
M = each DateTime.ToText([Date], "yyyyMM"),
P = each [Product ID],
S = Source(each Table.Last(Source((r) => M(r) = M(_) and P(r) = P(_))) = _)
in
SPower Query solution 2 for Filter Last Transaction!, 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.Group(AddMonth, {"Product ID", "Month"}, {{"All", each Table.Last(_)}}),
Expand = Table.ExpandRecordColumn(Group, "All", {"Date", "Quantity"}, {"Date", "Quantity"}),
Clean = Table.Sort(
Table.RemoveColumns(
Table.ReorderColumns(Expand, {"Month", "Date", "Product ID", "Quantity"}),
"Month"
),
{{"Date", Order.Ascending}, {"Product ID", Order.Ascending}}
)
in
CleanPower Query solution 3 for Filter Last Transaction!, proposed by Luan Rodrigues:
let
Fonte = Table.AddColumn(Tabela1, "Mes_Ano", each DateTime.ToText([Date], "MM/yyyy")),
grp = Table.Group(
Fonte,
{"Mes_Ano", "Product ID"},
{{"tab", each Table.RemoveColumns(Table.MaxN(_, {"Date"}, 1), {"Mes_Ano"})}}
)[tab],
cmb = Table.Combine(grp),
srt = Table.Sort(cmb, {"Date"})
in
srtPower Query solution 4 for Filter Last Transaction!, 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,{"Product ID","M"},{"G", each [[Date],[Product ID],[Quantity]]})[G],
Sol = Table.Sort(Table.Combine(List.Transform(b, each Table.LastN(_,1))),{"Date",0})
in
SolPower Query solution 5 for Filter Last Transaction!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(List.Combine(Table.Group(Source, "Date", {{"A", each
let
a = _,
b = Table.Group(a, "Product ID", {"B", each Table.LastN(_,1)})[B]
in b
}},0, (a,b)=> Number.From(Date.Month(a)<>Date.Month(b)))[A])),
Sol = Table.Sort(Group,{{"Date", 0}})
in
SolPower Query solution 6 for Filter Last Transaction!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Add = Table.AddColumn(Source, "A", each Text.From(Date.Month([Date])) & [Product ID]),
Group = Table.Combine(Table.Group(Add, {"A"}, {{"B", each Table.RemoveColumns(Table.LastN(_,1), "A")}})[B]),
Sol = Table.Sort(Group,{{"Date", 0}})
in
SolPower Query solution 7 for Filter Last Transaction!, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddStartOfMonth = Table.AddColumn(Source, "SoM", each Date.StartOfMonth([Date])),
Result = Table.Sort(
Table.FromRecords(
List.Transform(
Table.Group(AddStartOfMonth, {"Product ID", "SoM"}, {{"NT", each Table.Last(_)}})[NT],
each Record.RemoveFields(_, "SoM")
)
),
{{"Date", 0}}
)
in
ResultPower Query solution 8 for Filter Last Transaction!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Sort(
Table.FromRecords(
List.Combine(
Table.Group(
A,
"Date",
{"All", each Table.Group(_, "Product ID", {"Ans", each Table.Last(_)})[Ans]},
0,
(x, y) => Number.From(Date.Month(x) <> Date.Month(y))
)[All]
)
),
"Date"
)
in
BPower Query solution 9 for Filter Last Transaction!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(A, "EOM", each Date.EndOfMonth([Date])),
C = Table.Sort(
Table.Combine(
Table.Group(
B,
{"Product ID", "EOM"},
{"All", each Table.LastN([[Date], [Product ID], [Quantity]], 1)}
)[All]
),
"Date"
)
in
CPower Query solution 10 for Filter Last Transaction!, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(Source, "Month", each Date.Month([Date])),
Group = Table.Group(AddCol, {"Month", "Product ID"}, {"Date", each List.Max([Date])})[
[Date],
[Product ID]
],
Join = Table.Join(Source, {"Date", "Product ID"}, Group, {"Date", "Product ID"}),
Result = Table.TransformColumnTypes(Join, {"Date", type date})
in
ResultPower Query solution 11 for Filter Last Transaction!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddMonth = Table.AddColumn(Source, "Month", each DateTime.ToText([Date], "yyyy-MM")),
Group = Table.Group(AddMonth, {"Month", "Product ID"}, {{"Count", each Table.LastN(_, 1)}}),
Result = Table.Combine(Group[Count])[[Date], [Product ID], [Quantity]]
in
ResultPower Query solution 12 for Filter Last Transaction!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.AddColumn(S, "Month Name", each Date.MonthName([Date]), type text),
B = Table.Group(A, {"Product ID", "Month Name"}, {{"T", each _}}),
C = Table.AddColumn(B, "T2", each Table.FromColumns({{Table.Last(Table.Sort([T],{"Date",Order.Ascending}))[Date]},{Table.Last(Table.Sort([T],{"Date",Order.Ascending}))[Product ID]},{Table.Last(Table.Sort([T],{"Date",Order.Ascending}))[Quantity]}},{"Date","Product ID","Quantity"})),
D = Table.Combine(C[T2]),
E = Table.TransformColumnTypes(D,{{"Date", type date}}),
F = Table.Sort(E,{{"Date", Order.Ascending}})
in
FPower Query solution 13 for Filter Last Transaction!, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Q"]}[Content],
A = Table.AddColumn(Source, "Month Name", each Date.MonthName([Date]), type text),
B = Table.Group(A, {"Month Name", "Product ID"}, {{"LastDay", each Table.LastN(_, 1)}}),
C = Table.ExpandTableColumn(B, "LastDay", {"Date", "Quantity"}, {"Date", "Quantity"}),
D = Table.SelectColumns(C, {"Date", "Product ID", "Quantity"}),
E = Table.TransformColumnTypes(
D,
{{"Date", type date}, {"Product ID", type text}, {"Quantity", Int64.Type}}
),
Result = Table.Sort(E, {{"Date", Order.Ascending}})
in
ResultPower Query solution 14 for Filter Last Transaction!, proposed by Liuxuan Fan:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Inserted Year" = Table.AddColumn(
#"Promoted Headers",
"Year",
each Date.Year([Date]),
Int64.Type
),
#"Inserted Month" = Table.AddColumn(
#"Inserted Year",
"Month",
each Date.Month([Date]),
Int64.Type
),
#"Grouped Rows" = Table.Group(
#"Inserted Month",
{"Product ID", "Year", "Month"},
{{"Latest", each List.Max([Date]), type nullable datetime}}
),
#"Merged Queries" = Table.NestedJoin(
#"Grouped Rows",
{"Product ID", "Latest"},
#"Table 1",
{"Column1", "Question"},
"Table 1",
JoinKind.LeftOuter
),
#"Expanded Table 1" = Table.ExpandTableColumn(
#"Merged Queries",
"Table 1",
{"Column2"},
{"Table 1.Column2"}
),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table 1", {"Year", "Month"}),
#"Reordered Columns" = Table.ReorderColumns(
#"Removed Columns",
{"Latest", "Product ID", "Table 1.Column2"}
),
#"Renamed Columns" = Table.RenameColumns(
#"Reordered Columns",
{{"Table 1.Column2", "Quantity"}, {"Latest", "Date"}}
),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}})
in
#"Changed Type"Power Query solution 15 for Filter Last Transaction!, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
SoM = Table.AddColumn(Source, "SoM", each Date.StartOfMonth([Date])),
LT = Table.Group(SoM, {"Product ID", "SoM"}, {{"LT", each Table.LastN(_, 1)}})[LT],
Res = Table.Sort(Table.Combine(LT, Table.ColumnNames(Source)), {{"Date", 0}})
in
ResPower Query solution 16 for Filter Last Transaction!, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Table.AddColumn(Source, "Month", each Date.Month([Date])),
{"Product ID", "Month"},
{{"All", each Table.LastN(_, 1)}}
),
Final = Table.RemoveColumns(Table.Sort(Table.Combine(Group[All]), {"Date"}), "Month")
in
FinalSolving the challenge of Filter Last Transaction! with Excel
Excel solution 1 for Filter Last Transaction!, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
B3:B14,
p,
C3:C14,
FILTER(
B3:D14,
MAXIFS(
d,
p,
p,
d,
"<="&EOMONTH(
+d,
0
)
)=d
)
)Excel solution 2 for Filter Last Transaction!, proposed by 🇰🇷 Taeyong Shin:
=LET(
s,
SORT(
B2:D14,
,
-1
),
DROP(
GROUPBY(
TEXT(
TAKE(
s,
,
2
),
"em"
),
s,
SINGLE,
3,
0,
3
),
,
2
)
)Excel solution 3 for Filter Last Transaction!, proposed by Oscar Mendez Roca Farell:
=LET(
s,
SORT(
B3:D14,
,
-1
),
DROP(
GROUPBY(
HSTACK(
MONTH(
TAKE(
s,
,
1
)
),
INDEX(
s,
,
2
)
),
s,
SINGLE,
,
0
),
,
2
)
)Excel solution 4 for Filter Last Transaction!, proposed by Julian Poeltl:
=LET(
D,
B3:B14,
I,
C3:C14,
M,
EOMONTH(
--D,
0
),
R,
REDUCE(
B2:D2,
TOCOL(
UNIQUE(
M
)&TOROW(
UNIQUE(
I
)
)
),
LAMBDA(
A,
B,
VSTACK(
A,
TAKE(
FILTER(
B3:D14,
M&I=B
),
-1
)
)
)
),
VSTACK(
TAKE(
R,
1
),
SORT(
DROP(
R,
1
)
)
)
)Excel solution 5 for Filter Last Transaction!, proposed by Kris Jaganah:
=LET(
a,
B3:B14,
b,
C3:C14,
d,
MONTH(
a
)&b,
e,
XLOOKUP(
d,
d,
a,
,
,
-1
),
VSTACK(
B2:D2,
DROP(
GROUPBY(
e,
B3:D14,
SINGLE,
,
0,
,
a=e
),
,
1
)
)
)Excel solution 6 for Filter Last Transaction!, proposed by Imam Hambali:
=LET( q,
B3:D14, pd,
C3:C14&TEXT(
B3:B14,
"yyyy-mm"
), CHOOSEROWS(
q,
SORT(
XMATCH(
UNIQUE(
pd
),
pd,
,
-1
)
)
))Excel solution 7 for Filter Last Transaction!, proposed by Imam Hambali:
=LET( q,
SORT(
B3:D14,
{2,
1},
-1
), cc,
CHOOSECOLS, pp,
cc(
q,
2
)&TEXT(
cc(
q,
1
),
"yyyy-mm"
), SORT(
FILTER(
q,
pp<>VSTACK(
0,
DROP(
pp,
-1
)
)
),
1
))Excel solution 8 for Filter Last Transaction!, proposed by Ivan William:
=DROP(
GROUPBY(
HSTACK(
MONTH(
B2:B14
),
C2:C14
),
B2:D14,
LAMBDA(
x,
@TAKE(
x,
-1
)
),
3,
0,
3
),
,
2
)Excel solution 9 for Filter Last Transaction!, proposed by Sunny Baggu:
=REDUCE(
B2:D2,
UNIQUE(MONTH(B3:B14)),
LAMBDA(x, y,
VSTACK(
x,
DROP(
REDUCE(
"🌼",
UNIQUE(C3:C14),
LAMBDA(a, v,
VSTACK(a, TAKE(FILTER(B3:D14, (MONTH(B3:B14) = y) * (C3:C14 = v)), -1))
)
),
1
)
)
)
)Excel solution 10 for Filter Last Transaction!, proposed by Asheesh Pahwa:
=LET(
d,
B3:B14,
m,
MONTH(
d
),
p,
C3:C14,
up,
UNIQUE(
p
),
u,
UNIQUE(
m
),
REDUCE(
B2:D2,
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B3:D14,
m=y
),
t,
INDEX(
f,
,
2
),
SORT(
DROP(
REDUCE(
"",
up,
LAMBDA(
a,
v,
VSTACK(
a,
TAKE(
FILTER(
f,
t=v
),
-1
)
)
)
),
1
)
)
)
)
)
)
)Excel solution 11 for Filter Last Transaction!, proposed by ferhat CK:
=LET(a,GROUPBY(MONTH(B3:B14)&"-"&C3:C14,B3:B14,MAX,,0),b,TEXTAFTER(TAKE(a,,1),"-"),c,TAKE(a,,-1),SORT(HSTACK(b,c,XLOOKUP(b&c,C3:C14&B3:B14,B3:B14)),2))Excel solution 12 for Filter Last Transaction!, proposed by Hamidi Hamid:
=LET(
c,
C3:C14,
b,
B3:B14,
x,
GROUPBY(
MONTH(
b
)&"-"&c,
D3:D14,
MIN,
0,
0
),
p,
TAKE(
x,
,
-1
),
y,
TEXTAFTER(
TAKE(
x,
,
1
),
"-",
, ),
z,
XLOOKUP(
y&p,
c&D3:D14,
b,
"",
1
),
SORT(
HSTACK(
z,
y,
p
),
,
1
)
)Excel solution 13 for Filter Last Transaction!, proposed by Hussein SATOUR:
=SORT(DROP(REDUCE("",TOCOL({6;7}&{"A","B"}),LAMBDA(x,y,VSTACK(x,XLOOKUP(y,MONTH(B3:B14)&C3:C14,B3:D14,,,-1)))),1))Excel solution 14 for Filter Last Transaction!, proposed by Md. Zohurul Islam:
=LET( hdr,
B2:D2, dt,
B3:B14, prd,
C3:C14, qty,
D3:D14, mnth,
MONTH(
dt
), a,
mnth & "-" & prd, b,
GROUPBY(
a,
dt,
MAX,
0,
0
), e,
BYROW(
b,
LAMBDA(
x,
CONCAT(
x
)
)
), f,
XLOOKUP(
e,
a & dt,
qty
), g,
SORT(
HSTACK(
b,
f
),
2,
1
), h,
TAKE(
g,
,
1
), j,
TEXTAFTER(
h,
"-"
), rng,
CHOOSECOLS(
HSTACK(
j,
DROP(
g,
,
1
)
),
2,
1,
3
), result,
VSTACK(
hdr,
rng
), result)Excel solution 15 for Filter Last Transaction!, proposed by Nicolas Micot:
=LET(_dates;
B3:B14;
_produits;
C3:C14;
_tableau;
B3:D14;_index;
MAP(_dates;
_produits;
LAMBDA(l_date;
l_produit;
1+SOMME((MOIS(
_dates
)=MOIS(
l_date
))*(_produits=l_produit)*(_dates>l_date))));FILTRE(
_tableau;
_index=1
))Excel solution 16 for Filter Last Transaction!, proposed by Pieter de B.:
=LET(
s,
SORT,
x,
s(
B3:D14,
,
-1
),
c,
CHOOSECOLS,
s(
c(
GROUPBY(
TEXT(
c(
x,
1,
2
),
{"m",
"@"}
),
c(
x,
1,
3
),
SINGLE,
,
0
),
3,
2,
4
)
)
)Excel solution 17 for Filter Last Transaction!, proposed by Tomasz Jakóbczyk:
=SORTBY(
B3:D14,
C3:C14,
1,
B3:B14,
-1
)
F3: =SORT(
FILTER(
J3#,
MONTH(
J3:J14
)<>MONTH(
J2:J13
)
)
)Solving the challenge of Filter Last Transaction! with Python
Python solution 1 for Filter Last Transaction!, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-158 Filter the last transaction in mounth.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=12)
test = pd.read_excel(path, usecols="F:H", skiprows=1, nrows=4).rename(columns=lambda x: x.split('.')[0])
input['Date'] = pd.to_datetime(input['Date'])
input['month'] = input['Date'].dt.month
result = input.loc[input.groupby(['Product ID', 'month'])['Date'].idxmax()].sort_values(by='Date').reset_index(drop=True).drop(columns=['month'])
print(result.equals(test)) # TruePython solution 2 for Filter Last Transaction!, proposed by Luan Rodrigues:
import pandas as pd
file = "CH-158 Filter the last transaction in mounth.xlsx"
df = pd.read_excel(file,usecols='B:D',skiprows=1)
df['Mes_Ano'] = df['Date'].astype('str').apply(lambda x: x[:7])
grp = df.loc[
df.groupby(['Mes_Ano', 'Product ID'])['Date'].idxmax
].reset_index(drop=True)
del grp['Mes_Ano']
print(grp)Solving the challenge of Filter Last Transaction! with Python in Excel
Python in Excel solution 1 for Filter Last Transaction!, proposed by Alejandro Campos:
df = xl("B2:D14", headers=True)
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
result = (df.assign(YearMonth=df['Date'].dt.to_period('M'))
.sort_values('Date', ascending=False)
.drop_duplicates(['YearMonth', 'Product ID'])
.drop(columns='YearMonth')
.sort_values('Date')
.reset_index(drop=True))
resultSolving the challenge of Filter Last Transaction! with R
R solution 1 for Filter Last Transaction!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-158 Filter the last transaction in mounth.xlsx"
input = read_excel(path, range = "B2:D14")
test = read_excel(path, range = "F2:H6")
result = input %>%
group_by(`Product ID`, month = month(Date)) %>%
filter(Date == max(Date)) %>%
ungroup() %>%
select(-month)
all.equal(result, test)
# [1] TRUE
Solving the challenge of Filter Last Transaction! with Google Sheets
Google Sheets solution 1 for Filter Last Transaction!, proposed by Milan Shrimali:
Google Sheets:
=SORT(REDUCE("",UNIQUE(B3:B14),LAMBDA(X,Y,VSTACK(X,BYROW(Y,LAMBDA(X,LET(a,FILTER(A3:C14,B3:B14=X),MNTH,ARRAYFORMULA(UNIQUE(MONTH(CHOOSECOLS(A,1)))),BYROW(MNTH,LAMBDA(X,CHOOSEROWS(SORT(FILTER(A,MONTH(CHOOSECOLS(A,1))=X),1,TRUE),-1))))))))),1,TRUE,2,TRUE)Google Sheets solution 2 for Filter Last Transaction!, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1970617058#gid=1970617058