In Question Table 1, sales transactions are provided. Unfortunately, in this system, when a customer’s identity changes, their ID is also changed which its historical values are provided in Table 2. We want to calculate the total sales per customer based on their latest ID, as shown in the result table. For example, since C-5 changed to C-8, the total sales to C-5 and C-8 are combined, as highlighted in the cell.
📌 Challenge Details and Links
Challenge Number: 61
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sales Per Customer with Power Query
Power Query solution 1 for Sales Per Customer, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T2 = Table.ToColumns(Source("Table2")),
S = Table.Sort(
Table.Group(
List.Accumulate(
List.Positions(T2{0}),
Source("Table1"),
(s, c) => Table.ReplaceValue(s, T2{0}{c}, T2{1}{c}, Replacer.ReplaceValue, {"Customer ID"})
),
"Customer ID",
{"Sales", each List.Sum([Quantity])}
),
each Number.From(Text.AfterDelimiter([Customer ID], "-"))
)
in
SPower Query solution 2 for Sales Per Customer, proposed by Ramiro Ayala Chávez:
let
t2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
t1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a1 = List.Accumulate(List.Positions(t2[New ID]),t1,(s,c)=>Table.ReplaceValue(s,t2[OLD ID]{c},t2[New ID]{c},Replacer.ReplaceText,{"Customer ID"})),
b = Table.TransformColumns(a1,{"Customer ID", each if List.ContainsAny({_},t1[Customer ID]&t2[New ID]) then _ else Text.RemoveRange(_,Text.Length(_)-2,1)}),
t3 = Table.LastN(t2,1),
a2 = List.Accumulate(List.Positions(t3[New ID]),b,(s,c)=>Table.ReplaceValue(s,t3[OLD ID]{c},t3[New ID]{c},Replacer.ReplaceText,{"Customer ID"})),
c = Table.Group(a2,{"Customer ID"},{"Sales", each List.Sum([Quantity])}),
d = Table.TransformColumnTypes(Table.SplitColumn(c,"Customer ID",Splitter.SplitTextByDelimiter("-"),{"C1","C2"}),{"C2",Int64.Type}),
Sol = Table.CombineColumns(Table.TransformColumnTypes(Table.Sort(d,{"C2",0}),{"C2",type text}),{"C1","C2"},Combiner.CombineTextByDelimiter("-"),"Customer")
in
SolPower Query solution 3 for Sales Per Customer, proposed by Aditya Kumar Darak 🇮🇳:
let
Transactions = Excel.CurrentWorkbook(){[Name = "Transactions"]}[Content],
Changes = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Changes"]}[Content]),
Replaced = List.Accumulate(
Changes,
Transactions,
(s, c) => Table.ReplaceValue(s, c{0}, c{1}, Replacer.ReplaceValue, {"Customer ID"})
),
Group = Table.Group(Replaced, "Customer ID", {"Sales", each List.Sum([Quantity])}),
Return = Table.Sort(Group, each Number.From(Text.Select([Customer ID], {"0" .. "9"})))
in
ReturnPower Query solution 4 for Sales Per Customer, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
Replace = List.Accumulate(
Table.ToRows(Tbl2),
Source[Customer ID],
(s, c) => List.ReplaceMatchingItems(s, {c})
),
NewTbl = Table.FromColumns({Replace, Source[Quantity]}, {"Customer", "A"}),
Sol = Table.Sort(
Table.Group(NewTbl, {"Customer"}, {{"Sales", each List.Sum([A])}}),
each Number.From(Text.Select([Customer], {"0" .. "9"}))
)
in
SolPower Query solution 5 for Sales Per Customer, proposed by Abdallah Ally:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
t3 = List.Accumulate(
t2[OLD ID],
t1,
(x, y) =>
Table.ReplaceValue(x, y, t2{[OLD ID = y]}[New ID], Replacer.ReplaceValue, {"Customer ID"})
),
t4 = Table.AddColumn(t3, "Order", each Number.FromText(Text.AfterDelimiter([Customer ID], "-"))),
t5 = Table.RenameColumns(t4, {"Customer ID", "Customer"}),
t6 = Table.Group(t5, {"Order", "Customer"}, {{"Sales", each List.Sum([Quantity]), type number}}),
t7 = Table.Sort(t6, {{"Order", Order.Ascending}}),
t8 = Table.RemoveColumns(t7, {"Order"})
in
t8Power Query solution 6 for Sales Per Customer, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S2 = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content],
S2e = Table.AddIndexColumn(S2, "Index", 0, 1, Int64.Type),
S1 = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content],
A = Table.AddIndexColumn(S1, "Index", 0, 1, Int64.Type),
B = Table.AddColumn(
A,
"Customer ID.",
each List.Accumulate(
S2e[Index],
S1[Customer ID],
(s, c) => List.ReplaceValue(s, S2e[OLD ID]{c}, S2e[New ID]{c}, Replacer.ReplaceValue)
){[Index]}
),
C = Table.SelectColumns(B, {"Customer ID.", "Quantity"}),
D = Table.Group(C, {"Customer ID."}, {{"Sales", each List.Sum([Quantity]), type number}}),
E = Table.AddColumn(
D,
"Text After Delimiter",
each Text.AfterDelimiter([#"Customer ID."], "-"),
type text
),
F = Table.TransformColumnTypes(E, {{"Text After Delimiter", Int64.Type}}),
G = Table.Sort(F, {{"Text After Delimiter", Order.Ascending}}),
H = Table.RemoveColumns(G, {"Text After Delimiter"})
in
HPower Query solution 7 for Sales Per Customer, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Merge_Tables = Table.ExpandTableColumn(
Table.NestedJoin(Source, {"Customer ID"}, Table2, {"OLD ID"}, "Table2", JoinKind.LeftOuter),
"Table2",
{"New ID"},
{"New ID"}
),
Customer = Table.AddColumn(
Merge_Tables,
"Latest ID",
each if [New ID] = null then [Customer ID] else [New ID]
),
#"Removed Columns" = Table.RemoveColumns(Customer, {"Date", "Customer ID", "New ID"}),
Grouping = Table.Group(
#"Removed Columns",
{"Latest ID"},
{{"Sales", each List.Sum([Quantity]), type number}}
),
Sorted = Table.Sort(Grouping, each Number.From(Text.Select([Latest ID], {"0" .. "9"})))
in
SortedPower Query solution 8 for Sales Per Customer, proposed by Venkata Rajesh:
let
Source = Data,
Custom = Table.AddColumn(
Source,
"Customer",
each [
fx = (c) =>
[
x = try ID_Changes{[OLD ID = c]}[New ID] otherwise null,
y = if x <> null then @fx(x) else c
][y],
z = fx([Customer ID])
][z]
),
Group = Table.Group(Custom, {"Customer"}, {{"Sales", each List.Sum([Quantity]), Int64.Type}}),
Sort = Table.Sort(Group, each Number.From(Text.AfterDelimiter([Customer], "-")))
in
SortSolving the challenge of Sales Per Customer with Excel
Excel solution 1 for Sales Per Customer, proposed by Bo Rydobon 🇹🇭:
=LET(
R,
LAMBDA(
R,
a,
LET(
b,
IFNA(
VLOOKUP(
a,
F3:G8,
2,
),
a
),
IF(
AND(
a=b
),
b,
R(
R,
b
)
)
)
),
x,
REGEXEXTRACT(
R(
R,
C3:C36
),
{"d+$",
".+"}
),
DROP(
GROUPBY(
IFERROR(
--x,
x
),
D3:D36,
SUM,
,
0
),
,
1
)
)Excel solution 2 for Sales Per Customer, proposed by Bo Rydobon 🇹🇭:
=LET(x,REGEXEXTRACT(REDUCE(C3:C36,{1,2,3},LAMBDA(a,w,IFNA(VLOOKUP(a,F3:G8,2,0),a))),{"d+$",".+"}),
DROP(GROUPBY(IFERROR(--x,x),D3:D36,SUM,,0),,1))Excel solution 3 for Sales Per Customer, proposed by محمد حلمي:
=REDUCE(C3:C36,SEQUENCE(9),LAMBDA(a,v,
IFNA(XLOOKUP(a,F3:F8,G3:G8),a)))Excel solution 4 for Sales Per Customer, proposed by محمد حلمي:
=LET(e,
REDUCE(
C3:C36,
SEQUENCE(
9
),
LAMBDA(
a,
v,
IFNA(
XLOOKUP(
a,
F3:F8,
G3:G8
),
a
)
)
),
u,
UNIQUE(
e
),
SORTBY(HSTACK(u,
MAP(u,
LAMBDA(a,
SUM((a=e)*D3:D36)))),
--TEXTAFTER(
u,
"-"
)))Excel solution 5 for Sales Per Customer, proposed by 🇵🇪 Ned Navarrete C.:
=LET(n,
MAP(
C3:C36,
LAMBDA(
r,
REDUCE(
r,
F3:F8&"*"&G3:G8,
LAMBDA(
c,
v,
XLOOKUP(
c,
TEXTBEFORE(
v,
"*"
),
TEXTAFTER(
v,
"*"
),
c
)
)
)
)
),
u,
UNIQUE(
n
),
w,
SORTBY(
u,
--TEXTAFTER(
u,
"-"
)
),
HSTACK(w,
MAP(w,
LAMBDA(f,
SUM((f=n)*D3:D36)))))Excel solution 6 for Sales Per Customer, proposed by Oscar Mendez Roca Farell:
=LET(
c,
I3:I10,
p,
".",
HSTACK(
c,
MAP(
c,
LAMBDA(
a,
SUM(
FILTER(
D3:D36,
TAKE(
REDUCE(
C3:C36&p,
G3:G8,
LAMBDA(
i,
x,
HSTACK(
i,
SUBSTITUTE(
i,
@+TAKE(
F3:x,
-1
)&p,
x&p
)
)
)
),
,
-1
)=a&"."
)
)
)
)
)
)Excel solution 7 for Sales Per Customer, proposed by Julian Poeltl:
=LET(
T,
B3:D36,
TT,
F3:G8,
C,
CHOOSECOLS(
T,
2
),
Q,
TAKE(
T,
,
-1
),
O,
TAKE(
TT,
,
1
),
N,
TAKE(
TT,
,
-1
),
UID,
UNIQUE(
C
),
QS,
MAP(
UID,
LAMBDA(
A,
SUM(
FILTER(
Q,
C=A
)
)
)
),
ENN,
FILTER(
N,
ISNUMBER(
XMATCH(
N,
O
)
)=FALSE
),
XL,
LAMBDA(
A,
XLOOKUP(
A,
N,
O
)
),
Fi,
XL(
ENN
),
Se,
XL(
Fi
),
Th,
XL(
Se
),
H,
HSTACK(
ENN,
Fi,
Se,
Th
),
RR,
HSTACK(
ENN,
BYROW(
MAP(
H,
LAMBDA(
A,
IFERROR(
FILTER(
QS,
UID=A
),
0
)
)
),
LAMBDA(
A,
SUM(
A
)
)
)
),
NRI,
FILTER(
HSTACK(
UID,
QS
),
ISNUMBER(
XMATCH(
UID,
TOCOL(
TT
)
)
)=FALSE
),
A,
SORT(
VSTACK(
RR,
NRI
)
),
SORTBY(
A,
LEN(
TAKE(
A,
,
1
)
)
)
)Excel solution 8 for Sales Per Customer, proposed by Julian Poeltl:
=LET(
T,
C3:D36,
I,
L_ReplaceMultipleListValues(
TAKE(
T,
,
1
),
F3,
G3
),
U,
UNIQUE(
I
),
A,
SORT(
HSTACK(
U,
MAP(
U,
LAMBDA(
A,
SUM(
FILTER(
TAKE(
T,
,
-1
),
I=A
)
)
)
)
)
),
SORTBY(
A,
LEN(
TAKE(
A,
,
1
)
)
)
)
Pre-programmed Lambda (used in challenge 47 before):
=LAMBDA(
Text,
ToBeReplaced_ListFirstEntryCell,
Replacements_ListFirstEntryCell,
IF(
ToBeReplaced_ListFirstEntryCell<>"",
L_ReplaceMultipleListValues(
IF(
EXACT(
Text,
ToBeReplaced_ListFirstEntryCell
),
SUBSTITUTE(
Text,
ToBeReplaced_ListFirstEntryCell,
Replacements_ListFirstEntryCell
),
Text
),
OFFSET(
ToBeReplaced_ListFirstEntryCell,
1,
0
),
OFFSET(
Replacements_ListFirstEntryCell,
1,
0
)
),
Text
)
)Excel solution 9 for Sales Per Customer, proposed by Julian Poeltl:
=LET(
T,
C3:D36,
ID,
TAKE(
T,
,
1
),
Old,
F3:F8,
New,
G3:G8,
I,
REDUCE(
ID,
Old&","&New,
LAMBDA(
A,
B,
LET(
Be,
TEXTBEFORE(
B,
","
),
Af,
TEXTAFTER(
B,
","
),
IF(
EXACT(
Be,
A
),
SUBSTITUTE(
A,
Be,
Af
),
A
)
)
)
),
U,
UNIQUE(
I
),
S,
SORT(
HSTACK(
U,
MAP(
U,
LAMBDA(
A,
SUM(
FILTER(
DROP(
T,
,
1
),
I=A
)
)
)
)
)
),
SORTBY(
S,
LEN(
TAKE(
S,
,
1
)
)
)
)Excel solution 10 for Sales Per Customer, proposed by Kris Jaganah:
=LET(a,
F3:F8,
b,
G3:G8,
c,
C3:C36,
d,
D3:D36,
e,
MAP(
a,
LAMBDA(
z,
REDUCE(
z,
SEQUENCE(
9
),
LAMBDA(
x,
y,
XLOOKUP(
x,
a,
b,
x
)
)
)
)
),
f,
IFNA(
XLOOKUP(
c,
a,
e
),
c
),
g,
UNIQUE(
f
),
h,
SORTBY(
g,
--TEXTAFTER(
g,
"-"
)
),
HSTACK(h,
MAP(h,
LAMBDA(v,
SUM((f=v)*d)))))Excel solution 11 for Sales Per Customer, proposed by Abdallah Ally:
=LET(
a,
C3:C36,
b,
MAP(
a,
LAMBDA(
x,
REDUCE(
x,
F3:F8,
LAMBDA(
u,
v,
LET(
c,
OFFSET(
v,
,
1
),
IF(
u=v,
c,
u
)
)
)
)
)
),
UNIQUE(
SORTBY(
DROP(
REDUCE(
"",
b,
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
y,
SUM(
FILTER(
D3:D36,
b=y
)
)
)
)
)
),
1
),
--TEXTAFTER(
b,
"-"
)
)
)
)Excel solution 12 for Sales Per Customer, proposed by Sunny Baggu:
=MAP(
I3:I10, LAMBDA(g, LET(
_a,
TOROW( UNIQUE(
TOCOL(
REDUCE(
g,
SEQUENCE(
9
),
LAMBDA(
a,
v,
VSTACK(
a,
IFERROR(
INDEX(
F3:F8,
XMATCH(
a,
G3:G8
)
),
g
)
)
)
),
3
)
) ), SUM((C3:C36 = _a) * D3:D36)
)
)
)Excel solution 13 for Sales Per Customer, proposed by Sunny Baggu:
=MAP(
I3:I10, LAMBDA(x, LET(
_a,
TOROW( UNIQUE(
REDUCE(
x,
SEQUENCE(
COUNTA(
F3:F8
)
),
LAMBDA(
a,
v,
VSTACK(
a,
XLOOKUP(
a,
G3:G8,
F3:F8,
a
)
)
)
)
) ), SUM((C3:C36 = _a) * D3:D36)
)
)
)Excel solution 14 for Sales Per Customer, proposed by Andy Heybruch:
=LET(_c1,
C3:C36,
_q,
D3:D36,
_cOld,
F3:F8,
_cNew,
G3:G8,_NewCust,
REDUCE(
_c1,
_cOld,
LAMBDA(
a,
v,
IF(
a=v,
XLOOKUP(
v,
_cOld,
_cNew
),
a
)
)
),_uCust,
SORTBY(
UNIQUE(
_NewCust
),
--TEXTAFTER(
UNIQUE(
_NewCust
),
"-"
)
),HSTACK(_uCust,
BYROW(_uCust,
LAMBDA(a,
SUM(_q*(_NewCust=a))))))Excel solution 15 for Sales Per Customer, proposed by Gerson Pineda:
=GROUPBY(
REDUCE(
C3:C36,
G3:G8,
LAMBDA(
a,
b,
IF(
a=@+F8:b,
b,
a
)
)
),
D3:D36,
SUM,
,
0
)Excel solution 16 for Sales Per Customer, proposed by Hussein SATOUR:
=LET(
O,
F3:F8,
C,
C3:C36,
f,
LAMBDA(
ME,
n,
IF(
ISNA(
XMATCH(
n,
O
)
),
n,
ME(
ME,
XLOOKUP(
n,
O,
G3:G8
)
)
)
),
a,
IFNA(
XLOOKUP(
C,
O,
MAP(
O,
LAMBDA(
x,
f(
f,
x
)
)
)
),
C
),
b,
UNIQUE(
a
),
HSTACK(
b,
MAP(
b,
LAMBDA(
y,
SUM(
FILTER(
D3:D36,
a=y
)
)
)
)
)
)Excel solution 17 for Sales Per Customer, proposed by LEONARD OCHEA 🇷🇴:
=LET(
r,
REDUCE(
C3:C36,
G3:G8,
LAMBDA(
a,
b,
IF(
a=@+F8:b,
b,
a
)
)
),
VSTACK(
I2:J2,
DROP(
GROUPBY(
HSTACK(
--TEXTAFTER(
r,
"-"
),
r
),
D3:D36,
SUM,
,
0
),
,
1
)
)
)Excel solution 18 for Sales Per Customer, proposed by Pieter de B.:
=LET(v,
LAMBDA(
x,
IFNA(
VLOOKUP(
x,
F3:G8,
2,
0
),
x
)
),
y,
v(
v(
v(
C3:C36
)
)
),
u,
UNIQUE(
y
),
SORTBY(HSTACK(u,
MAP(u,
LAMBDA(m,
SUM((y=m)*D3:D36)))),
--MID(
u,
3,
2
)))Excel solution 19 for Sales Per Customer, proposed by Rayan Saud:
=LET( Q,
$D$3:$D$36, ID,
REDUCE( $C$3:$C$36, $F$3:$F$8, LAMBDA(
a,
b,
MAP(
a,
LAMBDA(
x,
XLOOKUP(
x,
b,
OFFSET(
b,
,
1
),
x,
0
)
)
)
) ), nID,
UNIQUE(
ID
), HSTACK(
nID,
MAP(
nID,
LAMBDA(
x,
SUM(
FILTER(
Q,
ID = x
)
)
)
)
))