Pivot the problem tables into result table. Result table has values as sum of Quantity * Price.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 231
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Quantity Times Price Total with Power Query
Power Query solution 1 for Quantity Times Price Total, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = Table.ToColumns(Excel.CurrentWorkbook(){[Name = "Table2"]}[Content]),
D = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each
let
s = each Text.Split(_, ", ")
in
List.Zip({s(_{1}), s(_{2})}),
(i, _) => {i{0}} & {_{0}, T{1}{List.PositionOf(T{0}, _{0})} * Number.From(_{1})}
),
{"Name", "A", "V"}
),
S = Table.AddColumn(
Table.Pivot(
D & Table.Group(D, "A", {{"Name", each "Total"}, {"V", each List.Sum([V])}}),
List.Intersect({T{0}, D[A]}),
"A",
"V",
each List.Sum(_) ?? 0
),
"Total",
each List.Sum(List.Skip(Record.ToList(_)))
)
in
SPower Query solution 2 for Quantity Times Price Total, proposed by Kris Jaganah:
let
A = (x) => Excel.CurrentWorkbook(){[Name = x]}[Content],
B = Table.ExpandListColumn(
Table.TransformColumns(A("Table1"), {"Items", each Text.Split(_, ", ")}),
"Items"
),
C = Table.Combine(
Table.Group(
B,
{"Person"},
{
"All",
each
let
a = Table.AddIndexColumn(_, "Id"),
b = Table.AddColumn(a, "Qty", each Number.From(Text.Split([Quantity], ", "){[Id]})),
c = Table.AddColumn(
b,
"QxP",
each (Table.SelectRows(A("Table2"), (v) => v[Items] = [Items])[Price]{0}) * [Qty]
)
in
c[[Person], [Items], [QxP]]
}
)[All]
),
D = Table.Pivot(C, List.Sort(List.Distinct(C[Items])), "Items", "QxP", List.Sum),
E = Table.AddColumn(D, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
G = Table.Group(C, {"Items"}, {{"Sum", each List.Sum([QxP]), type number}}),
H = Table.Pivot(G, List.Distinct(G[Items]), "Items", "Sum", List.Sum),
I = Table.AddColumn(
Table.AddColumn(H, "Total", each List.Sum(Record.FieldValues(_))),
"Person",
each "Total"
),
J = Table.Combine({E, I}),
K = Table.RenameColumns(J, {{"Person", "Name"}})
in
KPower Query solution 3 for Quantity Times Price Total, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Tbl = Table.AddColumn(
T1,
"A",
each
let
a = _,
b = List.Zip(List.Transform({[Items], [Quantity]}, each Text.Split(_, ", "))),
c = List.Transform(
b,
each Table.SelectRows(T2, (k) => k[Items] = _{0})[Price]{0} * Number.From(_{1})
),
d = Table.Group(
Table.FromColumns({List.Transform(b, each _{0}), c}),
{"Column1"},
{"A", each List.Sum([Column2])}
),
e = Table.PromoteHeaders(Table.Transpose(d))
in
e
)[[Person], [A]],
Tbl2 = Table.Sort(
Table.ExpandTableColumn(Tbl, "A", List.Sort(Table.ColumnNames(Table.Combine(Tbl[A])))),
"Person"
),
Sol =
let
a = Tbl2,
b = {"Total"} & List.Transform(List.Skip(Table.ToColumns(a)), each List.Sum(_)),
c = List.Transform(Table.ToRows(a), each List.Sum(List.Skip(_))),
d = c & {List.Sum(c)},
e = Table.ToRows(a) & {b},
f = List.Transform({0 .. List.Count(d) - 1}, each e{_} & {d{_}}),
g = Table.FromRows(f, {"Name"} & List.Skip(Table.ColumnNames(a)) & {"Total"})
in
g
in
SolPower Query solution 4 for Quantity Times Price Total, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(Fonte, "tab", each
let lt = List.Transform, pr = [Person], a = List.Skip(Record.FieldValues(_)),
b = lt(a,(x)=> Text.Split(x,", ") ),c = Table.FromRows(List.Zip({b{0},List.ReplaceMatchingItems(b{0},Table.ToRows(Tabela2)),lt(b{1},Number.From)})),
d = Table.Group(c,{"Column1"},{"tab", each List.Sum(lt(List.Zip({_[Column3],_[Column2]}),List.Product))}),
e = Table.AddColumn(Table.PromoteHeaders(Table.Transpose(d)),"rec", each [Name = pr, Total = List.Sum(Record.FieldValues(_))])
in Table.ExpandRecordColumn(e, "rec", Record.FieldNames(e[rec]{0} ) )
)[tab], tab = Table.Combine(add), cmb = tab & hashtag#table(Table.ColumnNames(tab),{List.Transform(Table.ToColumns(tab), each try List.Sum(_) otherwise "Total")}),
res = Table.ReorderColumns(cmb,List.Sort(List.RemoveItems(Table.ColumnNames(cmb),{"Total"})) & {"Total"})
in
res
Power Query solution 5 for Quantity Times Price Total, proposed by Abdallah Ally:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Transform1 = Table.TransformRows(
Source("Table1"),
each [
a = Text.Split([Items], ", "),
b = Text.Split([Quantity], ", "),
c = List.Transform(List.Zip({a, b}), (x) => [Name = [Person], Data = x{0} & "," & x{1}])
][c]
),
FromRecords = Table.FromRecords(List.Combine(Transform1)),
Split = Table.SplitColumn(FromRecords, "Data", each Text.Split(_, ","), {"Items", "Quantity"}),
Join = Table.Join(Split, "Items", Source("table2"), "Items"),
AddCol = Table.AddColumn(Join, "Data", each Number.From([Quantity]) * [Price]),
Pivot = Table.Pivot(
AddCol[[Name], [Items], [Data]],
List.Distinct(List.Sort(AddCol[Items])),
"Items",
"Data",
each List.Sum(_) ?? 0
),
Transform2 = Table.ToRows(Pivot)
& {{"Total"} & List.Transform(List.Skip(Table.ToColumns(Pivot)), List.Sum)},
FromRows = Table.FromRows(Transform2, Table.ColumnNames(Pivot)),
Result = Table.AddColumn(FromRows, "Total", each List.Sum(List.Skip(Record.ToList(_))))
in
ResultPower Query solution 6 for Quantity Times Price Total, proposed by Ramiro Ayala Chávez:
let
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.TransformColumns(
t1,
{{"Items", each Text.Split(_, ", ")}, {"Quantity", each Text.Split(_, ", ")}}
),
b = Table.AddColumn(a, "A", each Table.FromColumns({[Items], [Quantity]}))[[Person], [A]],
c = Table.ExpandTableColumn(b, "A", {"Column1", "Column2"}),
d = Table.AddColumn(c, "P", each t2[Price]{List.PositionOf(t2[Items], [Column1])}),
e = Table.AddColumn(
d,
"T",
each List.Product(List.Transform(List.Skip(Record.ToList(_), 2), Number.From))
)[[Person], [Column1], [T]],
f = Table.Sort(e, {"Column1", 0}),
g = Table.Pivot(f, List.Distinct(f[Column1]), "Column1", "T", List.Sum),
h = Table.RenameColumns(g, {"Person", "Name"}),
i = Table.ReplaceValue(h, null, 0, Replacer.ReplaceValue, {"u", "x", "y", "z"}),
j = Table.AddColumn(i, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
Sol = j
& #table(
Table.ColumnNames(j),
{{"Total"} & List.Transform(List.Skip(Table.ToColumns(j)), List.Sum)}
)
in
SolPower Query solution 7 for Quantity Times Price Total, proposed by Eric Laforce:
let
fxSrce = (n) => Excel.CurrentWorkbook(){[Name = n]}[Content],
Prices = List.Buffer(Table.ToColumns(fxSrce("tData231b"))),
Source = Table.Sort(fxSrce("tData231a"), "Person"),
Transform = Table.TransformRows(
Source,
each
let
_T = Table.FromRows(
List.Zip(
{Text.Split([Items], ", "), List.Transform(Text.Split([Quantity], ", "), Number.From)}
),
{"I", "Q"}
),
_Group = Table.Group(
_T,
"I",
{"V", each List.Sum([Q]) * Prices{1}{List.PositionOf(Prices{0}, [I]{0})}}
)
in
Table.FromRows(
{{[Person]} & _Group[V] & {List.Sum(_Group[V])}},
{"Name"} & _Group[I] & {"Total"}
)
),
Combine = Table.TransformColumns(Table.Combine(Transform), {}, each _ ?? 0),
UsedItems = List.Sort(List.RemoveItems(Table.ColumnName(Combine), {"Name", "Total"})),
OrderCols = Table.ReorderColumns(Combine, {"Name"} & UsedItems & {"Total"}),
AddTotalR = Table.FromColumns(
{OrderCols[Name] & {"Total"}}
& List.Transform(List.Skip(Table.ToColumns(OrderCols)), each _ & {List.Sum(_)}),
Table.ColumnNames(OrderCols)
)
in
AddTotalRPower Query solution 8 for Quantity Times Price Total, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
S1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
S1,
"T",
each Table.FromColumns(
{Text.Split([Items], ", "), Text.Split([Quantity], ", ")},
{"Items", "Qty"}
)
),
B = Table.SelectColumns(A, {"Person", "T"}),
C = Table.ExpandTableColumn(B, "T", {"Items", "Qty"}, {"Items", "Qty"}),
D = Table.NestedJoin(C, {"Items"}, S2, {"Items"}, "N"),
E = Table.ExpandTableColumn(D, "N", {"Price"}, {"Price"}),
F = Table.AddColumn(E, "TP", each Number.From([Qty]) * Number.From([Price])),
G = Table.SelectColumns(F, {"Person", "Items", "TP"}),
H = Table.Pivot(G, List.Sort(List.Distinct(G[Items])), "Items", "TP", List.Sum),
I = Table.AddColumn(H, "Total", each List.Sum(List.Skip(Record.ToList(_), 1))),
J = Table.Group(
I,
{},
{
{"x", each List.Sum([x]), type nullable number},
{"y", each List.Sum([y]), type number},
{"u", each List.Sum([u]), type nullable number},
{"z", each List.Sum([z]), type nullable number},
{"Total", each List.Sum([Total])}
}
),
K = Table.AddColumn(J, "Person", each "Total"),
L = Table.Combine({I, K})
in
LPower Query solution 9 for Quantity Times Price Total, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S = Table.SplitColumn(
Table.TransformColumns(
Table.RemoveColumns(
Table.ExpandListColumn(
Table.AddColumn(
Source,
"Custom",
each List.Zip({Text.Split([Items], ", "), Text.Split([Quantity], ", ")})
),
"Custom"
),
{"Items", "Quantity"}
),
{"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}
),
"Custom",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}),
{"Items", "Quantity"}
),
M = Table.ExpandTableColumn(
Table.NestedJoin(
S,
{"Items"},
Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
{"Items"},
"x",
JoinKind.LeftOuter
),
"x",
{"Price"},
{"Price"}
),
V = Table.RemoveColumns(
Table.AddColumn(M, "Value", each Number.From([Quantity]) * [Price]),
{"Quantity", "Price"}
),
P = Table.Pivot(
Table.Sort(V, {{"Items", Order.Ascending}}),
List.Distinct(Table.Sort(V, {{"Items", Order.Ascending}})[Items]),
"Items",
"Value",
List.Sum
),
RT = Table.AddColumn(
P,
"Total",
each List.Sum(List.Select(Record.FieldValues(_), each _ is number))
),
GT = List.Accumulate(
Table.ColumnNames(RT),
[],
(s, c) => Record.AddField(s, c, try List.Sum(Table.Column(RT, c)) otherwise "Total")
),
H = Table.InsertRows(RT, Table.RowCount(RT), {GT})
in
HPower Query solution 10 for Quantity Times Price Total, proposed by Alexandre Garcia:
let
A = each Excel.CurrentWorkbook(){[Name = _]}[Content],
B =
let
x = Table.ToColumns(A("Table2"))
in
Record.FromList(x{1}, x{0}),
C = {"Total"},
D = (x) => List.Transform(Text.Split(x, ", "), each try Number.From(_) otherwise _),
E = List.TransformMany(
Table.ToRows(A("Table1")),
each {Table.FromRows(List.Zip(List.Transform(List.Skip(_), D)), {"a", "b"})},
(x, y) =>
[
a = Table.Group(y, "a", {"b", each Record.Field(B, [a]{0}) * List.Sum([b])}),
b = Record.FromList(a[b], a[a]),
c = List.Sum(Record.FieldValues(b)),
d = Table.FromRecords({[Name = x{0}] & b & [Total = c]})
][d]
),
F = Table.TransformColumns(Table.Sort(Table.Combine(E), {"Name", 0}), {}, each _ ?? 0),
G = Table.FromRows(
Table.ToRows(F) & {C & List.Transform(List.Skip(Table.ToColumns(F)), List.Sum)},
Table.ColumnNames(F)
),
H = Table.SelectColumns(G, List.RemoveItems(List.Sort(Table.ColumnNames(G)), C) & C)
in
HPower Query solution 11 for Quantity Times Price Total, proposed by Mihai Radu O:
let
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
s = Table.Combine(
Table.AddColumn(
t1,
"r",
each [
ts = Text.Split,
lt = List.Transform,
a = ts([Items], ", "),
b = lt(ts([Quantity], ", "), Number.From),
c = List.Combine(lt(a, (x) => Table.SelectRows(t2, each [Items] = x)[Price])),
d = lt(List.Zip({b, c}), (x) => x{0} * x{1}),
e = Table.FromColumns(
{List.Repeat({[Person]}, List.Count(a)), a, d},
{"Name", "Items", "Value"}
)
][e]
)[r]
),
Pivot = Table.Pivot(s, List.Sort(List.Distinct(s[Items])), "Items", "Value", List.Sum),
tr = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.FieldValues(_)))),
tc = [
a = Table.ToColumns(tr),
b = List.Transform(a, (x) => try List.Sum(x) otherwise "Total"),
c = Table.FromRows(Table.ToRows(tr) & {b}, Table.ColumnNames(tr))
][c]
in
tcPower Query solution 12 for Quantity Times Price Total, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
ToLists = Table.TransformColumns(
Source,
List.Transform(List.Skip(Table.ColumnNames(Source)), each {_, each Text.Split(_, ", ")})
),
AddNT = Table.AddColumn(
ToLists,
"NT",
each Table.FromColumns({[Items], [Quantity]}, {"Items", "Quantity"})
)[[Person], [NT]],
ExpandNT = Table.ExpandTableColumn(AddNT, "NT", {"Items", "Quantity"}, {"Items", "Quantity"}),
MergeCosts = Table.ExpandTableColumn(
Table.NestedJoin(ExpandNT, {"Items"}, Source2, {"Items"}, "ExpandNT", JoinKind.LeftOuter),
"ExpandNT",
{"Price"}
),
InsertValue = Table.AddColumn(
MergeCosts,
"Value",
each Number.From([Quantity]) * [Price],
type number
)[[Person], [Items], [Value]],
PivotCol = Table.Pivot(
InsertValue,
List.Sort(List.Distinct(InsertValue[Items])),
"Items",
"Value",
List.Sum
),
AddRowsTotal = Table.AddColumn(PivotCol, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
AddColsTotal = AddRowsTotal
& Table.FromColumns(
List.Transform(Table.ToColumns(AddRowsTotal), each {try List.Sum(_) otherwise "Total"}),
Table.ColumnNames(AddRowsTotal)
)
in
AddColsTotalPower Query solution 13 for Quantity Times Price Total, proposed by Francesco Bianchi 🇮🇹:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Tbl = Table.AddColumn(
T1,
"R",
each [
I = Text.Split(_[Items], ", "),
Q = List.Transform(Text.Split(_[Quantity], ", "), (x) => Number.From(x)),
t = Table.FromColumns({I, Q}, List.Skip(Table.ColumnNames(T1)))
][t]
)[[Person], [R]],
Exp = Table.ExpandTableColumn(Tbl, "R", {"Items", "Quantity"}, {"Items", "Quantity"}),
Calc = Table.AddColumn(
Exp,
"Total",
each [a = Table.ToColumns(T2), b = List.PositionOf(a{0}, _[Items]), c = a{1}{b} * _[Quantity]][
c
]
)[[Person], [Items], [Total]],
SortedRows = Table.Sort(Calc, {{"Items", Order.Ascending}}),
PivotedColumn = Table.Pivot(
SortedRows,
List.Distinct(SortedRows[Items]),
"Items",
"Total",
each List.Sum(_) ?? 0
),
Tot1 = PivotedColumn
& Table.FromRows(
{{"Total"} & List.Transform(List.Skip(Table.ToColumns(PivotedColumn)), each List.Sum(_))},
Table.ColumnNames(PivotedColumn)
),
Sol = Table.RenameColumns(
Table.AddColumn(Tot1, "T&otal", each List.Sum(List.Skip(Record.ToList(_))), type number),
{{"Person", "Name"}}
)
in
SolPower Query solution 14 for Quantity Times Price Total, proposed by Fowmy Abdulmuttalib:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
AC = Table.AddColumn,
T3 = Table.TransformColumns(T1, {{"Items", each Text.Split(_, ", ")}, {"Quantity", each Text.Split(_, ", ")}}),
T4 = AC(T3, "Index", each {0 .. List.Count([Items]) - 1}),
T5 = Table.ExpandListColumn(T4, "Index"),
T6 = AC(T5, "Item", each _[Items]{[Index]}),
T7 = AC(T6, "Amount", (x) => [q = Number.From(x[Quantity]{x[Index]}), p = Table.SelectRows(T2, each _[Items] = x[Item])[Price]{0}, a = p * q][a]),
T8 = T7[[Person], [Item], [Amount]],
T9 = Table.Pivot(T8, List.Sort(List.Distinct(T8[Item])), "Item", "Amount", List.Sum),
T10 = AC(T9, "RowTotal", each List.Sum(List.Skip(Record.FieldValues(_)))),
T11 = List.Accumulate(List.Skip(Table.ColumnNames(T10)), hashtag#table({"Person"}, {{"Total"}}), (s, c) => AC(s, c, each List.Sum(Table.Column(T10, c)))),
T12 = Table.Combine({T10, T11})
in
T12
hashtag#msexcel hashtag#powerbi
Power Query solution 15 for Quantity Times Price Total, proposed by Oleksandr Mynka:
let
tbl1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
dic = Record.FromTable(Table.RenameColumns(tbl2, {{"Items", "Name"}, {"Price", "Value"}})),
nms = List.Sort(List.Distinct(b[Item])),
total = Record.Combine(
{[Name = "Total"]}
& List.Transform(nms & {"Total"}, (n) => Record.AddField([], n, List.Sum(Table.Column(e, n))))
),
tr = (x) =>
[
fx_split = (x) => Splitter.SplitTextByDelimiter(",")(x),
name = x{0},
items = List.Transform(fx_split(x{1}), Text.Trim),
qty = List.Transform(fx_split(x{2}), Number.FromText),
num = List.Count(qty),
prod = List.Transform(List.Numbers(0, num), (i) => qty{i} * Record.Field(dic, items{i})),
out = Table.FromColumns({List.Repeat({name}, num), items, prod}, {"Name", "Item", "Amount"})
][out],
a = Table.ToRows(tbl1),
b = Table.Combine(List.Transform(a, tr)),
c = Table.Pivot(b, nms, "Item", "Amount", List.Sum),
d = Table.TransformColumns(c, {"Name", each _}, (x) => if x = null then 0 else x),
e = Table.AddColumn(d, "Total", (r) => List.Sum(Record.ToList(Record.SelectFields(r, nms)))),
f = Table.FromRecords({total}),
g = Table.Combine({e, f})
in
gSolving the challenge of Quantity Times Price Total with Excel
Excel solution 1 for Quantity Times Price Total, proposed by Bo Rydobon 🇹🇭:
=LET(
c,
TOCOL,
s,
LAMBDA(
a,
TEXTSPLIT(
CONCAT(
a&"_"
),
", ",
"_",
1
)
),
q,
--s(
C3:C5
),
i,
s(
B3:B5
),
PIVOTBY(
c(
IFS(
q,
A3:A5
),
3
),
c(
i,
3
),
c(
q*LOOKUP(
i,
A9:B15
),
3
),
SUM
)
)Excel solution 2 for Quantity Times Price Total, proposed by Kris Jaganah:
=LET(
a,
TEXTSPLIT(
CONCAT(
MAP(
A3:A5,
B3:B5,
C3:C5,
LAMBDA(
x,
y,
z,
LET(
b,
TEXTSPLIT(
y,
", "
),
c,
XLOOKUP(
b,
A9:A15,
B9:B15
)*TEXTSPLIT(
z,
", "
),
CONCAT(
x&"-"&b&"-"&c&","
)
)
)
)
),
"-",
",",
1
),
PIVOTBY(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
--TAKE(
a,
,
-1
),
SUM
)
)Excel solution 3 for Quantity Times Price Total, proposed by Julian Poeltl:
=LET(
T,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
B3:B5
)
),
LAMBDA(
A,
B,
VSTACK(
A,
LET(
S,
TEXTSPLIT(
INDEX(
B3:B5,
B
),
,
", "
),
N,
INDEX(
A3:A5,
B
),
IFNA(
HSTACK(
N,
S,
XLOOKUP(
S,
A9:A15,
B9:B15
)*TEXTSPLIT(
INDEX(
C3:C5,
B
),
,
", "
)
),
N
)
)
)
)
),
1
),
P,
PIVOTBY(
TAKE(
T,
,
1
),
CHOOSECOLS(
T,
2
),
DROP(
T,
,
2
),
SUM,
),
D,
DROP(
P,
1
),
R,
VSTACK(
HSTACK(
"Name",
TAKE(
DROP(
P,
,
1
),
1
)
),
D
),
IF(
R="",
0,
R
)
)Excel solution 4 for Quantity Times Price Total, proposed by Oscar Mendez Roca Farell:
=LET(
O,
TOCOL,
F,
LAMBDA(
j,
TEXTSPLIT(
CONCAT(
j&"|"
),
", ",
"|",
1
)
),
n,
-F(
C3:C5
),
i,
O(
F(
B3:B5
),
2
),
PIVOTBY(
O(
IFS(
n,
A3:A5
),
2
),
i,
O(
-n,
2
)*VLOOKUP(
i,
A9:B15,
2,
),
SUM
)
)Excel solution 5 for Quantity Times Price Total, proposed by Duy Tùng:
=LET(
C,
TOCOL,
f,
LAMBDA(
x,
TEXTSPLIT(
TEXTJOIN(
"/",
,
x
),
", ",
"/"
)
),
b,
C(
f(
B3:B5
),
3
),
d,
f(
C3:C5
)*1,
PIVOTBY(
C(
IFS(
d,
A3:A5
),
3
),
b,
C(
d,
3
)*LOOKUP(
b,
A9:B15
),
SUM
)
)Excel solution 6 for Quantity Times Price Total, proposed by Sunny Baggu:
=LET(
_p,
A3:A5,
_i,
B3:B5,
_q,
C3:C5,
_a,
TOROW(
SORT(
UNIQUE(
TEXTSPLIT(
ARRAYTOTEXT(
_i
),
,
", "
)
)
)
),
_b,
SORT(
_p
),
_c,
DROP(
REDUCE(
"",
_a,
LAMBDA(e,
f,
HSTACK(
e,
MAP(
_b,
LAMBDA(x,
SUM(
TEXTSPLIT(
XLOOKUP(
x,
_p,
C3:C5
),
", "
) *
XLOOKUP(
TEXTSPLIT(
XLOOKUP(
x,
_p,
_i
),
", "
),
A9:A15,
B9:B15
) *
(TEXTSPLIT(
XLOOKUP(
x,
_p,
_i
),
", "
) = f)
)
)
)
)
)
),
,
1
),
_tc,
BYCOL(
_c,
LAMBDA(
c,
SUM(
c
)
)
),
_tr,
BYROW(VSTACK(
_c,
_tc
),
LAMBDA(r,
SUM((r)))),
VSTACK(
HSTACK(
"Name",
_a,
"Total"
),
HSTACK(
VSTACK(
_b,
"Total"
),
VSTACK(
_c,
_tc
),
_tr
)
)
)Excel solution 7 for Quantity Times Price Total, proposed by LEONARD OCHEA 🇷🇴:
=LET(
a,
A3:C5,
b,
A9:B15,
C,
CHOOSECOLS,
F,
LAMBDA(
x,
TEXTSPLIT(
CONCAT(
SUBSTITUTE(
x&", ",
", ",
"-"&C(
a,
1
)&"|"
)
),
"-",
"|",
1
)
),
n,
C(
F(
C(
a,
2
)
),
2
),
i,
C(
F(
C(
a,
2
)
),
1
),
q,
C(
F(
C(
a,
3
)
),
1
),
PIVOTBY(
n,
i,
q*VLOOKUP(
i,
b,
2
),
SUM
)
)Excel solution 8 for Quantity Times Price Total, proposed by Anshu Bantra:
=LET(
tbl1_,
A3:C5,
tbl2_,
A9:B15,
repts_,
BYROW(
INDEX(
tbl1_,
,
2
),
LAMBDA(
x,
LEN(
SUBSTITUTE(
x,
", ",
""
)
)
)
),
names_,
TEXTSPLIT(
CONCAT(
REPT(
INDEX(
tbl1_,
,
1
) & ",",
repts_
)
),
,
","
),
items_,
TEXTSPLIT(
TEXTJOIN(
", ",
TRUE,
INDEX(
tbl1_,
,
2
)
),
,
", "
),
qty_,
TEXTSPLIT(
TEXTJOIN(
", ",
TRUE,
INDEX(
tbl1_,
,
3
)
),
,
", "
),
price_,
XLOOKUP(
items_,
INDEX(
tbl2_,
,
1
),
INDEX(
tbl2_,
,
2
)
),
amount_,
qty_ * price_,
pvt_,
PIVOTBY(
DROP(
names_,
-1
),
items_,
amount_,
SUM
),
seq_,
SEQUENCE(
ROWS(
pvt_
),
COLUMNS(
pvt_
)
),
IF(
seq_ = 1,
"Name",
IF(
pvt_ = "",
0,
pvt_
)
)
)Excel solution 9 for Quantity Times Price Total, proposed by Hamidi Hamid:
=LET(
g,
TOCOL,
w,
LAMBDA(
d,
DROP(
REDUCE(
0,
BYROW(
d&"-",
CONCAT
),
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
{",",
"-"},
)
)
)
),
1,
-1
)
),
x,
w(
B3:B5
),
t,
g(
TRIM(
g(
IF(
x="",
1/0,
x
)
)
),
3
),
z,
w(
C3:C5
),
r,
g(
IF(
z="",
1/0,
z
),
3
)*1,
n,
TRIM(
g(
IF(
x<>"",
A3:A5,
1/0
),
3
)
),
rc,
g(
XLOOKUP(
t,
A9:A15,
B9:B15,
1/0
)*1
),
PIVOTBY(
n,
t,
r*rc,
SUM
)
)Excel solution 10 for Quantity Times Price Total, proposed by Asheesh Pahwa:
=LET(
p,
A3:A5,
r,
DROP(
REDUCE(
"",
SEQUENCE(
3
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
I,
INDEX(
B3:C5,
y,
),
t,
TEXTSPLIT(
TAKE(
I,
,
1
),
,
", "
),
tq,
TEXTSPLIT(
TAKE(
I,
,
-1
),
,
", "
),
xl,
XLOOKUP(
t,
A10:A16,
B10:B16,
""
),
ip,
INDEX(
p,
y,
),
IFNA(
HSTACK(
ip,
t,
tq*xl
),
ip
)
)
)
)
),
1
),
s,
SORT(
TOROW(
UNIQUE(
INDEX(
r,
,
2
)
)
),
,
1,
1
),
c,
SORT(
p
)&"-"&s,
cn,
TAKE(
r,
,
1
)&"-"&INDEX(
r,
,
2
),
t,
TOCOL(
c
),
w,
WRAPROWS(
IFERROR(
MAP(
t,
LAMBDA(
x,
SUM(
FILTER(
TAKE(
r,
,
-1
),
cn=x,
0
)
)
)
),
""
),
4
),
b,
BYROW(
w,
LAMBDA(
& x,
SUM(
x
)
)
),
h,
HSTACK(
VSTACK(
SORT(
p
),
"Total"
),
IFNA(
VSTACK(
HSTACK(
w,
b
),
BYCOL(
w,
LAMBDA(
y,
SUM(
y
)
)
)
),
SUM(
b
)
)
),
v,
VSTACK(
HSTACK(
"Name",
s,
"Total"
),
h
),
v
)Excel solution 11 for Quantity Times Price Total, proposed by ferhat CK:
=LET(
r,
DROP(
REDUCE(
0,
A3:A5,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
TEXTSPLIT(
OFFSET(
y,
,
1
),
,
", "
),
b,
XLOOKUP(
a,
A9:A15,
B9:B15
)*--TEXTSPLIT(
OFFSET(
y,
,
2
),
,
", "
),
IFERROR(
HSTACK(
y,
GROUPBY(
a,
b,
SUM,
,
0
)
),
y
)
)
)
)
),
1
),
PIVOTBY(
TAKE(
r,
,
1
),
TAKE(
TAKE(
r,
,
-2
),
,
1
),
TAKE(
r,
,
-1
),
SUM
)
)Excel solution 12 for Quantity Times Price Total, proposed by Ankur Sharma:
=LET(
a,
TEXTSPLIT(
TEXTJOIN(
", ",
,
MAP(
A3:A5,
B3:B5,
C3:C5,
LAMBDA(
x,
y,
z,
TEXTJOIN(
", ",
,
x & "-" & TEXTSPLIT(
y,
", "
) & "-" & TEXTSPLIT(
z,
", "
)
)
)
)
),
"-",
", "
),
a_pe,
TAKE(
a,
,
1
),
a_i,
CHOOSECOLS(
a,
2
),
a_q,
--TAKE(
a,
,
-1
),
a_pr,
a_q * XLOOKUP(
a_i,
A9:A15,
B9:B15
),
PIVOTBY(
a_pe,
a_i,
a_pr,
SUM
)
)Excel solution 13 for Quantity Times Price Total, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
h,
LET(
m,
VALUE(
TRANSPOSE(
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
BYCOL(
TOROW(
SORT(
UNIQUE(
TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
,
B3:B5
),
,
","
)
)
),
,
1
)
),
LAMBDA(
c,
TEXTJOIN(
",",
,
IFERROR(
MAP(
SORT(
A3:A5
),
LAMBDA(
p,
XLOOKUP(
c,
A9:A15,
B9:B15
)*XLOOKUP(
c,
MAP(
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
MAP(
A3:A5,
MAP(
B3:B5,
LAMBDA(
a,
COUNTA(
TEXTSPLIT(
a,
","
)
)
)
),
LAMBDA(
x,
y,
REPT(
x&",",
y
)
)
)
),
,
","
),
-1
),
TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
,
B3:B5
),
,
","
)
),
LAMBDA(
o,
i,
XLOOKUP(
p,
o,
i
)
)
),
TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
,
C3:C5
),
,
","
)
)
)
)
),
0
)
)&"/"
)
)
),
",",
"/"
),
-1
)
)
),
VSTACK(
m,
BYCOL(
m,
LAMBDA(
t,
SUM(
t
)
)
)
)
),
HSTACK(
VSTACK(
HSTACK(
"Name",
TOROW(
SORT(
UNIQUE(
TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
,
B3:B5
),
,
","
)
)
),
,
1
)
)
),
HSTACK(
VSTACK(
SORT(
A3:A5
),
"Total"
),
h
)
),
VSTACK(
"Total",
BYROW(
h,
LAMBDA(
n,
SUM(
n
)
)
)
)
)
)Excel solution 14 for Quantity Times Price Total, proposed by Imam Hambali:
=LET(
l,
LAMBDA(
x,
TEXTSPLIT(
TEXTJOIN(
";",
TRUE,
x
),
",",
";"
)
),
i,
TRIM(
l(
B3:B5
)
),
p,
IF(
ISTEXT(
i
),
A3:A5,
NA()
),
q,
l(
C3:C5
)*1,
hs,
HSTACK(
TOCOL(
i,
3
),
TOCOL(
p,
3
),
TOCOL(
q,
3
)
),
xl,
XLOOKUP(
TAKE(
hs,
,
1
),
A9:A15,
B9:B15
),
cc,
CHOOSECOLS,
pvt,
DROP(
PIVOTBY(
cc(
hs,
2
),
cc(
hs,
1
),
cc(
hs,
3
)*xl,
SUM
),
1
),
VSTACK(
E2:J2,
IF(
pvt="",
0,
pvt
)
)
)Excel solution 15 for Quantity Times Price Total, proposed by Philippe Brillault:
=LET(i,
INDEX,
rr,
REGEX.REPLACE,
cc,
CHOOSECOLS,
wrc,
WRAPCOLS,
reps,
LEN(
rr(
cc(
T_1,
2
),
"[^,]",
""
)
),
n,
COLUMNS(
T_1
),
v,
TEXTSPLIT(CONCAT(MAKEARRAY(n,
1,
LAMBDA(i,
j,
CONCAT(REPT(rr(
cc(
T_1,
i
),
"s",
""
)&",",
1+reps*(i=1)))))),
,
",",
1),
t2d,
wrc(
v,
ROWS(
v
)/n
),
p,
cc(
t2d,
2
),
PIVOTBY(
cc(
t2d,
1
),
p,
--cc(
t2d,
3
)*LOOKUP(
p,
T_p
),
SUM
))Excel solution 16 for Quantity Times Price Total, proposed by Songglod P.:
=LET(
ts,
TEXTSPLIT,
i,
INDEX,
_t,
LAMBDA(
x,
DROP(
REDUCE(
0,
x,
LAMBDA(
a,
v,
VSTACK(
a,
ts(
v,
", "
)
)
)
),
1
)
),
c,
TOCOL(
A3:A5&";"&_t(
B3:B5
)&";"&_t(
C3:C5
),
3
),
d,
ts(
ARRAYTOTEXT(
c
),
";",
", "
),
e,
IFERROR(
--d*XLOOKUP(
i(
d,
,
2
),
A9:A15,
B9:B15
),
d
),
PIVOTBY(
i(
e,
,
1
),
i(
e,
,
2
),
i(
e,
,
3
),
SUM
)
)Solving the challenge of Quantity Times Price Total with Python
Python solution 1 for Quantity Times Price Total, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_231.xlsx"
input1 = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=3)
input2 = pd.read_excel(path, usecols="A:B", skiprows=7, nrows=8)
test = pd.read_excel(path, usecols="E:J", skiprows=1, nrows=4)
input1 = input1.assign(
Items=input1['Items'].str.split(', '),
Quantity=input1['Quantity'].str.split(', ')
).explode(['Items', 'Quantity'], ignore_index=True)
input = input1.merge(input2, on='Items', how='left')
input['Amount'] = input.eval('Quantity.astype("int64") * Price').astype('int64')
input.drop(columns=['Price', 'Quantity'], inplace=True)
input = input.pivot_table(
index='Person',
columns='Items',
values='Amount',
aggfunc='sum',
fill_value=0,
margins=True,
margins_name='Total'
).reset_index().rename(
columns={'Person': 'Name'}
).rename_axis(
None, axis=1
)
print(input.equals(test)) # True
Solving the challenge of Quantity Times Price Total with Python in Excel
Python in Excel solution 1 for Quantity Times Price Total, proposed by Alejandro Campos:
df_table1 = xl("A2:C5", headers=True)
df_table2 = xl("A8:B15", headers=True)
df_table1['Items'] = df_table1['Items'].apply(lambda x: x.split(', '))
df_table1['Quantity'] = df_table1['Quantity'].apply(lambda x: list(map(int, x.split(', '))))
df_table1 = df_table1.explode(['Items', 'Quantity'])
merged_df = pd.merge(df_table1, df_table2, on='Items')
merged_df['TotalValue'] = merged_df['Quantity'] * merged_df['Price']
result_df = merged_df.pivot_table(index='Person', columns='Items', values='TotalValue', aggfunc='sum')
pd.set_option('future.no_silent_downcasting', True)
result_df = result_df.fillna(0).infer_objects(copy=False)
pd.reset_option('future.no_silent_downcasting')
result_df['Total'] = result_df.sum(axis=1)
total_row = result_df.sum(axis=0).to_frame().T
total_row.index = ['Total']
result_df = pd.concat([result_df, total_row])
result_df
Python in Excel solution 2 for Quantity Times Price Total, proposed by Anshu Bantra:
data_df = xl("A2:C5", headers=True)
price_df = xl("A8:B15", headers=True)
data_df['Items'] = data_df['Items'].apply(lambda x: x.split(', '))
data_df['Quantity'] = data_df['Quantity'].apply(lambda x: list(map(int, x.split(', '))))
data_df = data_df.explode(['Items', 'Quantity'])
data_df = data_df.merge(price_df, how='left', on='Items')
data_df['Amount'] = data_df['Quantity'] * data_df['Price']
pd.pivot_table( data_df, index='Person', columns='Items', values='Amount'
, aggfunc='sum', margins=True, margins_name='Total', fill_value=0 ).rename_axis('Name', axis=1).rename_axis(None, axis=0)
Python in Excel solution 3 for Quantity Times Price Total, proposed by Ümit Barış Köse, MSc:
data_df, price_df = xl("A2:C5", headers=True), xl("A8:B15", headers=True)
data_df['Items'] = data_df['Items'].str.split(r's*,s*')
data_df['Quantity'] = data_df['Quantity'].str.split(r's*,s*').apply(lambda x: list(map(int, x)))
data_df = data_df.explode(['Items', 'Quantity'])
data_df = data_df.merge(price_df, how='left', on='Items')
data_df['Amount'] = data_df['Quantity'] * data_df['Price']
pivot_table = data_df.groupby(['Person', 'Items'])['Amount'].sum().unstack(fill_value=0)
pivot_table['Total'] = pivot_table.sum(axis=1)
pivot_table.loc['Total'] = pivot_table.sum()
pivot_table = pivot_table.rename_axis('Name', axis=1).rename_axis(None)
Solving the challenge of Quantity Times Price Total with R
R solution 1 for Quantity Times Price Total, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(janitor)
path = "Power Query/PQ_Challenge_231.xlsx"
input1 = read_excel(path, range = "A2:C5")
input2 = read_excel(path, range = "A8:B15")
test = read_excel(path, range = "E2:J6")
input = input1 %>%
separate_rows(c(Items, Quantity), sep = ", ") %>%
left_join(input2, by = "Items") %>%
mutate(Amount = as&.numeric(Quantity) * Price) %>%
select(-c(Price, Quantity)) %>%
pivot_wider(names_from = "Items", values_from = "Amount", values_fn = list(Amount = sum), values_fill = 0) %>%
select(Name = Person,u, x, y, z) %>%
arrange(Name) %>%
adorn_totals(c("row", "col"))
all.equal(input, test, check.attributes = FALSE)
#> [1] TRUE