In the Question Table, historical sales data are provided. We aim to determine the number of times the product combinations listed in cells H3 to H7 were purchased together under the same Invoice ID. For example, products C and D were only purchased together twice, specifically in invoices IN-01 and IN-05.
📌 Challenge Details and Links
Challenge Number: 51
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Purchasing Together! with Power Query
Power Query solution 1 for Purchasing Together!, proposed by Brian Julius:
let
Source = Table.SelectColumns(
Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name = "rng"]}[Content]),
{"Invoice ID", "Product"}
),
Combos = Table.RemoveFirstN(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 1),
AddProdsList = Table.Group(Source, {"Invoice ID"}, {"ProdsList", each [Product]}),
Extract = Table.TransformColumns(
AddProdsList,
{"ProdsList", each Text.Combine(List.Transform(_, Text.From), ",")}
),
CrossJ = Table.ExpandListColumn(
Table.AddColumn(Combos, "ProdList", each Extract[ProdsList]),
"ProdList"
),
Group = Table.Group(CrossJ, {"Combinations"}, {{"All", each _}}),
AddCount = Table.AddColumn(
Group,
"Count",
each [
a = [All],
b = Text.Split(List.First(a[Combinations]), ","),
c = a[ProdList],
d = List.Transform(c, each if List.ContainsAll(Text.Split(_, ","), b) then 1 else 0),
e = List.Sum(d)
][e]
),
Clean = Table.RenameColumns(Table.RemoveColumns(AddCount, "All"), {"Combinations", "Products"})
in
CleanPower Query solution 2 for Purchasing Together!, proposed by Eric Laforce:
let
XLSource = (tName) => Excel.CurrentWorkbook(){[Name = tName]}[Content],
ListOfProducts = List.Buffer(
Table.Group(XLSource("Table1"), "Invoice ID", {"P", each [Product]})[P]
),
Add_Count = Table.AddColumn(
XLSource("Table2"),
"Count",
each
let
P = Text.Split([Column1], ",")
in
List.Count(List.Select(ListOfProducts, each List.ContainsAll(_, P)))
)
in
Add_CountPower Query solution 3 for Purchasing Together!, proposed by Ramiro Ayala Chávez:
let
t2 = Table.RenameColumns(Excel.CurrentWorkbook(){[Name="Table2"]}[Content],{"Combinations","C"}),
t1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = t1[[Invoice ID],[Product]],
b = Table.Group(a,{"Invoice ID"},{"G", each List.Sort([Product])}),
Fx = (x as list)=> let
c = x,
d = List.Transform({0..Number.Power(2,List.Count(c))-1},(i)=>List.Transform({0..List.Count(c)-1},
(j)=>if Number.Mod(Number.IntegerDivide(i, Number.Power(2,j)),2)=1 then c{j} else null)),
e = List.Skip(List.Transform(d, each List.RemoveNulls(_)))
in e,
f = Table.AddColumn(b,"Products", each Fx([G]))[[Invoice ID],[Products]],
g = Table.TransformColumns(f,{"Products", each List.Transform(_, each Text.Combine(_,","))}),
h = Table.ExpandListColumn(g,"Products"),
i = Table.SelectRows(h, each [Products]=t2[C]{0} or [Products]=t2[C]{1} or [Products]=t2[C]{2} or [Products]=t2[C]{3} or [Products]=t2[C]{4}),
j = Table.Group(i,{"Products"},{"Count", each Table.RowCount(_)}),
Sol = Table.Sort(j,{ each List.PositionOf(t2[C],[Products])})
in
SolPower Query solution 4 for Purchasing Together!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Combo = Excel.CurrentWorkbook(){[Name = "combo"]}[Content],
Group = Table.Group(Source, "Invoice ID", {"P", each [Product]}),
Return = Table.AddColumn(
Combo,
"Count",
each [
S = Text.Split([Combinations], ","),
F = Table.SelectRows(Group, (f) => List.ContainsAll(f[P], S)),
R = Table.RowCount(F)
][R]
)
in
ReturnPower Query solution 5 for Purchasing Together!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Comb = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Sol = Table.AddColumn(Comb, "Count", (x)=>
let
a = Source,
b = x[Combinations],
c = Table.Group(a, "Invoice ID", {"A", each List.ContainsAll([Product], Text.Split(b,","))}),
d = List.Count(Table.SelectRows(c, each [A])[A])
in d)
in
SolPower Query solution 6 for Purchasing Together!, proposed by Masoud Karami:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
Tb1 = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"1" = Excel.CurrentWorkbook(){[Name = "Combin"]}[Content],
#"2" = Table.PromoteHeaders(#"1", [PromoteAllScalars = true]),
#"3" = Table.SplitColumn(
#"2",
"Combinations",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"co1", "co2", "co3", "co4"}
),
#"4" = Table.AddIndexColumn(#"3", "Index", 0, 1, Int64.Type),
Tb2 = Table.UnpivotOtherColumns(#"4", {"Index"}, "Attribute", "Value"),
#"5" = Table.NestedJoin(Tb1, {"Product"}, Tb2, {"Value"}, "Combin", JoinKind.LeftOuter),
#"6" = Table.ExpandTableColumn(#"5", "Combin", {"Index"}, {"Index"}),
#"7" = Table.Group(
#"6",
{"Invoice ID", "Index"},
{{"Combine", each Text.Combine(List.Sort([Product]), ","), type text}}
),
#"8" = Table.Group(
#"7",
{"Invoice ID", "Combine"},
{{"Count_", each Table.RowCount(Table.Distinct(_)), Int64.Type}}
),
#"9" = Table.Group(#"8", {"Combine"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"10" = Table.SelectRows(
#"9",
each (
[Combine]
= "A,B" or [Combine]
= "A,B,C" or [Combine]
= "A,B,C,D" or [Combine]
= "A,C" or [Combine]
= "A,D"
)
)
in
#"10"Power Query solution 7 for Purchasing Together!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.Group(S, {"Invoice ID"}, {{"Tbl", each _, type table [Date=datetime, Invoice ID=text, Customer ID=text, Product=text, Quantity=number]}}),
WN=(NE)=>
let
A = Table.TransformColumnTypes(NE,{{"Date", type datetime}, {"Invoice ID", type text}, {"Customer ID", type text}, {"Product", type text}, {"Quantity", Int64.Type}}),
S=List.Transform({1 ..Number.Power(2,List.Count(List.Distinct(A[Product])))},each _ -1),
Z = Table.FromList(S, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
B = Table.RenameColumns(Z,{{"Column1", "Rep"}}),
C = Table.AddColumn(B, "C", each let
Loop = List.Generate(() =>[i = [Rep], j = Number.IntegerDivide(i, 2), k = Number.Mod(i, 2), l = Text.From(k)],each [i] > 0,each [
i = [j],
j = Number.IntegerDivide(i, 2),
k = Number.Mod(i, 2),
l = Text.From(k) & [l]
],
each [l]
),
y = try Loop{List.Count(Loop) - 1} otherwise "0",
z = Text.PadStart(y,List.Count(A[Product]), "0")
in
Power Query solution 8 for Purchasing Together!, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Combin = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Grouped = Table.Group(
Source,
{"Invoice ID"},
{
{
"Grouped",
each
let
a = _[Product],
b = List.Sort(a)
in
b
}
}
),
Added = Table.AddColumn(
Combin,
"Count",
each
let
a = Text.Split([Combinations], ","),
b = Table.RowCount(Table.SelectRows(Grouped, (g) => List.ContainsAll(g[Grouped], a)))
in
b
)
in
AddedPower Query solution 9 for Purchasing Together!, proposed by Thomas DUCROQUETZ:
let
Source = Combinations,
GetCount =
Table.AddColumn(
Source,
"Count", each let
currentCombination = Text.Split([Combinations],","),
groupedInvoices = Table.Group(Invoices,{"Invoice ID"},{{"products",each [Product],type {text}}}),
filteredProducts = Table.SelectRows(
groupedInvoices,
each let
products = [products]
in
List.ContainsAll( products , currentCombination )
)
in
Table.RowCount(filteredProducts),
Int64.Type
)
in
GetCount
Combinations and Invoices corresponds to your two given tables.Solving the challenge of Purchasing Together! with Excel
Excel solution 1 for Purchasing Together!, proposed by Bo Rydobon 🇹🇭:
=MAP(
H3:H7,
LAMBDA(
c,
SUM(
BYROW(
COUNTIFS(
E3:E26,
TEXTSPLIT(
c,
","
),
C3:C26,
UNIQUE(
C3:C26
)
),
MIN
)
)
)
)Excel solution 2 for Purchasing Together!, proposed by Bo Rydobon 🇹🇭:
=MAP(
H3:H7,
LAMBDA(
c,
SUM(
GROUPBY(
C3:C26,
E3:E26,
LAMBDA(
x,
--AND(
1-ISNA(
XMATCH(
TEXTSPLIT(
c,
","
),
x
)
)
)
),
,
0
)
)
)
)Excel solution 3 for Purchasing Together!, proposed by 🇰🇷 Taeyong Shin:
=LET(i,C3:C26,c,H3:H7,HSTACK(c,MAP(c,LAMBDA(x,LET(s,TEXTSPLIT(x,,","),SUM(N(BYROW(N(UNIQUE(i)=TOROW(REPT(i,TEXTSPLIT(E3:E26,s)=""))),SUM)=ROWS(s))))))))Excel solution 4 for Purchasing Together!, proposed by محمد حلمي:
=MAP(H3:H7,LAMBDA(x,SUM(BYROW(COUNTIFS(C3:C26,UNIQUE(C3:C26),E3:E26,TEXTSPLIT(x,",")),LAMBDA(a,PRODUCT(a))))))Excel solution 5 for Purchasing Together!, proposed by محمد حلمي:
=MAP(
H3:H7,
LAMBDA(
x,
SUM(
BYROW(
COUNTIFS(
C3:C26,
UNIQUE(
C3:C26
),
E3:E26,
TEXTSPLIT(
x,
","
)
),
PRODUCT
)
)
)
)Excel solution 6 for Purchasing Together!, proposed by محمد حلمي:
=MAP(
H3:H7,
LAMBDA(
x,
SUM(
--MAP(
UNIQUE(
C3:C26
),
LAMBDA(
a,
LEN(
x
)+1=2*COUNT(
FIND(
IF(
a=C3:C26,
E3:E26
),
x
)
)
)
)
)
)
)Excel solution 7 for Purchasing Together!, proposed by Aditya Kumar Darak 🇮🇳:
=LET( _d,
B3:F26, _cmb,
H3:H7, _inv,
CHOOSECOLS(
_d,
2
), _p,
CHOOSECOLS(
_d,
4
), _uinv,
UNIQUE(
_inv
), _allp,
MAP(
_uinv,
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
_p,
_inv = a
)
)
)
), _cnt,
MAP( _cmb, LAMBDA(
a,
COUNT(
SEARCH(
SUBSTITUTE(
a,
",",
"*"
),
_allp & ", " & _allp
)
)
) ), _r,
HSTACK(
_cmb,
_cnt
), _r)Excel solution 8 for Purchasing Together!, proposed by Julian Poeltl:
=LET(
T,
B3:F26,
D,
TAKE(
T,
,
1
),
P,
CHOOSECOLS(
T,
4
),
UP,
UNIQUE(
P
),
NP,
COUNTA(
UP
),
UD,
UNIQUE(
D
),
DS,
DEC2BIN(
SEQUENCE(
2^NP-1
),
NP
),
Comb,
MAP(
DS,
LAMBDA(
A,
TEXTJOIN(
",",
,
IF(
MID(
A,
SEQUENCE(
,
NP
),
1
)*SEQUENCE(
,
NP
)=0,
"",
INDEX(
UP,
MID(
A,
SEQUENCE(
,
NP
),
1
)*SEQUENCE(
,
NP
)
)
)
)
)
),
CP,
MAP(
UD,
LAMBDA(
A,
TEXTJOIN(
",",
,
FILTER(
P,
D=A
)
)
)
),
R,
MAP(
Comb,
LAMBDA(
C,
SUM(
--MAP(
CP,
LAMBDA(
B,
AND(
ISNUMBER(
SEARCH(
TEXTSPLIT(
C,
","
),
B
)
)
)
)
)
)
)
),
VSTACK(
HSTACK(
"Products",
"Count"
),
HSTACK(
Comb,
R
)
)
)Excel solution 9 for Purchasing Together!, proposed by Julian Poeltl:
=LET(
T,
B3:F26,
C,
H3:H7,
D,
TAKE(
T,
,
1
),
P,
CHOOSECOLS(
T,
4
),
UD,
UNIQUE(
D
),
CP,
MAP(
UD,
LAMBDA(
A,
TEXTJOIN(
",",
,
FILTER(
P,
D=A
)
)
)
),
R,
MAP(
C,
LAMBDA(
C,
SUM(
--MAP(
CP,
LAMBDA(
B,
AND(
ISNUMBER(
SEARCH(
TEXTSPLIT(
C,
","
),
B
)
)
)
)
)
)
)
),
VSTACK(
HSTACK(
"Products",
"Count"
),
HSTACK(
C,
R
)
)
)Excel solution 10 for Purchasing Together!, proposed by Kris Jaganah:
=REDUCE(
{"Products",
"Count"},
H3:H7,
LAMBDA(
v,
w,
VSTACK(
v,
LET(
a,
TEXTSPLIT(
w,
,
","
),
HSTACK(
w,
SUM(
N(
BYROW(
DROP(
PIVOTBY(
C3:C26,
E3:E26,
E3:E26,
CONCAT,
0,
0,
,
0
),
1,
1
),
LAMBDA(
x,
SUM(
IFERROR(
FIND(
a,
x
),
0
)
)
)
)=COUNTA(
a
)
)
)
)
)
)
)
)Excel solution 11 for Purchasing Together!, proposed by John Jairo Vergara Domínguez:
=MAP(
H3:H7,
LAMBDA(
x,
COUNT(
-BYROW(
FIND(
TEXTSPLIT(
x,
","
),
DROP(
GROUPBY(
C3:C26,
E3:E26,
CONCAT
),
1,
1
)
),
OR
)
)
)
)
✅=MAP(
H3:H7,
LAMBDA(
x,
SUM(
--BYROW(
COUNTIFS(
C3:C26,
UNIQUE(
C3:C26
),
E3:E26,
TEXTSPLIT(
x,
","
)
),
AND
)
)
)
)Excel solution 12 for Purchasing Together!, proposed by Sunny Baggu:
=MAP(
H3:H7, LAMBDA(b, SUM(
MAP(
UNIQUE(
C3:C26
), LAMBDA(x, MIN(BYCOL((E3:E26 = TEXTSPLIT(
b,
","
)) * (C3:C26 = x),
LAMBDA(
a,
SUM(
a
)
)))
)
)
)
)
)Excel solution 13 for Purchasing Together!, proposed by Hussein SATOUR:
=LET(
a,
GROUPBY(
B3:B26,
D3:D26,
SUM,
,
0
),
FILTER(
DROP(
INDEX(
a,
,
1
),
1
),
DROP(
INDEX(
a,
,
2
),
1
)>DROP(
INDEX(
a,
,
2
),
-1
)
)
)Excel solution 14 for Purchasing Together!, proposed by Nicolas Micot:
=LET(_Invoices;
UNIQUE(
$C$3:$C$26
);_products;
FRACTIONNER.TEXTE(
J3;
","
);_hasProduct;
NB.SI.ENS(
$C$3:$C$26;
_Invoices;
$E$3:$E$26;
_products
);SOMME(BYROW(_hasProduct;
LAMBDA(l_row;
--(PRODUIT(
l_row
)=1)))))Excel solution 15 for Purchasing Together!, proposed by Rayan Saud:
=LET(
I,
C3:C26,
p,
E3:E26,
comb,
H3:H7,
c,
MAP(
UNIQUE(
I
),
LAMBDA(
x,
CONCAT(
FILTER(
p,
I=x
)
)
)
),
HSTACK(
comb,
MAP(
comb,
LAMBDA(
a,
SUM(
MAP(
c,
LAMBDA(
x,
IFERROR(
IF(
SUM(
FIND(
TEXTSPLIT(
a,
","
),
x,
1
)
)>0,
1
),
0
)
)
)
)
)
)
)
)Solving the challenge of Purchasing Together! with Python
Python solution 1 for Purchasing Together!, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("CH-051 Purchasing together.xlsx", sheet_name="Sheet1", usecols="B:F", skiprows=1)
test = pd.read_excel("CH-051 Purchasing together.xlsx", sheet_name="Sheet1", usecols="J:K", skiprows=1, nrows = 5)
test = test.sort_values(by = "Products").reset_index(drop = True)
result = input[['Invoice ID', 'Product', 'Quantity']].pivot_table(index='Invoice ID', columns='Product', values='Quantity', fill_value=0)
result['A,B'] = (result['A'] > 0) & (result['B'] > 0)
result['A,C'] = (result['A'] > 0) & (result['C'] > 0)
result['C,D'] = (result['C'] > 0) & (result['D'] > 0)
result['A,B,C'] = (result['A'] > 0) & (result['B'] > 0) & (result['C'] > 0)
result['A,B,C,D'] = (result['A'] > 0) & (result['B'] > 0) & (result['C'] > 0) & (result['D'] > 0)
result = result[['A,B', 'A,C', 'C,D', 'A,B,C', 'A,B,C,D']].reset_index()
result.columns.name = None
result = result.melt(id_vars='Invoice ID', var_name='Products', value_name='Purchased Together')
result = result[result['Purchased Together'] == True]
result = result.groupby('Products').size().reset_index(name='Count')
print(test.equals(result)) # TruePython solution 2 for Purchasing Together!, proposed by Luke Jarych:
xlwings import)
import pandas as pd
import xlwings as xw
from itertools import combinations
hashtag
#import workbook range as DataFrame
wb = xw.Book(r'CH-051 Purchasing together - find all combinations within list.xlsx')
sh = wb.sheets[0]
table = sh.tables['Table1']
rng = sh.range(table.range.address)
df_source = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
table = sh.tables['Table2']
rng = sh.range(table.range.address)
df_combinations = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
df_source = df_source.groupby('Invoice ID')
comb_dict = {}
for combo in df_combinations['Combinations'].str.split(','):
combo_str = ','.join(sorted(combo))
comb_dict[combo_str] = 0
for name, group in df_source:
products = group['Product'].tolist()
for comb_dict_key in comb_dict.keys():
comb = comb_dict_key.split(',')
if set(comb).issubset(set(products)):
comb_dict[comb_dict_key] += 1
comb_dictSolving the challenge of Purchasing Together! with R
R solution 1 for Purchasing Together!, proposed by Konrad Gryczan, PhD:
library(UpSetR)
library(tidyverse)
library(readxl)
input = read_excel("files/CH-051 Purchasing together.xlsx", range = "B2:F26")
invoices = input %>%
select(`Invoice ID`, Product) %>%
mutate(exist = 1) %>%
pivot_wider(names_from = Product, values_from = exist, values_fill = list(exist = 0)) %>%
as_tibble() %>%
as.data.frame()
upset(invoices, keep.order = T)R solution 2 for Purchasing Together!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("files/CH-051 Purchasing together.xlsx", range = "B2:F26")
test= read_excel("files/CH-051 Purchasing together.xlsx", range = "J2:K7")
result = input %>%
select(`Invoice ID`, Product, Quantity) %>%
pivot_wider(names_from = Product, values_from = Quantity, values_fill = list(Quantity = 0)) %>%
mutate(`A,B` = ifelse(A>0 & B>0, T, F),
`A,C` = ifelse(A>0 & C>0, T, F),
`C,D` = ifelse(C>0 & D>0, T, F),
`A,B,C` = ifelse(A>0 & B>0 & C>0, T, F),
`A,B,C,D` = ifelse(A>0 & B>0 & C>0 & D>0, T, F)) %>%
select(`Invoice ID`, `A,B`, `A,C`, `C,D`, `A,B,C`, `A,B,C,D`) %>%
pivot_longer(cols = -`Invoice ID`, names_to = "Products", values_to = "Purchased") %>%
filter(Purchased == T) %>%
summarise(Count = n()