Determine how frequently products are bought together (same invoice number). For instance, since products B and C are bought together solely under invoice number IN-001, the highlighter cell displays a count of 1.
📌 Challenge Details and Links
Challenge Number: 5
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Analyze Customer Purchasing Patterns! with Power Query
Power Query solution 2 for Analyze Customer Purchasing Patterns!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
C = Table.TransformColumnTypes(Source,{{"Invoice Num", type text}, {"Product", type text}, {"Quantity", Int64.Type}}),
G = Table.Group(C, {"Invoice Num"}, {{"TBL", each _, type table [Invoice Num=nullable text, Product=nullable text, Quantity=nullable number]}}),
MF=(TBL1)=>
let
In = Table.AddIndexColumn(TBL1, "In", 1, 1, Int64.Type),
I1 = Table.AddColumn(In, "L", each List.Skip(TBL1[Product],[In])),
I2 = Table.SelectColumns(I1,{"Product", "L"}),
I3 = Table.ExpandListColumn(I2, "L"),
I4 = Table.AddColumn(I3, "F", each Text.Combine({[Product], [L]}, "-"), type text),
I5 = Table.SelectColumns(I4,{"F"})
in
I5,
IN = Table.AddColumn(G, "MF", each MF([TBL])),
R = Table.SelectColumns(IN,{"MF"}),
E = Table.ExpandTableColumn(R, "MF", {"F"}, {"F"}),
GR = Table.Group(E, {"F"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
art2:
S = Table.SplitColumn(GR, "F", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"F.1", "F.2"}),
C2 = Table.TransformColumnTypes(S,{{"F.1", type text}, {"F.2", type text}}),
F = Table.SelectRows(C2, each ([F.2] <> null)),
LP = List.Distinct(C[Product]),
F2 = Table.FromList(LP, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
C3 = Table.NestedJoin(F2,{"Column1"},F,{"F.1"},"N",JoinKind.LeftOuter),
E1 = Table.ExpandTableColumn(C3, "N", {"F.2", "Count"}, {"F.2", "Count"}),
T1 = Table.RenameColumns(E1,{{"Column1", "C1"}, {"F.2", "C2"}}),
Re = Table.ReorderColumns(T1,{"C2", "C1", "Count"}),
T2 = Table.RenameColumns(Re,{{"C1", "C2"}, {"C2", "C1"}}),
C4 = Table.Combine({T1,T2}),
F1 = Table.SelectRows(C4, each ([Count] <> null)),
P = Table.Pivot(F1, LP, "C2", "Count", List.Sum)
in
P
Power Query solution 3 for Analyze Customer Purchasing Patterns!, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(Source, {"Invoice Num"}, {{"All", each [Product]}})[All],
Prod = List.Distinct(Source[Product]),
Transform = List.Select(
List.TransformMany(Prod, (a) => Prod, (a, b) => {a} & {b}),
each List.IsDistinct(_)
),
Tbl = Table.FromRows(
List.Transform(
Transform,
(x) => x & {List.Count(List.Select(Grouped, (s) => List.ContainsAll(s, x)))}
)
),
Pivot = Table.Pivot(Tbl, Prod, "Column1", "Column3")
in
PivotSolving the challenge of Analyze Customer Purchasing Patterns! with Excel
Excel solution 1 for Analyze Customer Purchasing Patterns!, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
B3:B21,
b,
C3:C21,
u,
UNIQUE(
b
),
MAKEARRAY(
4,
4,
LAMBDA(
i,
j,
IF(
i=j,
"",
COUNT(
XMATCH(
FILTER(
a,
b=INDEX(
u,
i
)
),
FILTER(
a,
b=INDEX(
u,
j
)
)
)
)
)
)
)
)Excel solution 2 for Analyze Customer Purchasing Patterns!, proposed by Bo Rydobon 🇹🇭:
=LET(
c,
C3:C21,
g,
GROUPBY(
B3:B21,
C3:C21&" ",
CONCAT,
0,
0
),
u,
UNIQUE(
c
),
n,
ROWS(
u
),
m,
MAKEARRAY(
n,
n,
LAMBDA(
i,
j,
COUNT(
SEARCH(
INDEX(
u,
i
)&" *"&INDEX(
u,
j
)&" ",
DROP(
g,
,
1
)
)
)
)
), VSTACK(
HSTACK(
"",
TOROW(
u
)
),
HSTACK(
u,
m+TRANSPOSE(
m
)
)
)
)Excel solution 3 for Analyze Customer Purchasing Patterns!, proposed by محمد حلمي:
=MAP(
I2:L2&H3:H6,
LAMBDA(
a,
LET(
i,
LEFT(
a
),
r,
RIGHT(
a
),
b,
B3:B21,
e,
COUNTIFS(
C3:C21,
VSTACK(
i,
r
),
b,
TOROW(
UNIQUE(
b
)
)
),
IF(
i=r,
"",
SUM(
TAKE(
e,
1
)*DROP(
e,
1
)
)
)
)
)
)Excel solution 4 for Analyze Customer Purchasing Patterns!, proposed by محمد حلمي:
=IF(
H3:H6=I2:L2,
"",
DROP(
REDUCE(
0,
H3:H6,
LAMBDA(
q,
w,
VSTACK(
q,
BYCOL(
I2:L2,
LAMBDA(
x,
REDUCE(
0,
UNIQUE(
B3:B21
),
LAMBDA(
a,
d,
a+IFNA(
AND(
XMATCH(
VSTACK(
w,
x
),
FILTER(
C3:C21,
B3:B21=d
)
)
),
0
)
)
)
)
)
)
)
),
1
)
)Excel solution 5 for Analyze Customer Purchasing Patterns!, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
vn,
B3:B21,
u,
UNIQUE(
vn
),
p,
C3:C21,
q,
UNIQUE(
p
),
r,
TOCOL(
q&TOROW(
q
)
),
s,
REDUCE(
0,
u,
LAMBDA(
c,
v,
LET(
f,
FILTER(
p,
vn=v
),
g,
TOROW(
f
),
h,
TOCOL(
IF(
f<>g,
f&g,
1/0
),
3
),
c+MAP(
r,
LAMBDA(
a,
ISNUMBER(
XMATCH(
a,
h
)
)
)
)
)
)
),
WRAPCOLS(
IF(
s,
s,
""
),
4
)
)Excel solution 6 for Analyze Customer Purchasing Patterns!, proposed by Abdallah Ally:
=LET(
a,
B3:B21,
b,
C3:C21,
c,
SORT(
UNIQUE(
b
)
),
d,
COUNTA(
c
),
e,
MAKEARRAY(
d,
d,
LAMBDA(
x,
y,
IF(
x=y,
"",
LET(
u,
FILTER(
a,
b=CHOOSEROWS(
c,
x
)
),
v,
FILTER(
a,
b=CHOOSEROWS(
c,
y
)
),
w,
VSTACK(
u,
v
),
COUNTA(
UNIQUE(
w,
,
0
)
)-COUNTA(
UNIQUE(
w,
,
1
)
)
)
)
)
),
HSTACK(
VSTACK(
"",
c
),
VSTACK(
TOROW(
c
),
e
)
)
)Excel solution 7 for Analyze Customer Purchasing Patterns!, proposed by Kris Jaganah:
=LET(a,
B3:B21,
b,
C3:C21,
c,
D3:D21,
d,
UNIQUE(
b
),
e,
TOROW(
d
),
f,
TOCOL(
IF(
d=e,
x,
d&e
),
3
),
g,
MAP(f,
LAMBDA(v,
SUM(INT(BYCOL(DROP(IFNA(REDUCE("",
UNIQUE(
a
),
LAMBDA(x,
y,
HSTACK(x,
FILTER(b,
(a=y))))),
""),
,
1),
LAMBDA(x,
SUM((x=LEFT(
v
))+(x=RIGHT(
v
)))/2)))))),
PIVOTBY(
LEFT(
f
),
RIGHT(
f
),
g,
SUM,
0,
0,
,
0
))Excel solution 8 for Analyze Customer Purchasing Patterns!, proposed by Kris Jaganah:
=LET(a,
B3:B21,
b,
C3:C21,
c,
D3:D21,
d,
UNIQUE(
b
),
e,
TOROW(
d
),
VSTACK(HSTACK(
"",
e
),
HSTACK(d,
IFERROR(MAP(IF(
d=e,
z,
d&e
),
LAMBDA(v,
SUM(INT(BYCOL(DROP(IFNA(REDUCE("",
UNIQUE(
a
),
LAMBDA(x,
y,
HSTACK(x,
FILTER(b,
(a=y))))),
""),
,
1),
LAMBDA(x,
SUM((x=LEFT(
v
))+(x=RIGHT(
v
)))/2)))))),
""))))Excel solution 9 for Analyze Customer Purchasing Patterns!, proposed by John Jairo Vergara Domínguez:
=LET(
i,
B3:B21,
p,
C3:C21,
u,
UNIQUE(
p
),
n,
ROWS(
u
),
f,
LAMBDA(
v,
FILTER(
i,
p=INDEX(
u,
v
)
)
),
HSTACK(
VSTACK(
"",
u
),
VSTACK(
TOROW(
u
),
MAKEARRAY(
n,
n,
LAMBDA(
r,
c,
IF(
r=c,
"",
COUNT(
XMATCH(
f(
r
),
f(
c
)
)
)
)
)
)
)
)
)Excel solution 10 for Analyze Customer Purchasing Patterns!, proposed by Ankur Sharma:
=LET(
a,
$B$3:$B$21, b,
$C$3:$C$21, IF(
$H3 =I$2,
"",
COUNT(
IFERROR(
XMATCH(
FILTER(
a,
b = I$2
),
FILTER(
a,
b = $H3
),
0
),
""
)
)
)
)Excel solution 11 for Analyze Customer Purchasing Patterns!, proposed by Charles Roldan:
=LET(i,
B3:B21,
p,
C3:C21,
ui,
UNIQUE(
i
),
up,
UNIQUE(
p
),
REPT(REDUCE(0,
ui,
LAMBDA(a,
n,
a + LAMBDA(
x,
x * TOROW(
x
)
)(ISNUMBER(
XMATCH(
up,
FILTER(
p,
i = n
)
)
)))),
up <> TOROW(
up
)))Excel solution 12 for Analyze Customer Purchasing Patterns!, proposed by Mohammad Ashooryan:
=IF(
G$2=$F3,
"", SUMPRODUCT( COUNTIFS(
Purchase[[Product]:[Product]],
G$2,
Purchase[[Invoice Num]:[Invoice Num]],
FILTER(
Purchase[[Invoice Num]:[Invoice Num]],
Purchase[[Product]:[Product]]=$F3
)
)
)
)Excel solution 13 for Analyze Customer Purchasing Patterns!, proposed by Saikrishnaa R.:
=SUM(
COUNTIFS(
Data[Product],
$H3,
Data[Invoice Num],
TEXTSPLIT(
TEXTJOIN(
",",
TRUE,
IF(
Data[Product]=J$2,
Data[Invoice Num],
""
)
),
","
)
)
)Excel solution 14 for Analyze Customer Purchasing Patterns!, proposed by Saikrishnaa R.:
=SUM(
COUNTIFS(
Data[Product],
$H3,
Data[Invoice Num],
FILTER(
Data[Invoice Num],
Data[Product]=J$2
)
)
)