In online markets, when customers add items to their carts, other products, known as complementary products, which are often purchased with the selected items, are suggested to boost sales. In this challenge, we aim to propose complementary products based on the items in customer carts as detailed in scenarios H3 to H7. For example, consider product D. It is complemented by product A, as evidenced by data from 5 invoices containing D, where 3 also included A, while only 2 included either B or C. Similarly, if a customer’s cart contains both A and C, the complementary product would be B, as all 3 invoices featuring both A and C also include B
📌 Challenge Details and Links
Challenge Number: 40
Challenge Difficulty: ⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Cross Selling! with Power Query
Power Query solution 1 for Cross Selling!, proposed by Omid Motamedisedeh:
let
Data = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Scenarios = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Group = Table.Group(Data, {"Invoice ID"}, {{"Count", each _[Product]}}),
z = (a, x) =>
Table.RowCount(
Table.SelectRows(Group, each List.ContainsAll(_[Count], Text.Split(a & "," & x, ",")))
),
FX = (a) =>
List.Accumulate(
List.Distinct(Data[Product]),
"",
(x, y) => if Text.Contains(a, y) then x else if z(a, x) > z(a, y) then x else y
),
Result = Table.AddColumn(Scenarios, "Custom", each FX([Senarios]))
in
ResultPower Query solution 2 for Cross Selling!, proposed by Brian Julius:
let
Source = Table.RemoveColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Date", "Customer ID"}
),
Gp = Table.Group(
Source,
{"Invoice ID"},
{{"CS", each _, type table [Invoice ID = text, Product = text, Quantity = number]}}
),
Join = Table.RemoveColumns(Table.Join(Gp, "Invoice ID", Source, "Invoice ID"), "Quantity"),
Expand = Table.ExpandTableColumn(
Join,
"CS",
{"Product", "Quantity"},
{"CS.Product", "CS.Quantity"}
),
CrossSell = Table.SelectRows(
Table.Group(Expand, {"Product", "CS.Product"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
each [Product] <> [CS.Product]
),
AddSList = Table.AddColumn(
Table.RemoveFirstN(Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 1),
"SList",
each Text.Split([Senarios], ",")
),
Crossjoin = Table.AddColumn(
AddSList,
"CompProds",
each [
a = CrossSell,
b = [SList],
c = Table.SelectRows(
a,
each List.ContainsAny({[Product]}, b) and not List.ContainsAny({[CS.Product]}, b)
),
d = Table.SelectRows(c, each [Count] = List.Max(c[Count]))
][d][CS.Product]
),
Ex = Table.RemoveColumns(
Table.TransformColumns(
Crossjoin,
{"CompProds", each Text.Combine(List.Transform(_, Text.From), ","), type text}
),
"SList"
)
in
ExPower Query solution 3 for Cross Selling!, proposed by Aditya Kumar Darak 🇮🇳:
let
Data = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Scenarios = Excel.CurrentWorkbook(){[Name = "scenarios"]}[Content],
Products = List.Distinct(Data[Product]),
Group = Table.Group(Data, "Invoice ID", {"P", each [Product]}),
Return = Table.AddColumn(
Scenarios,
"Complementary",
each [
S = Text.Split([Scenarios], ","),
OP = List.Difference(Products, S),
C = List.Transform(
OP,
(f) => {f} & {List.Count(List.Select(Group[P], (x) => List.ContainsAll(x, S & {f})))}
),
R = List.Max(C, null, (f) => f{1}){0}
][R]
)
in
ReturnPower Query solution 4 for Cross Selling!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Senario = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Prod = Table.Group(Source, {"Product"}, {{"All", each [Invoice ID]}}),
Lista = Table.FromColumns({Senario[Senarios], List.Transform(List.Transform(Table.AddColumn(Senario, "A", each Text.Split([Senarios], ","))[A], each List.Transform(_, (x)=> Table.SelectRows(Prod, (y)=> y[Product]=x )[All])), each List.Sort(List.Distinct(List.Combine(List.Combine(_)))))}, {"Costumers Cart", "C"}),
Z = Table.AddColumn(Lista, "X", (y)=> let
a = Table.Combine(List.Transform(y[C], (x)=> Table.SelectRows(Source, each [Invoice ID]=x))),
b = List.Combine(Table.Group(a, {"Invoice ID"}, {"A", each let
c = if Text.Length(y[Costumers Cart])=1 then [Product] else
if List.ContainsAll([Product], Text.Split(y[Costumers Cart], ",")) then [Product] else {}
in c})[A])
in b),
Sol = Table.AddColumn(Z, "Complementary Products", each
let a = List.Accumulate(Text.Split([Costumers Cart],","), [X], (s,c)=> List.Select(s, each _<>c)),
b = Table.Sort(Table.FromRows(List.Transform(List.Distinct(a), each {_, List.Count(List.Select(a, (x)=> x=_))})), {"Column2",1})[Column1]{0}
in b)[[Complementary Products]]
in
SolPower Query solution 5 for Cross Selling!, proposed by Alexis Olson:
let
InvoiceProducts = Table.Buffer(Table.Group(Table1, {"Invoice ID"}, {{"Product", each [Product]}})),
Scenarios = Table.FromRows({{"A"}, {"B"}, {"D"}, {"A,B"}, {"A,C"}}, type table [Cart = text]),
FindInvoices = Table.AddColumn(
Scenarios,
"Invoices",
(row) =>
[
Products = Text.Split(row[Cart], ","),
TableRows = Table.SelectRows(
InvoiceProducts,
each List.Difference(Products, [Product]) = {}
),
Invoices = TableRows[Invoice ID]
][Invoices]
),
ExpandToRows = Table.ExpandListColumn(FindInvoices, "Invoices"),
Merge = Table.NestedJoin(
ExpandToRows,
{"Invoices"},
Table1,
{"Invoice ID"},
"Table1",
JoinKind.LeftOuter
),
Group = Table.Group(
Merge,
{"Cart"},
{{"Full Carts", each Table.Combine([Table1])[Product], type table}}
),
Result = Table.AddColumn(
Group,
"Complement",
each [
CartProducts = Text.Split([Cart], ","),
OtherProducts = List.RemoveItems([Full Carts], CartProducts),
Complement = List.Mode(OtherProducts)
][Complement]
)
in
ResultSolving the challenge of Cross Selling! with Excel
Excel solution 1 for Cross Selling!, proposed by Bo Rydobon 🇹🇭:
=LET(
x,
DROP(
GROUPBY(
C3:C26,
E3:E26,
ARRAYTOTEXT,
,
0
),
,
1
),
s,
H3:H7,
HSTACK(
s,
MAP(
s,
LAMBDA(
c,
LET(
d,
TEXTSPLIT(
ARRAYTOTEXT(
FILTER(
x,
1-BYROW(
ISERR(
FIND(
TEXTSPLIT(
c,
","
),
x
)
),
OR
)
)
),
,
", "
),
@GROUPBY(
d,
d,
ROWS,
0,
0,
-2,
ISERR(
FIND(
d,
c
)
)
)
)
)
)
)
)Excel solution 2 for Cross Selling!, proposed by محمد حلمي:
=MAP(H3:H7,
LAMBDA(v,
LET(e,
E3:E26,
y,
C3:C26,
x,
UNIQUE(
e
),j,
TEXTSPLIT(
v,
","
),
@SORT(FILTER(HSTACK(x,
MAP(x,
LAMBDA(a,
SUM(-(a=
REDUCE(
0,
FILTER(
y,
BYROW(
e=j,
LAMBDA(
a,
AND(
a
)
)
)
),
LAMBDA(
a,
c, VSTACK(
a,
FILTER(
e,
y=c
)
)
)
)))))),
ISNA(
XMATCH(
x,
j
)
)),
2))))Excel solution 3 for Cross Selling!, proposed by Oscar Mendez Roca Farell:
=HSTACK(
H3:H7,
MAP(
H3:H7,
LAMBDA(
a,
LET(
C,
C3:C26,
E,
E3:E26,
U,
UNIQUE(
E
),
A,
TEXTSPLIT(
a,
","
),
T,
COUNTA(
A
),
R,
DROP(
REDUCE(
"",
UNIQUE(
C
),
LAMBDA(
i,
x,
LET(
F,
FILTER(
E,
C=x
),
IF(
COUNT(
XMATCH(
A,
F
)
)=T,
VSTACK(
i,
F,
TOCOL(
A
)
),
i
)
)
)
),
1
),
B,
BYROW(
N(
TOROW(
R
)=U
),
LAMBDA(
r,
SUM(
r
)
)
),
INDEX(
SORTBY(
U,
-B
),
T+1
)
)
)
)
)Excel solution 4 for Cross Selling!, proposed by Julian Poeltl:
=LET(
Sz,
H3:H7,
R,
MAP(
Sz,
LAMBDA(
C,
LET(
T,
B3:F26,
I,
CHOOSECOLS(
T,
2
),
P,
CHOOSECOLS(
T,
4
),
UP,
UNIQUE(
P
),
Ord,
MAP(
UNIQUE(
I
),
LAMBDA(
A,
TEXTJOIN(
",",
,
FILTER(
P,
I=A
)
)
)
),
CC,
SUBSTITUTE(
C,
",",
"*"
),
F,
TAKE(
TEXTSPLIT(
CC,
"*"
),
,
1
),
S,
TAKE(
TEXTSLIT(
CC,
"*"
),
,
-1
),
FC,
TEXTJOIN(
",",
,
SUBSTITUTE(
SUBSTITUTE(
FILTER(
Ord,
ISNUMBER(
SEARCH(
CC,
Ord
)
)
),
F,
""
),
S,
""
)
),
SS,
MAP(
UP,
LAMBDA(
A,
LEN(
FC
)-LEN(
SUBSTITUTE(
FC,
A,
""
)
)
)
),
INDEX(
UP,
XMATCH(
MAX(
SS
),
SS
)
)
)
)
),
VSTACK(
HSTACK(
"Customers' Cart",
"Complementary Products"
),
HSTACK(
Sz,
R
)
)
)Excel solution 5 for Cross Selling!, proposed by Julian Poeltl:
=MAP(
H3:H7,
LAMBDA(
C,
LET(
I,
C3:C26,
P,
E3:E26,
UP,
UNIQUE(
P
),
Ord,
MAP(
UNIQUE(
I
),
LAMBDA(
A,
TEXTJOIN(
",",
,
FILTER(
P,
I=A
)
)
)
),
CC,
SUBSTITUTE(
C,
",",
"*"
),
SP,
TEXTSPLIT(
CC,
"*"
),
F,
TAKE(
SP,
,
1
),
S,
TAKE(
SP,
,
-1
),
FC,
TEXTJOIN(
",",
,
SUBSTITUTE(
SUBSTITUTE(
FILTER(
Ord,
ISNUMBER(
SEARCH(
CC,
Ord
)
)
),
F,
""
),
S,
""
)
),
SS,
MAP(
UP,
LAMBDA(
A,
LEN(
FC
)-LEN(
SUBSTITUTE(
FC,
A,
""
)
)
)
),
INDEX(
UP,
XMATCH(
MAX(
SS
),
SS
)
)
)
)
)Excel solution 6 for Cross Selling!, proposed by Kris Jaganah:
=HSTACK(H3:H7,
MAP(LEFT(
H3:H7
),
RIGHT(
H3:H7
),
LAMBDA(y,
z,
LET(a,
C3:C26,
b,
E3:E26,
c,
TOCOL(
XLOOKUP(
FILTER(
a,
b=y
)&z,
a&b,
a
),
3
),
d,
MAP(c,
LAMBDA(x,
ARRAYTOTEXT(FILTER(b,
(a=x)*((b<>y)*(b<>z)),
"")))),
e,
LEN(
SUBSTITUTE(
d,
", ",
""
)
),
f,
TOCOL(
1/TEXTSPLIT(
ARRAYTOTEXT(
REPT(
e&", ",
e
)
),
,
","
),
3
),
g,
TEXTSPLIT(
ARRAYTOTEXT(
d
),
,
", ",
1
),
h,
UNIQUE(
g
),
i,
MAP(h,
LAMBDA(v,
SUM((g=v)*f))),
TAKE(
FILTER(
h,
i=MAX(
i
)
),
1
)))))Excel solution 7 for Cross Selling!, proposed by John Jairo Vergara Domínguez:
=HSTACK(
H3:H7,
MAP(
H3:H7,
LAMBDA(
x,
LET(
p,
E3:E26,
g,
DROP(
GROUPBY(
C3:C26,
p,
CONCAT,
,
0
),
,
1
),
d,
TEXTSPLIT(
x,
","
),
z,
UNIQUE(
HSTACK(
d,
TOROW(
UNIQUE(
p
)
)
),
1,
1
),
@SORTBY(
z,
-MAP(
z,
LAMBDA(
x,
COUNT(
FIND(
x,
FILTER(
g,
1-ISERR(
BYROW(
FIND(
d,
g
),
OR
)
)
)
)
)
)
)
)
)
)
)
)Excel solution 8 for Cross Selling!, proposed by Sunny Baggu:
=HSTACK( K3:K7, MAP( K3:K7, LAMBDA(
y,
LET(
_ui,
UNIQUE(
C3:C26
),
_p,
TOROW(
UNIQUE(
E3:E26
)
),
arr,
DROP(
REDUCE(
"",
_ui,
LAMBDA(
a,
v,
VSTACK(
a,
BYCOL(
N(
TOCOL(
IFS(
C3:C26 = v,
E3:E26
),
3
) = _p
),
LAMBDA(
a,
SUM(
a
)
)
)
)
)
),
1
),
_tsp,
TEXTSPLIT(
y,
,
",",
1
),
_c1,
_p = _tsp,
_c2,
_p <> _tsp,
_a,
FILTER(
_p,
BYCOL(
_c2,
LAMBDA(
a,
AND(
a
)
)
)
),
_b,
FILTER(
BYCOL(
FILTER(
arr,
BYROW(
FILTER(
arr,
BYCOL(
_c1,
LAMBDA(
a,
OR(
a
)
)
)
),
LAMBDA(
a,
AND(
a = 1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
),
BYCOL(
_c2,
LAMBDA(
a,
AND(
a
)
)
)
),
XLOOKUP(
MAX(
_b
),
_b,
_a
)
)
) ))Excel solution 9 for Cross Selling!, proposed by Hussein SATOUR:
=MAP(
J3:J7,
LAMBDA(
y,
LET(
a,
C3:C26,
b,
E3:E26,
c,
TEXTSPLIT(
y,
","
),
e,
UNIQUE(
b
),
f,
FILTER(
e,
ISNA(
XMATCH(
e,
c
)
)
),
g,
LEN(
SUBSTITUTE(
ARRAYTOTEXT(
MAP(
DROP(
REDUCE(
"",
c,
LAMBDA(
x,
y,
VSTACK(
x,
FILTER(
a,
b=y
)
)
)
),
1
),
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
b,
a=x
)
)
)
)
),
f,
""
)
),
INDEX(
SORTBY(
f,
g
),
1
)
)
)
)
Excel solution 10 for Cross Selling!, proposed by Nicolas Micot:
=UNIQUE(
E3:E26
)
S3: products left (not included in the senario) -> =REDUCE(
R3#;
FRACTIONNER.TEXTE(
Q3;
;
","
);
LAMBDA(
l_value;
l_achat;
FILTRE(
l_value;
l_value<>l_achat
)
)
)
AA3: Invoices -> =UNIQUE(
C3:C26
)
AB3: Products bought with the invoice -> =MAP(
AA3#;
LAMBDA(
l_invoice;
JOINDRE.TEXTE(
",";
VRAI;
TRIER(
FILTRE(
$E$3:$E$26;
$C$3:$C$26=l_invoice
)
)
)
)
)
X3: Invoices left (those with the products in the senario) -> =FILTRE(
AA3#;
MAP(
AB3#;
LAMBDA(
l_products_bought;
SIERREUR(
CHERCHE(
SUBSTITU