Calculate the Shipped and Non-shipped amounts (Quantity * Price) for all Fruits. Also generate the total row and column.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 233
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Shipped Fruit Value with Power Query
Power Query solution 1 for Calculate Shipped Fruit Value, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
L = {"Shipped Amount", "Not Shipped Amount", "Total", "Status"},
P = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each
let
s = List.Split(List.Skip(_), 3)
in
s & List.Transform(s, each {"T"} & List.Skip(_)),
(i, _) => {L{List.PositionOf({"Y", "N", "T"}, _{0})}, i{0}, _{1} * _{2}}
),
{L{3}, "A", "V"}
),
S = Table.Sort(
Table.Pivot(
P & Table.Group(P, L{3}, {{"A", each L{2}}, {"V", each List.Sum([V])}}),
List.Sort(Source[Fruits]) & {L{2}},
"A",
"V",
each List.Sum(_) ?? 0
),
each List.PositionOf(L, [Status])
)
in
SPower Query solution 2 for Calculate Shipped Fruit Value, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Accumulate(
{"1", "2"},
A,
(x, y) =>
Table.CombineColumns(
x,
{"Price" & y, "Quantity" & y, "Shipped" & y},
each Text.From(_{0} * _{1}) & "-" & _{2},
"QXP" & y
)
),
C = Table.UnpivotOtherColumns(B, {"Fruits"}, "A", "V"),
D = Table.SplitColumn(C, "V", each Text.Split(_, "-"), {"Tot", "Status"}),
E = Table.TransformColumns(
D,
{"Status", each (if _ = "Y" then "" else "Not ") & "Shipped Amount"}
),
F = Table.TransformColumnTypes(E, {{"Tot", Int64.Type}}),
G = Table.RemoveColumns(F, {"A"}),
H = Table.Pivot(G, List.Sort(List.Distinct(G[Fruits])), "Fruits", "Tot", List.Sum),
I = Table.Sort(H, {"Status", 1}),
J = Table.AddColumn(I, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
K = List.Skip(Table.ColumnNames(J)),
L = Record.FromList(List.Transform(K, each List.Sum(Table.Column(J, _))), K),
M = Record.Combine({[Status = "Total"], L}),
N = Table.InsertRows(J, Table.RowCount(J), {M})
in
NPower Query solution 3 for Calculate Shipped Fruit Value, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sort = {"Apple", "Banana", "Papaya", "Mango", "Pineapple", "Kiwi"},
LT = List.Transform(
Table.ToRows(Source),
(x) =>
let
a = List.Split(List.Skip(x), 3),
b = List.Transform(a, each {x{0}, _{0}, _{1} * _{2}})
in
b
),
Tbl = Table.FromRows(List.Combine(LT), {"A", "Status", "B"}),
Replace = Table.ReplaceValue(
Tbl,
each [Status],
each if [Status] = "Y" then "Shipped Amount" else "Not Shipped Amount",
Replacer.ReplaceText,
{"Status"}
),
Pivot = Table.Sort(
Table.Pivot(
Replace,
List.Sort(List.Distinct(Replace[A]), each List.PositionOf(Sort, _)),
"A",
"B",
each List.Sum(_) ?? 0
),
{"Status", 1}
),
Tbl2 = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
Total = Table.FromRows(
{{"Total"} & List.Transform(List.Skip(Table.ToColumns(Tbl2)), List.Sum)},
Table.ColumnNames(Tbl2)
),
Sol = Tbl2 & Total
in
SolPower Query solution 4 for Calculate Shipped Fruit Value, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(
Fonte,
"tab",
each
let
a = List.Skip(Record.FieldValues(_)),
b = List.Transform(
List.Split(a, 3),
(x) =>
if x{0} = "Y" then
{[Fruits]} & {"Shipped Amount"} & {List.Product(List.Skip(x))}
else
{[Fruits]} & {"Not Shipped Amount"} & {List.Product(List.Skip(x))}
)
in
Table.FromRows(b, {"a", "Status", "b"})
)[tab],
cmb = Table.Combine(add),
pivot = Table.Pivot(cmb, List.Distinct(cmb[a]), "a", "b", List.Sum),
total = Table.AddColumn(pivot, "Total", each List.Sum(List.Skip(Record.FieldValues(_)))),
res = total
& Table.FromRows(
{List.Transform(Table.ToColumns(total), (x) => try List.Sum(x) otherwise "Total")},
Table.ColumnNames(total)
)
in
resPower Query solution 5 for Calculate Shipped Fruit Value, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform1 = Table.TransformRows(
Source,
each [
a = Record.ToList(_),
b = List.Split(List.Skip(a), 3),
c = List.Transform(b, each {a{0}, _{0}, List.Product(List.Skip(_))})
][c]
),
FromRows = Table.FromRows(List.Combine(Transform1)),
Unique = List.Distinct(List.Sort(FromRows[Column1])),
Pivot = Table.Pivot(FromRows, Unique, "Column1", "Column3", each List.Sum(_) ?? 0),
AddCol = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
Sort = Table.Sort(AddCol, {"Column2", 1}),
Transform2 = Table.TransformColumns(
Sort,
{"Column2", each (if _ = "Y" then "" else "Not ") & "Shipped Amount"}
),
TotalRow = {{"Total"} & List.Transform(List.Skip(Table.ToColumns(Transform2)), List.Sum)},
Combine = Transform2 & hashtag#table(Table.ColumnNames(Transform2), TotalRow),
Result = Table.RenameColumns(Combine, {"Column2", "Status"})
in
Result
Power Query solution 6 for Calculate Shipped Fruit Value, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData233"]}[Content],
Transform = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each List.Split(List.Skip(_), 3),
(o, _) => {o{0}, if (_{0} = "Y") then "Shipped Amount" else "Not Shipped Amount", _{1} * _{2}}
),
{"F", "Status", "Amt"}
),
Pivot = Table.Pivot(Transform, List.Distinct(Transform[F]), "F", "Amt", each List.Sum(_) ?? 0),
Sort = Table.ReorderColumns(
Table.Sort(Pivot, {{"Status", Order.Descending}}),
{"Status"} & List.Sort(List.Skip(Table.ColumnNames(Pivot)))
),
AddGTRowCol =
let
_AddGTCol = Table.FromRecords(
Table.TransformRows(Sort, each _ & [Total = List.Sum(List.Skip(Record.ToList(_)))])
),
_AddGTCNum = List.Transform(List.Skip(Table.ToColumns(_AddGTCol)), each _ & {List.Sum(_)})
in
Table.FromColumns({_AddGTCol[Status] & {"Total"}} & _AddGTCNum, Table.ColumnNames(_AddGTCol))
in
AddGTRowColPower Query solution 7 for Calculate Shipped Fruit Value, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
S,
"L",
each Table.FromColumns(
List.Zip(
List.Split(
List.Transform(
List.Skip(Record.ToList(_), 1),
each if _ = "Y" then "Shipped Amount" else if _ = "N" then "Not Shipped Amount" else _
),
3
)
),
{"Status", "Qty", "Price"}
)
),
B = Table.SelectColumns(A, {"Fruits", "L"}),
C = Table.ExpandTableColumn(B, "L", {"Status", "Qty", "Price"}, {"Status", "Qty", "Price"}),
D = Table.AddColumn(C, "Totol Qty", each [Qty] * [Price]),
E = Table.SelectColumns(D, {"Fruits", "Status", "Totol Qty"}),
F = Table.Pivot(E, List.Sort(List.Distinct(E[Fruits])), "Fruits", "Totol Qty", List.Sum),
G = Table.AddColumn(F, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
H = Table.UnpivotOtherColumns(G, {"Status"}, "A", "V"),
I = Table.Transpose(Table.Group(H, {"A"}, {{"Total", each List.Sum([V])}})),
J = Table.PromoteHeaders(I, [PromoteAllScalars = true]),
K = Table.AddColumn(J, "Status", each "Total"),
L = Table.Combine({G, K})
in
LPower Query solution 8 for Calculate Shipped Fruit Value, proposed by Ahmed Ariem:
let
f = (x) => {{x{0}, if x{1} = "N" then "Not Shipped Amount" else "Shipped Amount", x{2} * x{3}}}
& {{x{0}, if x{4} = "N" then "Not Shipped Amount" else "Shipped Amount", x{5} * x{6}}},
sort = List.Sort(Source[Fruits]),
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Types = Table.TransformColumnTypes(
Source,
{
{"Quantity1", Int64.Type},
{"Price1", Int64.Type},
{"Shipped2", type text},
{"Quantity2", Int64.Type},
{"Price2", Int64.Type}
}
),
result = Table.FromRows(List.Combine(Table.ToList(Source, f)), {"Fruits", "Shipped", "Amound"}),
Pivot = Table.Pivot(
result,
List.Distinct(result[Fruits]),
"Fruits",
"Amound",
(x) => List.Sum(x & {0})
),
Reorder = Table.ReorderColumns(Pivot, sort),
AddColtotal = Table.AddColumn(Reorder, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
Sort = Table.Sort(AddColtotal, {{"Shipped", Order.Descending}}),
Headers = Table.DemoteHeaders(Sort),
Transpose = Table.Transpose(Headers),
AddColumntotals = Table.AddColumn(
Transpose,
"Total",
each try List.Sum(List.Skip(Record.ToList(_))) otherwise "Total"
),
Transpose2 = Table.Transpose(AddColumntotals),
PromoteHeaders = Table.PromoteHeaders(Transpose2, [PromoteAllScalars = true])
in
PromoteHeadersPower Query solution 9 for Calculate Shipped Fruit Value, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = (x) => {"Shipped Amount", "Not Shipped Amount"}{Byte.From(x = "N")},
C = List.Transform,
D = List.TransformMany(
Table.ToRows(A),
each List.Split(List.Skip(_), 3),
(x, y) => {B(y{0}), x{0}, y{1} * y{2}}
),
E = Table.FromRows(D, {"Status", "x", "y"}),
F = Table.Sort(
Table.Pivot(E, List.Sort(List.Distinct(E[x])), "x", "y", each List.Sum(_) ?? 0),
{"Status", 1}
),
G = Table.FromRows(
C(
Table.ToRows(F) & {{"Total"} & C(List.Skip(Table.ToColumns(F)), List.Sum)},
each _ & {List.Sum(List.Skip(_))}
),
Table.ColumnNames(F) & {"Total"}
)
in
GPower Query solution 10 for Calculate Shipped Fruit Value, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GetTable = Table.Combine(
List.Transform(
List.Transform(
List.Split(List.Skip(Table.ToColumns(Source)), 3),
each {Table.Column(Source, Table.ColumnNames(Source){0})} & _
),
each Table.FromColumns(_)
)
),
InsSales = Table.AddColumn(GetTable, "Sales", each [Column3] * [Column4], type number)[
[Column1],
[Column2],
[Sales]
],
PivotCol = Table.Pivot(
InsSales,
List.Sort(List.Distinct(InsSales[Column1])),
"Column1",
"Sales",
List.Sum
),
RenCols = Table.RenameColumns(PivotCol, {{"Column2", "Shipped"}}),
ReplValue = Table.Sort(
Table.ReplaceValue(
RenCols,
each [Shipped],
each if [Shipped] = "Y" then "Shipped Amount" else "Not Shipped Amount",
Replacer.ReplaceText,
{"Shipped"}
),
{{"Shipped", Order.Descending}}
),
AddRowsTotal = Table.AddColumn(ReplValue, "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 11 for Calculate Shipped Fruit Value, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToLs = List.Transform(
Table.ToRows(Source),
each {List.Repeat({_{0}}, List.Count(List.Split(List.Skip(_), 3)))}
& Table.ToColumns(Table.FromRows(List.Split(List.Skip(_), 3)))
),
ToTb = Table.Combine(
List.Transform(ToLs, each Table.FromColumns(_, {"Fruits", "Status", "Quantity", "Price"}))
),
AmClc = Table.AddColumn(ToTb, "Amount", each [Quantity] * [Price], type number),
ShAm = Table.Sort(
Table.TransformColumns(
AmClc,
{{"Status", each if _ = "Y" then "Shipped Amount" else "Not Shipped Amount", type text}}
)[[Status], [Fruits], [Amount]],
{{"Fruits", Order.Ascending}, {"Status", Order.Descending}}
),
Pvt = Table.Sort(
Table.Pivot(ShAm, List.Distinct(ShAm[Fruits]), "Fruits", "Amount", each List.Sum(_) ?? 0),
{{"Status", Order.Descending}}
),
#"Added Custom" = Table.AddColumn(Pvt, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
Res = Table.FromRows(
Table.ToRows(#"Added Custom")
& {{"Total"} & List.Transform(List.Skip(Table.ToColumns(#"Added Custom")), List.Sum)},
Table.ColumnNames(#"Added Custom")
)
in
ResSolving the challenge of Calculate Shipped Fruit Value with Excel
Excel solution 1 for Calculate Shipped Fruit Value, proposed by Bo Rydobon 🇹🇭:
=LET(
v,
VSTACK(
B2:D7,
E2:G7
),
PIVOTBY(
IF(
TAKE(
v,
,
1
)="Y",
"",
"Not "
)&"Shipped Amount",
VSTACK(
A2:A7,
A2:A7
),
BYROW(
v,
PRODUCT
),
SUM,
,
,
-1
)
)Excel solution 2 for Calculate Shipped Fruit Value, proposed by Rick Rothstein:
=LET(t,
TRANSPOSE(SORT(HSTACK(A2:A7,
C2:C7*D2:D7*(B2:B7={"Y",
"N"})+F2:F7*G2:G7*(E2:E7={"Y",
"N"})))),
v,
VSTACK(
t,
BYCOL(
t,
SUM
)
),
HSTACK(
{"Status";"Shipped Amount";"Not Shipped Amount";"Total"},
v,
VSTACK(
"Total",
DROP(
BYROW(
v,
SUM
),
1
)
)
))Excel solution 3 for Calculate Shipped Fruit Value, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
A2:G7,
t,
WRAPCOLS(
TOCOL(
CHOOSECOLS(
d,
1,
1,
2,
5,
3,
6,
4,
7
),
,
1
),
ROWS(
d
)*2
),
PIVOTBY(
REPT(
"Not ",
INDEX(
t,
,
2
)="n"
)&"Shipped Amount",
TAKE(
t,
,
1
),
BYROW(
t,
PRODUCT
),
SUM,
,
,
-1
)
)Excel solution 4 for Calculate Shipped Fruit Value, proposed by Kris Jaganah:
=LET(
a,
A2:A7,
b,
TOCOL(
B2:G7
),
c,
BYROW(
WRAPROWS(
TOCOL(
--b,
3
),
2
),
PRODUCT
),
d,
IF(
FILTER(
b,
ISTEXT(
b
)
)="Y",
"",
"Not "
)&"Shipped Amount",
e,
TOCOL(
HSTACK(
a,
a
)
),
PIVOTBY(
d,
TOCOL(
HSTACK(
a,
a
)
),
c,
SUM,
,
,
-1
)
)Excel solution 5 for Calculate Shipped Fruit Value, proposed by Julian Poeltl:
=LET(
& W,
WRAPROWS(
TOROW(
B2:G7
),
3
),
P,
PIVOTBY(
TAKE(
W,
,
1
),
DROP(
REDUCE(
0,
A2:A7,
LAMBDA(
A,
B,
VSTACK(
A,
B,
B
)
)
),
1
),
BYROW(
DROP(
W,
,
1
),
PRODUCT
),
SUM
),
S,
VSTACK(
HSTACK(
"Status",
DROP(
TAKE(
P,
1
),
,
1
)
),
HSTACK(
"Shipped Amount",
DROP(
CHOOSEROWS(
P,
3
),
,
1
)
),
HSTACK(
"Not Shipped Amount",
DROP(
CHOOSEROWS(
P,
2
),
,
1
)
),
TAKE(
P,
-1
)
),
IF(
S="",
0,
S
)
)Excel solution 6 for Calculate Shipped Fruit Value, proposed by Oscar Mendez Roca Farell:
=LET(
t,
TRANSPOSE(
WRAPCOLS(
TOROW(
B2:G7
),
3
)
),
PIVOTBY(
IF(
TAKE(
t,
,
1
)="N",
"Not ",
""
)&"Shipped Amount",
TOCOL(
REPT(
A2:A7,
{1,
1}
)
),
BYROW(
DROP(
t,
,
1
),
PRODUCT
),
SUM,
,
,
-1
)
)Excel solution 7 for Calculate Shipped Fruit Value, proposed by Duy Tùng:
=LET(
I,
INDEX,
a,
VSTACK(
A2:D7,
HSTACK(
A2:A7,
E2:G7
)
),
b,
PIVOTBY(
IF(
I(
a,
,
2
)="Y",
"",
"Not "
)&"Shipped Amount",
I(
a,
,
1
),
I(
a,
,
3
)*I(
a,
,
4
),
SUM,
,
,
-1
),
IF(
TAKE(
b,
1
)&TAKE(
b,
,
1
)="",
"Status",
b
)
)Excel solution 8 for Calculate Shipped Fruit Value, proposed by Sunny Baggu:
=LET(
_a,
C2:C7 * D2:D7 * (B2:B7 = B2) + F2:F7 * G2:G7 * (E2:E7 = B2),
_b,
C2:C7 * D2:D7 * (B2:B7 = B3) + F2:F7 * G2:G7 * (E2:E7 = B3),
HSTACK(
{"Status"; "Shipped Amount"; "Not Shipped Amount"; "Total"},
TRANSPOSE(
VSTACK(
SORT(
HSTACK(
A2:A7,
_a,
_b,
_a + _b
)
),
HSTACK(
"Total",
SUM(
_a
),
SUM(
_b
),
SUM(
_a + _b
)
)
)
)
)
)Excel solution 9 for Calculate Shipped Fruit Value, proposed by Md. Zohurul Islam:
=LET(
a,
A2:A7,
b,
B2:D7,
c,
E2:G7,
rng,
HSTACK(
VSTACK(
a,
a
),
VSTACK(
b,
c
)
),
d,
MAP(
CHOOSECOLS(
rng,
3
),
TAKE(
rng,
,
-1
),
LAMBDA(
x,
y,
x*y
)
),
e,
MAP(
CHOOSECOLS(
rng,
2
),
LAMBDA(
x,
IF(
x="Y",
"Shipped Amount",
"Not Shipped Amount"
)
)
),
f,
PIVOTBY(
e,
TAKE(
rng,
,
1
),
d,
SUM,
0,
1,
,
1
),
g,
CHOOSEROWS(
IF(
f="",
0,
f
),
1,
3,
2,
4
),
h,
VSTACK(
"Status",
DROP(
TAKE(
g,
,
1
),
1
)
),
j,
HSTACK(
h,
DROP(
g,
,
1
)
),
k,
CHOOSECOLS(
j,
1,
2,
3,
6,
5,
7,
4,
8
),
k
)Excel solution 10 for Calculate Shipped Fruit Value, proposed by Hamidi Hamid:
=LET(
s,
HSTACK(
VSTACK(
A2:A7,
A2:A7
),
VSTACK(
B2:D7,
E2:G7
)
),
t,
PIVOTBY(
TAKE(
s,
,
1
),
CHOOSECOLS(
s,
2
),
CHOOSECOLS(
s,
3
)*TAKE(
s,
,
-1
),
SUM
),
HSTACK(
A13:A16,
DROP(
SORT(
HSTACK(
{1,
3,
2,
4},
TRANSPOSE(
t
)
),
1,
1,
0
),
,
2
)
)
)Excel solution 11 for Calculate Shipped Fruit Value, proposed by Asheesh Pahwa:
=LET(
c,
HSTACK(
B2:B7&"-"&C2:C7*D2:D7,
E2:E7&"-"&F2:F7*G2:G7
),
r,
IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
6
),
LAMBDA(
x,
y,
HSTACK(
x,
LET(
I,
INDEX(
c,
y,
),
t,
TOCOL(
I
),
ta,
--TEXTAFTER(
t,
"-"
),
tb,
TEXTBEFORE(
t,
"-"
),
f,
SUM(
FILTER(
ta,
tb="Y",
0
)
),
fl,
SUM(
FILTER(
ta,
tb="N",
0
)
),
VSTACK(
f,
fl
)
)
)
)
),
,
1
),
""
),
b,
BYROW(
r,
LAMBDA(
x,
SUM(
x
)
)
),
IFNA(
VSTACK(
HSTACK(
A14:A15,
r,
b
),
HSTACK(
A16,
BYCOL(
r,
LAMBDA(
x,
SUM(
x
)
)
)
)
),
SUM(
b
)
)
)Excel solution 12 for Calculate Shipped Fruit Value, proposed by ferhat CK:
=LET(
a,
VSTACK(
A2:D7,
HSTACK(
A2:A7,
E2:G7
)
),
b,
PIVOTBY(
CHOOSECOLS(
a,
2
),
TAKE(
a,
,
1
),
BYROW(
TAKE(
a,
,
-2
),
PRODUCT
),
SUM,
,
,
-1
),
IFS(
b="Y",
"Shipped Amount",
b="N",
"Not Shipped Amount",
b>0,
b
)
)Excel solution 13 for Calculate Shipped Fruit Value, proposed by Jaroslaw Kujawa:
=LET(
a ;
DROP(
REDUCE(
"";
A2:A7;
LAMBDA(
a;
x;
VSTACK(
a;
HSTACK(
VSTACK(
x;
x
);
WRAPROWS(
OFFSET(
x;
0;
1;
1;
6
);
3
)
)
)
)
);
1
);
ch;
CHOOSECOLS;
b;
HSTACK(
a;
ch(
a;
3
)*ch(
a;
4
)
);
c;
ch(
b;
{1;
2;
5}
);
PIVOTBY(
ch(
c;
2
);
TAKE(
c ;
;
1
);
TAKE(
c ;
;
-1
);
SUM
)
)Excel solution 14 for Calculate Shipped Fruit Value, proposed by Imam Hambali:
=LET(
cc,
CHOOSECOLS,
c,
VSTACK(
A2:D7,
HSTACK(
A2:A7,
E2:G7
)
),
PIVOTBY(
IF(
cc(
c,
2
)="Y",
"Shipped Amount",
"Not Shipped Amount"
),
cc(
c,
1
),
cc(
c,
3
)*cc(
c,
4
),
SUM,
,
,
-1
)
)Excel solution 15 for Calculate Shipped Fruit Value, proposed by Milan Shrimali:
=let(a,
arrayformula(
split(
tocol(
BYCOL(
B2:G7,
lambda(
x,
ARRAYFORMULA(
A2:A7&"-"&x
)
)
)
),
"-"
)
),
wrap,
WRAPROWS(
tocol(
a,
,
0
),
6
),
main,
sort(
filter(
wrap,
{1,
1,
0,
1,
0,
1}
),
1,
1,
2,
0
),
fnl,
Filter(hstack(main,
byrow(main,
lambda(x,
choosecols(
x,
3
)*(choosecols(
x,
4
))))),
{1,
1,
0,
0,
1}),
header,
transpose(
sort(
UNIQUE(
choosecols(
fnl,
1
)
),
1,
-1
)
),
fnltbl,
iferror(vstack(header,
bycol(header,
lambda(X,
sum(torow(filter(choosecols(
fnl,
3
),
(choosecols(
fnl,
1
)=x)*(choosecols(
fnl,
2
)="y")))))),
bycol(header,
lambda(X,
sum(torow(filter(choosecols(
fnl,
3
),
(choosecols(
fnl,
1
)=x)*(choosecols(
fnl,
2
)="n"))))))),
0),
withtotal,
vstack(
fnltbl,
bycol(
fnltbl,
lambda(
x,
sum(
x
)
)
)
),
HSTACK(
VSTACK(
"Status",
"Shipped amount",
"Not Shipped amount",
"Total"
),
withtotal,
byrow(
withtotal,
lambda(
x,
if(
isnumber(
x
),
sum(
x
),
"Total"
)
)
)
))Solving the challenge of Calculate Shipped Fruit Value with Python
Python solution 1 for Calculate Shipped Fruit Value, proposed by Luan Rodrigues:
import pandas as pd
import math
file = "PQ_Challenge_233.xlsx"
df = pd.read_excel(file,usecols="A:G",nrows=6)
def split_list(lst, contar):
a = [lst[i:i + contar] for i in range(0, len(lst), contar)]
b = ['Shipped Amount' if i[0] == 'Y' else 'Not Shipped Amount' for i in a]
c = [math.prod(i[1:]) for i in a]
return list(zip(b,c))
df['Lista'] = df.apply(lambda row: split_list(row.values.tolist()[1:],3) , axis=1).tolist()
df_fim = df.explode(['Lista'])
df_fim[['Status', 'Valor']] = pd.DataFrame(df_fim['Lista'].tolist(), index=df_fim.index)
df_res = df_fim[['Fruits', 'Status', 'Valor']]
pivot = df_res.pivot_table(values='Valor', index='Status', columns='Fruits', aggfunc='sum')
pivot = pivot.fillna(0)
pivot['Total'] = pivot.values.tolist()
pivot['Total'] = pivot['Total'].apply(sum)
total = pivot.sum(axis=0)
pivot.loc['Total'] = total
print(pivot)
Solving the challenge of Calculate Shipped Fruit Value with Python in Excel
Python in Excel solution 1 for Calculate Shipped Fruit Value, proposed by Alejandro Campos:
df = xl("A1:G7", headers=True)
df['Shipped_Amount'] = df['Quantity1'] * df['Price1'] * (df['Shipped1'] == 'Y') + df['Quantity2'] * df['Price2'] * (df['Shipped2'] == 'Y')
df['Non_Shipped_Amount'] = df['Quantity1'] * df['Price1'] * (df['Shipped1'] == 'N') + df['Quantity2'] * df['Price2'] * (df['Shipped2'] == 'N')
summary_df = pd.DataFrame({
'Status': ['Shipped Amount', 'Not Shipped Amount', 'Total'],
**{fruit: [
df.loc[i, 'Shipped_Amount'],
df.loc[i, 'Non_Shipped_Amount'],
df.loc[i, 'Shipped_Amount'] + df.loc[i, 'Non_Shipped_Amount']
] for i, fruit in zip([0, 4, 1, 5, 2, 3], ['Apple', 'Banana', 'Papaya', 'Mango', 'Pineapple', 'Kiwi'])},
'Total': [df['Shipped_Amount'].sum(), df['Non_Shipped_Amount'].sum(), df['Shipped_Amount'].sum() + df['Non_Shipped_Amount'].sum()]
})
summary_df
Solving the challenge of Calculate Shipped Fruit Value with R
R solution 1 for Calculate Shipped Fruit Value, proposed by Konrad Gryczan, PhD<&/strong>:
library(tidyverse)
library(readxl)
library(janitor)
path = "Power Query/PQ_Challenge_233.xlsx"
input = read_excel(path, range = "A1:G7")
test = read_excel(path, range = "A13:H16")
result = input %>%
pivot_longer(-c(1), names_to = c(".value", "number"), names_pattern = "([A-Za-z]+)(\d)") %>%
mutate(amount = Price * Quantity) %>%
summarise(amount = sum(amount), .by = c("Fruits", "Shipped")) %>%
mutate(Status = recode(Shipped, "Y" = "Shipped Amount", "N" = "Not Shipped Amount")) %>%
select(-Shipped) %>%
pivot_wider(names_from = Fruits, values_from = amount, values_fill = 0) %>%
adorn_totals("both") %>%
select(Status, Apple, Banana, Papaya, Mango, Pineapple, Kiwi, Total)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&