Transpose the table as shown by showing the amount paid each month. Amount paid = Amt * number appearing the column. Also show row and column totals.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 217
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Monthly Payments with Power Query
Power Query solution 1 for Calculate Monthly Payments, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
M = {"Month"},
T = {"Total"},
P = List.Zip(
List.Transform(
Table.ToRows(Source),
each
let
r = List.Transform(List.Skip(_, 2), (v) => v * _{1})
in
r & {List.Sum(r)}
)
),
S = Table.FromRows(
List.Transform(
List.Positions(P),
each {(Table.ColumnNames(Source) & T){_ + 2}} & P{_} & {List.Sum(P{_})}
),
M & Source[Customer] & T
)
in
SPower Query solution 2 for Calculate Monthly Payments, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Total",
each [a = Record.FieldValues(_), b = List.Sum(List.Transform(List.Skip(a, 2), each _ * a{1}))][
b
]
),
C = Table.UnpivotOtherColumns(B, {"Customer", "Amt"}, "Month", "Va"),
D = Table.AddColumn(C, "New", each if [Month] = "Total" then [Va] else [Amt] * [Va])[
[Customer],
[Month],
[New]
],
E = Table.Pivot(D, List.Distinct(D[Customer]), "Customer", "New"),
F = Table.AddColumn(E, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
G = Table.Sort(F, {each List.PositionOf(C[Month], [Month]), 0})
in
GPower Query solution 3 for Calculate Monthly Payments, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Unpivot = Table.UnpivotOtherColumns ( Source, { "Customer", "Amt" }, "Month", "V" ),
Amount = Table.AddColumn ( Unpivot, "A", each [Amt] * [V] ),
Remove = Table.RemoveColumns ( Amount, { "Amt", "V" } ),
Group1 = Table.Group (
Remove,
"Customer",
{ { "Month", each "Total" }, { "A", each List.Sum ( [A] ) } }
),
Combine = Remove & Group1,
Pivot = Table.Pivot ( Combine, Source[Customer], "Customer", "A" ),
Total = Table.AddColumn ( Pivot, "Total", each List.Sum ( List.Skip ( Record.ToList ( _ ) ) ) ),
Return = Table.Sort ( Total, each try Date.From ( [Month] & "1" ) otherwise hashtag#infinity )
in
Return
Power Query solution 4 for Calculate Monthly Payments, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Rep = Table.RemoveColumns(Table.ReplaceValue(Source, each _, each _,
(x,y,z)=> x*y[Amt], List.Skip(Table.ColumnNames(Source),2)), "Amt"),
List = Table.ToRows(Table.DemoteHeaders(Rep)),
Tbl = Table.PromoteHeaders(Table.FromColumns({{"Month"}&List.Skip(List{0})&{"Total"}}&
List.Transform({1..List.Count(List)-1},
each List{_}&{List.Sum(List.Skip(List{_}))}))),
Sol = Table.AddColumn(Tbl, "Total", each List.Sum(List.Skip(Record.ToList(_))))
in
Sol
Show translation
Show translation of this commentPower Query solution 5 for Calculate Monthly Payments, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Months = List.Skip(Table.ColumnNames(Source), 2),
List = {{"Month"} & Months & {"Total"}}
& (
Table.AddColumn(
Source,
"A",
each
let
a = Record.ToList(_),
b = List.Transform({2 .. List.Count(a) - 1}, each a{_} * a{1}),
c = {List.First(a)} & b & {List.Sum(b)}
in
c
)[A]
),
Tbl = Table.PromoteHeaders(Table.FromColumns(List)),
Sol = Table.AddColumn(Tbl, "Total", each List.Sum(List.Skip(Record.ToList(_))))
in
SolPower Query solution 6 for Calculate Monthly Payments, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = {{"Month"} & List.RemoveFirstN(Table.ColumnNames(Fonte), 2) & {"Total"}}
& Table.AddColumn(
Fonte,
"Personalizar",
each
let
a = List.Transform(List.RemoveFirstN(Record.FieldValues(_), 2), (x) => [Amt] * x),
b = {[Customer]} & a & {List.Sum(a)}
in
b
)[Personalizar],
tab = Table.PromoteHeaders(Table.FromColumns(add)),
res = Table.AddColumn(tab, "Total", each List.Sum(List.RemoveFirstN(Record.FieldValues(_), 1)))
in
resPower Query solution 7 for Calculate Monthly Payments, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformRows(
Source,
each [
a = Record.ToList(_),
b = List.Transform(List.Skip(a, 2), each _ * a{1}),
c = b & {List.Sum(b)}
][c]
),
ToTable = Table.FromColumns(
{List.Skip(Table.ColumnNames(Source), 2) & {"Total"}} & Transform,
{"Month"} & Source[Customer]
),
Result = Table.AddColumn(ToTable, "Total", each List.Sum(List.Skip(Record.ToList(_))))
in
ResultPower Query solution 8 for Calculate Monthly Payments, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData217"]}[Content],
TRows = Table.TransformRows(
Source,
(r) =>
let
L = List.Transform(List.Skip(Record.ToList(r), 2), each _ * r[Amt])
in
L & {List.Sum(L)}
),
Result = Table.FromColumns(
{List.Skip(Table.ColumnNames(Source), 2) & {"Total"}} & TRows,
{"Month"} & Source[Customer]
)
in
ResultPower Query solution 9 for Calculate Monthly Payments, proposed by Albert Cid Cañigueral:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.AddColumn(
Origen,
"Total",
each List.Transform(List.Skip(Record.ToList(_), 2), (x) => x * [Amt])
)[Total],
b = Table.FromColumns(a),
c = b & Table.FromRows({List.Transform(a, each List.Sum(_))}),
d = Table.AddColumn(c, "Total", each List.Sum(Record.ToList(_))),
e = Table.FromColumns(
{List.Skip(Table.ColumnNames(Origen), 2) & {"Total"}} & Table.ToColumns(d),
{"Month"} & Origen[Customer] & {"Total"}
)
in
ePower Query solution 10 for Calculate Monthly Payments, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.UnpivotOtherColumns(Source, {"Customer", "Amt"}, "M", "C"),
B = Table.AddColumn(A, "Am", each [Amt] * [C], type number),
C = Table.SelectColumns(B, {"Customer", "M", "Am"}),
D = Table.Pivot(C, List.Distinct(C[Customer]), "Customer", "Am", List.Sum),
E = Table.Sort(
Table.AddColumn(
D,
"Total",
each List.Sum(List.Select(Record.ToList(_), each Value.Is(_, Number.Type)))
),
{each List.PositionOf(Table.ColumnNames(Source), [M])}
),
F = Table.AddIndexColumn(E, "I", 1, 1, Int64.Type),
G = Table.Group(
F,
{},
{
{"Karen", each List.Sum([Karen]), type nullable number},
{"Shirley", each List.Sum([Shirley]), type nullable number},
{"Lawrence", each List.Sum([Lawrence]), type nullable number},
{"Christian", each List.Sum([Christian]), type nullable number},
{"Total", each List.Sum([Total]), type number}
}
),
H = Table.AddColumn(G, "M", each "Total"),
I = Table.AddColumn(H, "I", each List.Max(F[I]) + 1),
J = Table.Combine({F, I}),
K = Table.RemoveColumns(J, {"I"})
in
KPower Query solution 11 for Calculate Monthly Payments, proposed by Ahmed Ariem:
Alejandro Simón 🇵🇦 🇪🇸
=
Table.PromoteHeaders(Table.FromColumns({{"Month"}&List.Skip(List{0})&{"Total"}}&
List.Transform({1..List.Count(List)-1},
each List{_}&{List.Sum(List.Skip(List{_},1))})))
Power Query solution 12 for Calculate Monthly Payments, proposed by Ahmed Ariem:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Types = Table.TransformColumnTypes(
Source,
{
{"Customer", type text},
{"Amt", Int64.Type},
{"Jan", Int64.Type},
{"Feb", Int64.Type},
{"Mar", Int64.Type},
{"Apr", Int64.Type},
{"May", Int64.Type},
{"Jun", Int64.Type}
}
),
Replace = Table.ReplaceValue(
Types,
(x) => x,
(x) => x,
(x, y, z) => x * y[Amt],
Table.ColumnNames(Types)
),
AddColumn1 = Table.AddColumn(Replace, "Total", each List.Sum(List.Skip(Record.ToList(_), 2))),
RemoveCol = Table.RemoveColumns(AddColumn1, {"Amt"}),
Unpivot = Table.UnpivotOtherColumns(RemoveCol, {"Customer"}, "Month", "value"),
Pivot = Table.Pivot(Unpivot, List.Distinct(Unpivot[Customer]), "Customer", "value"),
AddColumn = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.ToList(_), 1)))
in
AddColumnPower Query solution 13 for Calculate Monthly Payments, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S1 = Table.ToRows(Source),
S2 = List.Transform(S1, each List.Skip(_)),
S3 = List.Transform(S2, each List.Transform(_, (a) => a * _{0})),
S4 = {List.Skip(Table.ColumnNames(Source), 2) & {"Total"}}
& List.Transform(S3, each List.Combine({List.Skip(_), {List.Sum(List.Skip(_))}})),
S6 = Table.FromColumns(S4, {"Month"} & Source[Customer]),
S7 = Table.AddColumn(S6, "Total", each [Karen] + [Shirley] + [Lawrence] + [Christian])
in
S7Power Query solution 14 for Calculate Monthly Payments, proposed by Gertjan Davies:
let
Source = Problem,
Unpivot = Table.UnpivotOtherColumns(Source, {"Customer", "Amt"}, "Month", "Value"),
Cust_Month_Total = Table.AddColumn(Unpivot, "CM_Total", each [Amt] * [Value]),
Relevant = Table.SelectColumns(Cust_Month_Total, {"Month", "Customer", "CM_Total"}),
Month_Total = Table.Group(
Relevant,
{"Month"},
{{"Customer", each "Total"}, {"CM_Total", each List.Sum([CM_Total]), type number}}
),
Add_MT = Relevant & Month_Total,
Totalize = Add_MT
& Table.Group(
Add_MT,
{"Customer"},
{{"Month", each "Total", type text}, {"CM_Total", each List.Sum([CM_Total]), type number}}
),
Pivot = Table.Pivot(Totalize, List.Distinct(Totalize[Customer]), "Customer", "CM_Total", List.Sum),
// Dont understand why pivot scrambles sorting?
Mnumber = Table.AddColumn(
Pivot,
"Custom",
each try Date.Month(Date.FromText([Month], [Format = "MMM"])) otherwise 99
),
Sort = Table.Sort(Mnumber, {{"Custom", Order.Ascending}}),
Clean = Table.RemoveColumns(Sort, {"Custom"})
in
CleanPower Query solution 15 for Calculate Monthly Payments, proposed by Amit Rathi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
UnpivotedData = Table.UnpivotOtherColumns(Source, {"Customer", "Amt"}, "Month", "Value"),
AddAmountPaid = Table.AddColumn(UnpivotedData, "Amount Paid", each [Amt] * [Value], type number),
RemoveOriginalValueColumn = Table.RemoveColumns(AddAmountPaid, {"Value"}),
PivotedTable = Table.Pivot(
RemoveOriginalValueColumn,
List.Distinct(RemoveOriginalValueColumn[Month]),
"Month",
"Amount Paid",
List.Sum
),
#"Changed Type" = Table.TransformColumnTypes(
PivotedTable,
{
{"Customer", type text},
{"Amt", Int64.Type},
{"Jan", Int64.Type},
{"Feb", Int64.Type},
{"Mar", Int64.Type},
{"Apr", Int64.Type},
{"May", Int64.Type},
{"Jun", Int64.Type}
}
),
AddTotalColumn = Table.AddColumn(
#"Changed Type",
"Total",
each List.Sum(List.Select(Record.FieldValues(_), each _ is number)),
Int64.Type
),
AddTotalRow = Table.InsertRows(
AddTotalColumn,
Table.RowCount(AddTotalColumn),
{
[Customer = "Total"]
& Record.FromList(
List.Transform(
List.RemoveFirstN(Table.ToColumns(AddTotalColumn), 1),
each List.Sum(List.Select(_, each _ is number))
),
List.RemoveFirstN(Table.ColumnNames(AddTotalColumn), 1)
)
}
)
in
AddTotalRowSolving the challenge of Calculate Monthly Payments with Excel
Excel solution 1 for Calculate Monthly Payments, proposed by Bo Rydobon 🇹🇭:
=TRANSPOSE(
HSTACK(
VSTACK(
"Month",
A2:A5,
"Total"
),
VSTACK(
HSTACK(
C1:H1,
"Total"
),
REDUCE(
C2:H5*B2:B5,
{0,
1},
LAMBDA(
m,
_,
TRANSPOSE(
HSTACK(
m,
BYROW(
m,
SUM
)
)
)
)
)
)
)
)
⛳️
=LET(
H,
HSTACK,
V,
VSTACK,
R,
TRANSPOSE,
L,
LAMBDA(
m,
R(
H(
m,
BYROW(
m,
SUM
)
)
)
),
c,
"Total",
R(
H(
V(
"Month",
A2:A5,
c
),
V(
H(
C1:H1,
c
),
L(
L(
C2:H5*B2:B5
)
)
)
)
)
)Excel solution 2 for Calculate Monthly Payments, proposed by Rick Rothstein:
=LET(
g,
B2:B5*C2:H5,
h,
HSTACK(
g,
BYROW(
g,
LAMBDA(
r,
SUM(
r
)
)
)
),
TRANSPOSE(
VSTACK(
HSTACK(
"Month",
C1:H1,
"Total"
),
HSTACK(
VSTACK(
A2:A5,
"Total"
),
VSTACK(
h,
BYCOL(
h,
LAMBDA(
c,
SUM(
c
)
)
)
)
)
)
)
)Excel solution 3 for Calculate Monthly Payments, proposed by محمد حلمي:
=LET(
i,
TRANSPOSE(
C2:H5*B2:B5
),
s,
LAMBDA(
a,
SUM(
a
)
),
j,
"Total",
VSTACK(
& HSTACK(
"Month",
TOROW(
A2:A5
),
j
),
HSTACK(
TOCOL(
C1:H1
),
i,
BYROW(
i,
s
)
),
HSTACK(
j,
BYCOL(
i,
s
),
SUM(
i
)
)
)
)Excel solution 4 for Calculate Monthly Payments, proposed by محمد حلمي:
=LET(
i,
C2:H5*B2:B5,
s,
LAMBDA(
a,
SUM(
a
)
),
j,
"Total",
TRANSPOSE(
HSTACK(
VSTACK(
"Month",
A2:A5,
j
),
VSTACK(
C1:H1,
i,
BYCOL(
i,
s
)
),
VSTACK(
j,
BYROW(
i,
s
),
SUM(
i
)
)
)
)
)Excel solution 5 for Calculate Monthly Payments, proposed by Julian Poeltl:
=LET(
C,
TRANSPOSE(
B2:B5*C2:H5
),
VSTACK(
HSTACK(
"Month",
TOROW(
A2:A5
),
"Total"
),
HSTACK(
TOCOL(
C1:H1
),
C,
BYROW(
C,
SUM
)
),
HSTACK(
"Total",
BYCOL(
C,
SUM
),
SUM(
C
)
)
)
)Excel solution 6 for Calculate Monthly Payments, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_cust,
A2:A5,
_mnth,
C1:H1,
_amt,
B2:B5,
_tms,
C2:H5,
_calc,
PIVOTBY(
TOCOL(
IFNA(
_mnth,
_cust
)
),
TOCOL(
IFNA(
_cust,
_mnth
)
),
TOCOL(
_amt * _tms
),
SUM
),
_sort1,
SORTBY(_calc,
--(INDEX(
_calc,
,
1
) & 1)),
_r,
SORTBY(
_sort1,
HSTACK(
0,
DROP(
XMATCH(
TAKE(
_calc,
1
),
_cust
),
,
1
)
)
),
_r
)Excel solution 7 for Calculate Monthly Payments, proposed by Hussein SATOUR:
=LET(
f,
LAMBDA(
x,
y,
z,
IF(
x=1,
HSTACK(
y,
z
),
VSTACK(
y,
z
)
)
),
v,
TRANSPOSE(
B2:B5*C2:H5
),
tr,
BYROW(
v,
SUM
),
tc,
BYCOL(
f(
1,
v,
tr
),
SUM
),
t,
"Total",
m,
TOCOL(
f(
1,
C1:H1,
t
)
),
n,
TOROW(
f(
2,
"Month",
f(
2,
A2:A5,
t
)
)
),
f(
2,
n,
f(
1,
m,
f(
2,
f(
1,
v,
tr
),
tc
)
)
)
)Excel solution 8 for Calculate Monthly Payments, proposed by Oscar Mendez Roca Farell:
=LET(
t,
TRANSPOSE(
B2:B5*C2:H5
),
c,
A2:A5,
m,
C1:H1,
o,
"Total",
VSTACK(
HSTACK(
"Month",
TOROW(
c
),
o
),
HSTACK(
TOCOL(
m
),
t,
MMULT(
t,
1^N(
+c
)
)
),
HSTACK(
o,
MMULT(
1^N(
+m
),
t
),
SUM(
t
)
)
)
)
Alternatively:
=LET(
H,
HSTACK,
V,
VSTACK,
t,
TRANSPOSE(
B2:B5*C2:H5
),
c,
A2:A5,
m,
C1:H1,
o,
"Total",
V(
H(
"Month",
TOROW(
c
),
o
),
H(
TOCOL(
m
),
t,
MMULT(
t,
1^N(
+c
)
)
),
H(
o,
MMULT(
1^N(
+m
),
t
),
SUM(
t
)
)
)
)Excel solution 9 for Calculate Monthly Payments, proposed by Duy Tùng:
=LET(
V,
VSTACK,
H,
HSTACK,
a,
TRANSPOSE(
B2:B5*C2:H5
),
b,
V(
H(
TOROW(
A2:A5
),
"Total"
),
H(
a,
BYROW(
a,
SUM
)
)
),
H(
V(
"Month",
TOCOL(
C1:H1
),
"Total"
),
V(
b,
BYCOL(
b,
SUM
)
)
)
)Excel solution 10 for Calculate Monthly Payments, proposed by Sunny Baggu:
=LET(
_m,
TRANSPOSE(
C1:H1
),
_c,
TRANSPOSE(
A2:A5
),
_v,
TRANSPOSE(
C2:H5 * B2:B5
),
_d,
VSTACK(
_v,
BYCOL(
_v,
LAMBDA(
a,
SUM(
a
)
)
)
),
_r,
BYROW(
_d,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
"Month",
_c,
"Total"
),
HSTACK(
VSTACK(
_m,
"Total"
),
HSTACK(
_d,
_r
)
)
)
)Excel solution 11 for Calculate Monthly Payments, proposed by LEONARD OCHEA 🇷🇴:
=LET(
c,
A2:A5,
a,
B2:B5,
d,
C2:H5,
i,
C1:H1,
S,
SEQUENCE,
H,
HSTACK,
F,
LAMBDA(
x,
TOCOL(
IF(
d,
x,
z
),
3
)
),
DROP(
PIVOTBY(
H(
F(
S(
,
COLUMNS(
i
)
)
),
F(
i
)
),
H(
F(
S(
ROWS(
c
)
)
),
F(
c
)
),
F(
a
)*F(
d
),
SUM
),
1,
1
)
)
Another approach
=LET(
c,
A2:A5,
a,
B2:B5,
k,
C1:H1,
d,
C2:H5,
I,
TRANSPOSE,
H,
HSTACK,
s,
"Total",
r,
I(
a
)*I(
d
),
x,
BYROW(
r,
SUM
),
VSTACK(
H(
"Month",
I(
c
),
s
),
H(
I(
k
),
r,
x
),
H(
s,
BYCOL(
H(
r,
x
),
SUM
)
)
)
)Excel solution 12 for Calculate Monthly Payments, proposed by Md. Zohurul Islam:
=LET(
A,
A2:A5,
B,
C1:H1,
C,
B2:B5,
D,
C2:H5,
mnth,
TOCOL(
IFNA(
B,
A
)
),
cust,
TOCOL(
IFNA(
A,
B
)
),
amt,
TOCOL(
C * D
),
rng,
SORTBY(
HSTACK(
ABS(
1 & mnth
),
cust,
amt
),
ABS(
1 & mnth
),
1
),
rowData,
TAKE(
rng,
,
1
),
colData,
CHOOSECOLS(
rng,
2
),
values,
CHOOSECOLS(
rng,
3
),
ans,
PIVOTBY(
rowData,
colData,
values,
SUM,
0,
1
),
P,
VSTACK(
"Month",
DROP(
CHOOSECOLS(
ans,
1
),
1
)
),
Q,
CHOOSECOLS(
DROP(
ans,
,
1
),
2,
4,
3,
1,
5
),
Final,
HSTACK(
P,
Q
),
Final
)Excel solution 13 for Calculate Monthly Payments, proposed by Pieter de B.:
=LET(
b,
LAMBDA(
a,
TRANSPOSE(
HSTACK(
a,
BYROW(
a,
LAMBDA(
a,
SUM(
a
)
)
)
)
)
),
IFNA(
VSTACK(
HSTACK(
"Month",
TOROW(
A2:A5
)
),
HSTACK(
TOCOL(
C1:H1
),
b(
b(
TRANSPOSE(
C2:H5*B2:B5
)
)
)
)
),
"Total"
)
)Excel solution 14 for Calculate Monthly Payments, proposed by Pieter de B.:
=LET(
d,
A1:H5,
n,
TRANSPOSE(
DROP(
d,
1,
2
)*DROP(
INDEX(
d,
,
2
),
1
)
),
m,
MMULT(
n,
SEQUENCE(
4
)^0
),
x,
HSTACK(
n,
m
),
y,
MMULT(
SEQUENCE(
,
ROWS(
x
)
)^0,
x
),
HSTACK(
VSTACK(
"Month",
TOCOL(
DROP(
TAKE(
d,
1
),
,
2
)
),
"Total"
),
VSTACK(
HSTACK(
TOROW(
DROP(
TAKE(
d,
,
1
),
1
)
),
"Total"
),
x,
y
)
)
)Excel solution 15 for Calculate Monthly Payments, proposed by Hamidi Hamid:
=LET(
v,
HSTACK(
TRANSPOSE(
A2:A5
),
"Total"
),
e,
VSTACK(
"Month",
TRANSPOSE(
C1:H1
),
"Total"
),
x,
TRANSPOSE(
$C$2:$H$5*$B$2:$B$5
),
z,
BYROW(
x,
LAMBDA(
a,
SUM(
a
)
)
),
q,
HSTACK(
x,
z
),
w,
BYCOL(
q,
LAMBDA(
a,
SUM(
a
)
)
),
HSTACK(
e,
VSTACK(
v,
VSTACK(
q,
w
)
)
)
)Excel solution 16 for Calculate Monthly Payments, proposed by Asheesh Pahwa:
=LET(
c,
A2:A5,
m,
C1:H1,
a,
B2:B5,
n,
C2:H5,
p,
a*n,
t,
TRANSPOSE(
p
),
b,
BYROW(
t,
LAMBDA(
x,
SUM(
x
)
)
),
bc,
BYCOL(
t,
LAMBDA(
x,
SUM(
x
)
)
),
s,
SUM(
bc
),
IFNA(
VSTACK(
HSTACK(
"Month",
TOROW(
c
),
"Total"
),
HSTACK(
TOCOL(
m
),
t,
b
),
HSTACK(
"Total",
bc
)
),
s
)
)Excel solution 17 for Calculate Monthly Payments, proposed by ferhat CK:
=IFNA(LET(a,
TRANSPOSE(--(SEQUENCE(
,
6
)*0&B2:B5)*C2:H5),
b,
VSTACK(
a,
BYCOL(
a,
SUM
)
),
c,
HSTACK(
b,
BYROW(
b,
SUM
)
),
VSTACK(
REDUCE(
"Month",
A2:A5,
LAMBDA(
x,
y,
HSTACK(
x,
y
)
)
),
HSTACK(
TEXT(
"1."&SEQUENCE(
6
)&".2024",
"aaa"
),
c
)
)),
"Total")Excel solution 18 for Calculate Monthly Payments, proposed by Jaroslaw Kujawa:
=LET(
x;
A1:H5;
y;
TRANSPOSE(
x
);
z;
DROP(
y;
1;
1
);
v;
TAKE(
z;
1
)*TAKE(
z;
-ROWS(
z
)+1
);
cols;
HSTACK(
v;
BYROW(
v;
LAMBDA(
v;
SUM(
v
)
)
)
);
nums;
VSTACK(
cols;
BYCOL(
cols;
LAMBDA(
cols;
SUM(
cols
)
)
)
);
na_s;
VSTACK(
TAKE(
y;
1
);
HSTACK(
DROP(
TAKE(
y;
;
1
);
2
);
nums
)
);
IFNA(
IF(
na_s="Customer";
"Month";
na_s
);
"Total"
)
)_x000D_
Excel solution 19 for Calculate Monthly Payments, proposed by Albert Cid Cañigueral:
=LET(
f,
LAMBDA(
r,
TRANSPOSE(
r
)
),
c,
f(
C2:H5
)*f(
B2:B5
),
tf,
BYCOL(
c,
SUM
),
tc,
BYROW(
c,
SUM
),
HSTACK(
VSTACK(
"Month",
f(
C1:H1
),
"Total"
),
VSTACK(
f(
A2:A5
),
c,
tf
),
VSTACK(
"Total",
tc,
SUM(
tc
)
)
)
)_x000D_
_x000D_
Excel solution 20 for Calculate Monthly Payments, proposed by Andy Heybruch:
=LET(
_a,
TRANSPOSE(
C2:H5*B2:B5
),
_rt,
HSTACK(
_a,
BYROW(
_a,
SUM
)
),
_amts,
VSTACK(
_rt,
BYCOL(
_rt,
SUM
)
),
VSTACK(
HSTACK(
"Month",
TRANSPOSE(
A2:A5
),
"Total"
),
HSTACK(
VSTACK(
TRANSPOSE(
C1:H1
),
"Total"
),
_amts
)
)
)
_x000D_
_x000D_
Excel solution 21 for Calculate Monthly Payments, proposed by Ankur Sharma:
=LET(
a,
TRANSPOSE(
B2:B5 * C2:H5
),
b,
BYCOL(
a,
LAMBDA(
z,
SUM(
z
)
)
),
c,
BYROW(
a,
LAMBDA(
z,
SUM(
z
)
)
),
d,
SUM(
b
),
HSTACK(
VSTACK(
"Month",
TRANSPOSE(
C1:H1
),
"Total"
),
VSTACK(
TRANSPOSE(
A2:A5
),
a,
b
),
VSTACK(
"Total",
c,
d
)
)
)
_x000D_
_x000D_
Excel solution 22 for Calculate Monthly Payments, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=HSTACK(
VSTACK(
"Month",
TOCOL(
C1:H1
),
"Total"
),
VSTACK(
HSTACK(
TOROW(
A2:A5
),
"Total"
),
LET(
i,
VALUE(
TEXTSPLIT(
TEXTJOIN(
,
,
BYCOL(
C2:H5,
LAMBDA(
a,
TEXTJOIN(
",",
,
B2:B5*a
)&"/"
)
)
),
",",
"/",
TRUE
)
),
HSTACK(
VSTACK(
i,
BYCOL(
i,
LAMBDA(
x,
SUM(
x
)
)
)
),
BYROW(
VSTACK(
i,
BYCOL(
i,
LAMBDA(
x,
SUM(
x
)
)
)
),
LAMBDA(
y,
SUM(
y
)
)
)
)
)
)
)
_x000D_
_x000D_
Excel solution 23 for Calculate Monthly Payments, proposed by Imam Hambali:
=LET(
td,
TRANSPOSE(
B2:B5*C2:H5
),
fn,
LAMBDA(
x,
x(
td,
SUM
)
),
c,
fn(
BYCOL
),
VSTACK(
HSTACK(
VSTACK(
"Month",
TRANSPOSE(
C1:H1
)
),
VSTACK(
TRANSPOSE(
A2:A5
),
td
),
VSTACK(
"Total",
fn(
BYROW
)
)
),
HSTACK(
"Total",
c,
SUM(
c
)
)
)
)
_x000D_
_x000D_
Excel solution 24 for Calculate Monthly Payments, proposed by Gerson Pineda:
=LET(
r,
"Total",
t,
TRANSPOSE(
A1:H5
),
e,
DROP,
x,
TAKE,
h,
HSTACK,
a,
e(
t,
1,
1
),
b,
x(
a,
1
)*e(
a,
1
),
c,
h(
b,
BYROW(
b,
SUM
)
),
VSTACK(
h(
"Month",
x(
e(
t,
,
1
),
1
),
r
),
h(
x(
e(
t,
2
),
,
1
),
c
),
h(
r,
BYCOL(
c,
SUM
)
)
)
)
_x000D_
_x000D_
Excel solution 25 for Calculate Monthly Payments, proposed by RIJESH T.:
=LET(
no,
TRANSPOSE(
B2:B5*C2:H5
),
rowttl,
HSTACK(
no,
BYROW(
no,
LAMBDA(
b,
SUM(
b
)
)
)
),
colttl,
BYCOL(
rowttl,
LAMBDA(
a,
SUM(
a
)
)
),
HSTACK(
VSTACK(
"Month",
TOCOL(
C1:H1
),
"Total"
),
VSTACK(
HSTACK(
TRANSPOSE(
A2:A5
),
"Total"
),
rowttl,
colttl
)
)
)
_x000D_
_x000D_
Excel solution 26 for Calculate Monthly Payments, proposed by Songglod P.:
=LET(
amt,
TRANSPOSE(
B2:B5*C2:H5
),
s,
LAMBDA(
x,
SUM(
x
)
),
tc,
VSTACK(
amt,
BYCOL(
amt,
s
)
),
VSTACK(
HSTACK(
"Month",
TOROW(
A2:A5
),
"Total"
),
HSTACK(
VSTACK(
TOCOL(
C1:H1
),
"Total"
),
tc,
BYROW(
tc,
s
)
)
)
)
_x000D_
Solving the challenge of Calculate Monthly Payments with Python
_x000D_
Python solution 1 for Calculate Monthly Payments, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_217.xlsx"
input = pd.read_excel(path, usecols="A:H", nrows = 4)
test = pd.read_excel(path, usecols="J:O", nrows = 7)
input.iloc[:, 2:8] = input.iloc[:, 2:8].apply(lambda x: x * input["Amt"])
input = input.drop(columns=["Amt"])
input = input.T
input.columns = input.iloc[0]
input = input.drop(input.index[0])
input["Total"] = input.sum(axis=1)
input.loc["Total"] = input.sum()
input = input.reset_index().rename(columns={"index": "Month"}).rename_axis(None, axis=1)
print(all(input == test)) # True
_x000D_
Solving the challenge of Calculate Monthly Payments with Python in Excel
_x000D_
Python in Excel solution 1 for Calculate Monthly Payments, proposed by Alejandro Campos:
df = xl("A1:H5", headers=True).pipe(lambda d: d.assign(
**{col: d[col] * d['Amt'] for col in d.columns[1:]}))
.drop(columns='Amt').set_index('Customer').T
df['Total'] = df.sum(axis=1)
df.loc['Total'] = df.sum()
df.reset_index(inplace=True)
df.rename(columns={'index': 'Month'}, inplace=True)
df.columns.name = None
df
_x000D_
_x000D_
Python in Excel solution 2 for Calculate Monthly Payments, proposed by Abdallah Ally:
df = xl("A1:H5", headers=True)
# Perform data wrangling
values = df.apply(
lambda x: [x[i] if i < 2 else x[i] * x[1] for i in range(len(df.columns))],
axis=1
).tolist()
df = pd.DataFrame(data=values, columns=df.columns).drop(columns='Amt')
df['Total'] = df.apply(lambda x: sum(x[1:]), axis=1)
df = df.set_index('Customer').transpose().reset_index()
df['Total'] = df.apply(lambda x: sum(x[1:]), axis=1)
df = df.rename(columns={'index': 'Month'})
df.columns.name = ''
df
_x000D_
Solving the challenge of Calculate Monthly Payments with R
_x000D_
R solution 1 for Calculate Monthly Payments, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(janitor)
path = "Power Query/PQ_Challenge_217.xlsx"
input = read_excel(path, range = "A1:H5")
test = read_excel(path, range = "J1:O8")
result = input %>%
mutate(across(3:8, ~ . * Amt)) %>%
select(-Amt) %>%
t() %>%
as.data.frame() %>%
row_to_names(1) %>%
rownames_to_column(var = "Month") %>%
mutate(across(-Month, ~ as.numeric(.))) %>%
adorn_totals(c("row", "col"))
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
_x000D_
&