Find the total amounts paid by all Stockists for the items purchased by them. If an item appears against many suppliers, then it will be equally divided. Ex. A appears against all 3 stockists and total amount is Unit Price * Quantity = 12*10 = 120. So, equal division will 120/3 = 40 against all 3 suppliers.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 195
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Shared Item Billing Per Supplier with Power Query
Power Query solution 1 for Shared Item Billing Per Supplier, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T1 = Source("Items"),
T2 = Table.TransformColumns(Source("Stockists"), {"Items", each Text.Split(_, ", ")}),
S = Table.RemoveColumns(
Table.AddColumn(
T2,
"Amount Paid",
each List.Sum(
List.Transform(
[Items],
(m) => (
List.Select(
List.TransformMany(
Table.ToRows(
Table.ReplaceValue(
T1,
";",
"&",
(x, y, z) =>
let
r = (a, b, c) => Text.Replace(Text.From(a), b, c)
in
Text.Split(r(r(r(x, " ", ""), ";", ","), "&", ","), ","),
Table.ColumnNames(T1)
)
),
List.Zip,
(i, _) => {_{0}, Expression.Evaluate(_{1} & "*" & _{2})}
),
each _{0} = m
){0}?{1}?
?? 0
)
/ List.Count(List.PositionOf(List.Combine(T2[Items]), m, 2))
)
)
),
"Items"
)
in
SPower Query solution 2 for Shared Item Billing Per Supplier, proposed by Zoran Milokanović:
Power Query solution 3 for Shared Item Billing Per Supplier, proposed by Kris Jaganah:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Unpv = Table.UnpivotOtherColumns(T1, {}, "A", "V"),
Sp = Table.ExpandListColumn(
Table.TransformColumns(
Unpv,
{
"V",
each
let
a = Text.From(_),
b = Splitter.SplitTextByAnyDelimiter({", ", "; ", "& ", ","})(a)
in
List.Transform(b, Text.Trim)
}
),
"V"
),
St = Table.Sort(Sp, {"A", 0}),
Id = Table.AddIndexColumn(St, "Ide", 1, 1),
Idx = Table.TransformColumns(
Id,
{
"Ide",
each
let
a = List.Count(St[A]) / List.Count(List.Distinct(St[A])),
b = Number.Mod(_, a)
in
if b = 0 then a else b
}
),
Piv = Table.Pivot(Idx, List.Distinct(Idx[A]), "A", "V"),
Occ = Table.AddColumn(
Piv,
"Occ",
each
let
a = [Items],
b = List.Count(
List.Select(List.Combine(List.Transform(T2[Items], each Text.Split(_, ", "))), each _ = a)
),
c = if b = 0 then null else 1 / b * (Number.From([Quantity]) * Number.From([Unit Price]))
in
c
),
ST2 = Table.ExpandListColumn(
Table.TransformColumns(T2, {"Items", Splitter.SplitTextByDelimiter(", ")}),
"Items"
),
Sol = Table.AddColumn(
ST2,
"Ans",
each List.Sum(Table.SelectRows(Occ, (x) => x[Items] = [Items])[Occ])
),
Grp = Table.Group(Sol, {"Stockist"}, {"Amount Paid", each List.Sum([Ans])})
in
GrpPower Query solution 4 for Shared Item Billing Per Supplier, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Trans = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {_, each List.Select(Text.SplitAny(Text.From(_), ", ;&"), each _ <> "")}
)
),
Tbl = Table.AddColumn(
Table.Combine(
Table.AddColumn(
Trans,
"A",
each Table.FromRows(List.Zip(Record.ToList(_)), Table.ColumnNames(Source))
)[A]
),
"Total",
each Number.From([Unit Price]) * Number.From([Quantity])
),
Tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Items = Table.FromColumns({List.Combine(List.Transform(Tbl2[Items], each Text.Split(_, ", ")))}),
Group = Table.Group(Items, {"Column1"}, {{"Count", each Table.RowCount(_)}}),
Calc = Table.AddColumn(
Group,
"B",
(x) => Table.SelectRows(Tbl, each [Items] = x[Column1])[Total]{0}? / x[Count]
),
Sol = Table.RemoveColumns(
Table.AddColumn(
Tbl2,
"Amount Paid",
(x) =>
let
a = Calc,
b = Text.Split(x[Items], ", "),
c = List.Transform(b, each Table.SelectRows(a, (y) => y[Column1] = _)[B]{0})
in
List.Sum(c)
),
{"Items"}
)
in
SolPower Query solution 5 for Shared Item Billing Per Supplier, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(
Fonte,
"tab",
each
let
a = Record.FieldValues(_),
b = Text.Combine(
List.Distinct(
List.TransformMany(
a,
(x) => Text.ToList(Text.Remove(Text.From(x), {"0" .. "9", "A" .. "Z", " "})),
(a, b) => b
)
)
),
c = List.Transform(a, (x) => Text.SplitAny(Text.From(x), b)),
d = Table.FromRows(
List.Transform(
{0 .. List.Count(List.Transform(c{0}, each _)) - 1},
(x) => List.Transform(c, (w) => w{x})
)
)
in
d
)[tab],
tab = Table.TransformColumns(Table.Combine(add), {}, Text.Trim),
mlt = Table.AddColumn(
tab,
"mult",
each List.Product(List.Transform(List.RemoveFirstN(Record.FieldValues(_)), Number.From))
),
t2 =
let
b = Table.TransformColumns(Tabela2, {"Items", each Text.Split(_, ",")}),
c = Table.TransformColumns(Table.ExpandListColumn(b, "Items"), {}, Text.Trim),
d = Table.Group(
c,
{"Items"},
{{"count", each Table.RowCount(_)}, {"Stockist", each _[Stockist]}}
),
e = Table.ExpandListColumn(d, "Stockist")
in
e,
cnt = Table.Join(t2, {"Items"}, mlt, {"Column1"})[[Stockist], [Items], [mult], [count]],
div = Table.AddColumn(cnt, "div", each [mult] / [count]),
res = Table.Group(div, {"Stockist"}, {{"Amount Paid", each List.Sum([div]), type number}})
in
resPower Query solution 6 for Shared Item Billing Per Supplier, proposed by Ramiro Ayala Chávez:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
TA = Table.AddColumn,
TT = Table.TransformColumnTypes,
TC = Table.TransformColumns,
a = TT(t1, {{"Items", type text}, {"Unit Price", type text}, {"Quantity", type text}}),
b = TC(a, {}, each Text.SplitAny(_, ",;&")),
c = TA(b, "T", each Table.FromColumns({[Items], [Unit Price], [Quantity]}))[[T]],
d = TC(Table.Combine(c[T]), {}, each Text.Trim(_)),
e = TT(d, {{"Column2", Int64.Type}, {"Column3", Int64.Type}}),
f = TA(e, "P", each [Column2] * [Column3])[[Column1], [P]],
g = TC(t2, {"Items", each Text.Split(_, ", ")}),
h = TA(
Table.ExpandListColumn(g, "Items"),
"P",
each try f[P]{List.PositionOf(f[Column1], [Items])} otherwise 0
),
i = Table.Group(h, {"Items"}, {"G", each Table.RowCount(_)}),
j = TA(h, "Q", each i[G]{List.PositionOf(i[Items], [Items])}),
k = TA(j, "R", each [P] / [Q])[[Stockist], [Items], [R]],
Sol = Table.Group(k, {"Stockist"}, {"Amount Paid", each List.Sum([R])})
in
SolPower Query solution 7 for Shared Item Billing Per Supplier, proposed by Rafael González B.:
let
Items_T = let
A = Excel.CurrentWorkbook(){0}[Content],
TC = Table.TransformColumnTypes(A,{{"Items", type text}, {"Unit Price", type text}, {"Quantity", type text}}),
L = {", "," & ","; ", ","},
B = List.Accumulate(
{0..3},
TC,
(s,c) => Table.ReplaceValue(s,L{c},"|", Replacer.ReplaceText,Table.ColumnNames(TC))
)
in
B,
Items_A = Table.Combine(Table.AddColumn(Items_T, "Amount", each
let
TS = Text.Split,
a = TS([Items], "|"),
b = TS([Unit Price], "|"),
c = TS([Quantity], "|"),
d = List.Zip({a,b,c}),
e = List.Transform(d, each {_{0}} & {Number.From(_{1}) * Number.From(_{2})}),
f = Table.FromRows(e),
g = Table.AddColumn(f, "Amount_Ad", each
[Column2] / List.Count(List.PositionOf(Stock_A[c], [Column1],2)))
in
g
)[Amount])[[Column1], [Amount_Ad]],
Power Query solution 8 for Shared Item Billing Per Supplier, proposed by Rafael González B.:
Part 2/2:
Stock_A = [
b = Table.ExpandListColumn(Table.TransformColumns(a, {{"Items", Splitter.SplitTextByDelimiter(", ")}}), "Items"),
c = b[Items]
],
Nested = Table.Join(Stock_A[b],{"Items"}, Items_A, {"Column1"}, 1),
Result = Table.Group(Nested, {"Stockist"}, {{"Amount Paid", each List.Sum([Amount_Ad]), type nullable number}})
in
Result
Power Query solution 9 for Shared Item Billing Per Supplier, proposed by Peter Tholstrup:
let
source_items = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
source_stocklists = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
lt = List.Transform,
transform = (l) =>
lt(
List.Combine(lt(l, each lt(Text.SplitAny(Text.From(_), ",;&"), Text.Trim))),
each try Number.From(_) otherwise _
),
stocklists = [
group = Table.Group(source_stocklists, {"Stockist"}, {"Items", each transform([Items])}),
expand = Table.ExpandListColumn(group, "Items")
][expand],
items = [
items = transform(source_items[Items]),
price = transform(source_items[Unit Price]),
qty = transform(source_items[Quantity]),
get_count = (i) => List.Count(List.Select(stocklists[Items], each _ = i)),
amount = lt(List.Zip({price, qty, lt(items, get_count)}), each _{0} * _{1} / _{2}),
to_table = Table.FromColumns({items, amount}, {"Items", "Amount Paid"})
][to_table],
join = Table.Join(stocklists, "Items", items, "Items"),
result = Table.Group(join, {"Stockist"}, {"Amount Paid", each List.Sum([Amount Paid])})
in
resultPower Query solution 10 for Shared Item Billing Per Supplier, proposed by Yaroslav Drohomyretskyi:
let
Джерело = Excel.CurrentWorkbook(){[Name = "Items"]}[Content],
T = Table.Transpose(Джерело),
I = Table.TransformColumns(
Table.TransformColumnTypes(
Table.Transpose(
Table.SplitColumn(
Table.CombineColumns(
Table.TransformColumnTypes(T, List.Transform(Table.ColumnNames(T), each {_, type text})),
Table.ColumnNames(
Table.TransformColumnTypes(
T,
List.Transform(Table.ColumnNames(T), each {_, type text})
)
),
Combiner.CombineTextByDelimiter(",", QuoteStyle.None),
"Combine"
),
"Combine",
Splitter.SplitTextByAnyDelimiter({";", "&", ","}, QuoteStyle.Csv)
)
),
{{"Column3", Int64.Type}, {"Column2", Int64.Type}}
),
{{"Column1", Text.Trim, type text}}
),
S = Table.ExpandListColumn(
Table.TransformColumns(
Excel.CurrentWorkbook(){[Name = "Stocklist"]}[Content],
{{"Items", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}
),
"Items"
),
C = Table.Group(S, {"Items"}, {{"C", each Table.RowCount(_), Int64.Type}}),
A = Table.AddColumn(
Table.ExpandTableColumn(
Table.NestedJoin(
Table.ExpandTableColumn(
Table.NestedJoin(S, {"Items"}, I, {"Column1"}, "i", JoinKind.LeftOuter),
"i",
{"Column2", "Column3"},
{"P", "Q"}
),
{"Items"},
C,
{"Items"},
"x",
JoinKind.LeftOuter
),
"x",
{"C"},
{"C"}
),
"Amount Paid",
each [P] * [Q] / [C]
),
G = Table.Group(A, {"Stockist"}, {{"Amount Paid", each List.Sum([Amount Paid])}})
in
GPower Query solution 11 for Shared Item Billing Per Supplier, proposed by Arnaud Duvernois:
let
Source = Excel.CurrentWorkbook(){[Name="tCorresp"]}[Content],
Split = Table.ExpandListColumn(Table.TransformColumns(Source, {"Items" , each Text.Split(_,", ")}) ,"Items")
in
Split
let
Source = Excel.CurrentWorkbook(){[Name="tSource"]}[Content],
Split = Table.TransformColumns(Source,{}, each List.Select(Text.SplitAny(Text.From(_),",&; "),each _ <>"")),
Combine = Table.Combine(List.Transform(Table.ToRows(Split) , each Table.FromColumns(_,Table.ColumnNames(Source)))),
AddAmount = Table.AddColumn(Combine, "Amount Paid", each let a = Number.From([Unit Price])*Number.From([Quantity]), b=Table.RowCount(Table.SelectRows(tCorresp, (x)=> x[Items] = [Items])) in if b = 0 then 0 else a/b ),
Join = Table.NestedJoin(AddAmount, {"Items"}, tCorresp, {"Items"}, "tCorresp", JoinKind.Inner),
Expand = Table.ExpandTableColumn(Join, "tCorresp", {"Stockist"}, {"Stockist"}),
Group = Table.Group(Expand, {"Stockist"}, {{"Amount Paid", each List.Sum([Amount Paid]), type number}})
in
Group
Solving the challenge of Shared Item Billing Per Supplier with Excel
Excel solution 1 for Shared Item Billing Per Supplier, proposed by Bo Rydobon 🇹🇭:
=LET(s,
LAMBDA(
x,
REGEXEXTRACT(
ARRAYTOTEXT(
x
),
"w+",
1
)
),
i,
B9:B11,
t,
TEXTSPLIT(
ARRAYTOTEXT(
i
),
,
", "
),
HSTACK(A9:A11,
MAP(i,
LAMBDA(x,
LET(v,
TEXTSPLIT(
x,
,
", "
),
SUM((v=s(
A2:A5
))*s(
B2:B5
)*s(
C2:C5
)/LOOKUP(
v,
GROUPBY(
t,
t,
ROWS
)
)))))))Excel solution 2 for Shared Item Billing Per Supplier, proposed by Bo Rydobon 🇹🇭:
=LET(d,
DROP(
REDUCE(
0,
BYCOL(
A2:C5,
ARRAYTOTEXT
),
LAMBDA(
a,
v,
HSTACK(
a,
TOCOL(
REGEXEXTRACT(
v,
"w+",
1
)
)
)
)
),
,
1
),
i,
B9:B11,
t,
TEXTSPLIT(
ARRAYTOTEXT(
i
),
,
", "
),
g,
GROUPBY(
t,
t,
ROWS,
,
0
),
HSTACK(A9:A11,
MAP(i,
LAMBDA(a,
LET(b,
TEXTSPLIT(
a,
", "
),
SUM((b=TAKE(
d,
,
1
))*INDEX(
d,
,
2
)*INDEX(
d,
,
3
)/LOOKUP(
b,
g
)))))))Excel solution 3 for Shared Item Billing Per Supplier, proposed by Kris Jaganah:
=LET(
p,
REDUCE(
"",
BYROW(
A2:C5,
ARRAYTOTEXT
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
TOCOL(
REGEXEXTRACT(
y,
"[A-Z]+",
1
)
),
b,
REGEXEXTRACT(
y,
"[0-9]+",
1
& ),
HSTACK(
a,
BYROW(
WRAPCOLS(
--b,
COLUMNS(
b
)/2
),
PRODUCT
)
)
)
)
)
),
q,
DROP(
REDUCE(
"",
B9:B11,
LAMBDA(
v,
w,
VSTACK(
v,
TEXTSPLIT(
w,
", "
)
)
)
),
1
),
r,
TOCOL(
q,
3
),
s,
GROUPBY(
r,
r,
COUNTA
),
VSTACK(
{"Stockist",
"Amount Paid"},
HSTACK(
A9:A11,
BYROW(
IFNA(
VLOOKUP(
q,
p,
2,
0
)/VLOOKUP(
q,
s,
2,
0
),
0
),
SUM
)
)
)
)Excel solution 4 for Shared Item Billing Per Supplier, proposed by Julian Poeltl:
=LET(
T,
A2:C5,
TT,
A9:B11,
I,
TAKE(
T,
,
1
),
P,
CHOOSECOLS(
T,
2
),
Q,
TAKE(
T,
,
-1
),
IT,
TAKE(
TT,
,
-1
),
SP,
LAMBDA(
A,
TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
,
A
),
{",",
";",
"&"}
)
)
),
II,
SP(
I
),
UP,
SP(
P
),
QQ,
SP(
Q
),
ITT,
SP(
IT
),
QI,
UP*QQ/MAP(
II,
LAMBDA(
A,
COUNTA(
FILTER(
ITT,
ITT=A
)
)
)
),
VSTACK(
HSTACK(
"Stockist",
"Amount Paid"
),
HSTACK(
TAKE(
TT,
,
1
),
MAP(
IT,
LAMBDA(
A,
SUM(
XLOOKUP(
SP(
A
),
II,
QI,
0
)
)
)
)
)
)
)Excel solution 5 for Shared Item Billing Per Supplier, proposed by Oscar Mendez Roca Farell:
=LET(
t,
TRIM(
TEXTSPLIT(
CONCAT(
A2:A5&","
)&"|"&CONCAT(
B2:B5&","
)&"|"&CONCAT(
C2:C5&","
),
{",",
";",
"&"},
"|",
1
)
),
f,
FIND(
TAKE(
t,
1
),
B9:B11
),
HSTACK(
A9:A11,
BYROW(
IF(
f,
INDEX(
t,
2,
)*INDEX(
t,
3,
)/BYCOL(
f,
LAMBDA(
c,
COUNT(
c
)
)
)
),
LAMBDA(
r,
SUM(
TOCOL(
r,
2
)
)
)
)
)
)Excel solution 6 for Shared Item Billing Per Supplier, proposed by Duy Tùng:
=LET(
f,
LAMBDA(
v,
TOCOL(
TEXTSPLIT(
TEXTJOIN(
"/",
,
v
),
{",",
" ",
";",
"&"},
"/",
1
),
3
)
),
a,
B9:B11,
b,
f(
A2:A5
),
HSTACK(
A9:A11,
MAP(
a,
LAMBDA(
s,
SUM(
REGEXTEST(
s,
b
)*IFERROR(
f(
B2:B5
)*f(
C2:C5
)/BYROW(
N(
b=TOROW(
f(
a
)
)
),
SUM
),
0
)
)
)
)
)
)Solving the challenge of Shared Item Billing Per Supplier with Python
Python solution 1 for Shared Item Billing Per Supplier, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
def split_string(s):
return re.split(r"W+", str(s))
path = "PQ_Challenge_195.xlsx"
input1 = pd.read_excel(path, usecols="A:C", nrows=5)
input2 = pd.read_excel(path, usecols="A:B", skiprows=7, nrows=3)
test = pd.read_excel(path, usecols="F:G", nrows=3)
input1 = pd.concat([input1[col].apply(split_string).explode().reset_index(drop=True) for col in input1.columns], axis=1)
input2["Items"] = input2["Items"].apply(split_string)
input2 = pd.DataFrame(input2["Items"].explode()).
merge(input2, left_index=True, right_index=True).
drop(columns=["Items_y"]).rename(columns={"Items_x": "Items"})
input2["Stockist_no"] = input2.groupby("Items")["Stockist"].transform("count")
input2 = pd.merge(input1, input2, on="Items", how="right").dropna().reset_index(drop=True)
input2["Amount Paid"] = (input2["Unit Price"].astype(int) * input2["Quantity"].astype(int)) / input2["Stockist_no"]
input2["Amount Paid"] = input2["Amount Paid"].astype("int64")
result = input2.groupby("Stockist")["Amount Paid"].sum().reset_index()
print(result.equals(test)) # True
Solving the challenge of Shared Item Billing Per Supplier with Python in Excel
Python in Excel solution 1 for Shared Item Billing Per Supplier, proposed by Abdallah Ally:
import pandas as pd
# Function to clean and split text into alphanumeric words
def clean_split(text):
for char in text:
if not char.isalpha() and not char.isdigit():
text = text.replace(char, ' ')
return text.split()
file_path = 'PQ_Challenge_195.xlsx'
df1 = pd.read_excel(file_path, usecols='A:C', nrows=4).astype(str)
df2 = pd.read_excel(file_path, usecols='A:B', skiprows=7)
# Perform data wrangling
df2['Items'] = df2['Items'].map(lambda x: x.split(', '))
df2 = df2.explode(column='Items', ignore_index=True)
df2['Count'] = df2.groupby('Items').transform('count')
# Apply clean_split function to each column in df1
for col in df1.columns:
df1[col] = df1[col].map(clean_split)
df1 = df1.explode(column=['Items', 'Unit Price', 'Quantity'], ignore_index=True)
df = df2.merge(df1)
df[df.columns[2: ]] = df[df.columns[2: ]].astype(int)
df['Amount Paid'] = df.apply(lambda x: x[3] * x[4] /x[2], axis=1).astype(int)
df = df.groupby('Stockist')['Amount Paid'].sum().reset_index()
df
Solving the challenge of Shared Item Billing Per Supplier with R
R solution 1 for Shared Item Billing Per Supplier, proposed by Konrad Gryczan, PhD:
Not short, but concise, verbatim and "pipy" way.
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_195.xlsx"
input1 = read_xlsx(path, range = "A1:C5")
input2 = read_xlsx(path, range = "A8:B11")
test = read_xlsx(path, range = "F1:G4")
result1 = input1 %>%
mutate(across(everything(), ~str_split(.x, "\W+"))) %>%
unnest(cols = everything()) %>%
mutate(total = as.numeric(`Unit Price`) * as.numeric(Quantity)) %>%
select(Items, total)
result2 = input2 %>%
mutate(across(everything(), ~str_split(.x, "\W+"))) %>%
unnest(cols = everything()) %>%
mutate(part = n(), .by = Items)
result = result2 %>%
left_join(result1, by = "Items") %>%
mutate(paid_by_stockist = total/part) %>%
summarise(`Amount Paid` = sum(paid_by_stockist, na.rm = T), .by = Stockist)
identical(result, test)
# [1] TRUE
R solution 2 for Shared Item Billing Per Supplier, proposed by Anil Kumar Goyal:
library(readxl)
library(tidyverse)
items <- read_excel("PQ/PQ_Challenge_195.xlsx", range = "A1:C5") %>%
janitor::clean_names()
stocks <- read_excel("PQ/PQ_Challenge_195.xlsx", range = "A8:B11")
stocks %>%
separate_rows(Items) %>%
inner_join(
items %>%
separate_rows(items, unit_price, quantity, convert = TRUE),
by = join_by(Items == items)
) %>%
mutate(amt_paid = unit_price * quantity / n_distinct(Stockist),
.by = Items) %>%
summarise(amt_paid = sum(amt_paid),
.by = Stockist)
&&