The provided question table contains information regarding the amount of time individuals spend in meetings with each other, and we want to generate a result table that displays the percentage of time each person (G3:G7) spends with others (H2:L2), ensuring that the sum of each row equals 100%. The highlighted cells are calculated by dividing the meeting duration between person D and person C (1:30) by the total meeting duration of person D (16:30).
📌 Challenge Details and Links
Challenge Number: 26
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Calculate Spending Time with Power Query
Power Query solution 1 for Calculate Spending Time, proposed by Ramiro Ayala Chávez:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
G = Table.Group,
T = Table.AddColumn,
R = Table.RenameColumns,
S = List.Sum,
a = Table.RemoveColumns(Source,"Date"),
b = G(a,{"Person 1"},{{"S", each S([Duration])}}),
c = R(G(a,{"Person 2"},{{"S", each S([Duration])}}),{"Person 2","Person 1"}),
d = G(b&c,{"Person 1"},{{"S", each S([S])}}),
e = Table.CombineColumns(a,{"Person 1","Person 2"},Combiner.CombineTextByDelimiter(""),"M"),
f = G(e,{"M"},{{"G", each S([Duration])}}),
g = Table.SplitColumn(f,"M",Splitter.SplitTextByRepeatedLengths(1),{"C1","C2"}),
h = List.Transform(Table.ToRows(g), each List.Reverse(_)),
i = R(Table.SelectColumns(Table.FromRows(h),{"Column2","Column3","Column1"}),{{"Column2","C1"},{"Column3","C2"},{"Column1","G"}}),
j = G(g&i,{"C1","C2"},{{"F", each S([G])}}),
k = T(j,"T",each d[S]{List.PositionOf(d[Person 1],[C1])}),
l = Table.Sort(T(k,"P", each Number.Round([F]/[T],2))[[C1],[C2],[P]],{{"C2",0}}),
m = Table.Pivot(l, List.Distinct(l[C2]),"C2","P"),
n = Table.ReplaceValue(m,null,0,Replacer.ReplaceValue,Table.ColumnNames(m)),
Sol = R(n,{"C1","Month"})
in
SolPower Query solution 2 for Calculate Spending Time, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Date", "Duration"}, "Person", "Name"),
Group = Table.Group(Unpivot, "Name", {"Duration", each List.Sum([Duration])}),
Sort = Table.Sort(Group, "Name"),
Total = Table.AddColumn(
Sort,
"Total",
each Record.FromList(
List.Transform(
Sort[Name],
(f) =>
[
S = Table.SelectRows(
Source,
(x) => (x[Person 1] = [Name] and x[Person 2] = f)
or (x[Person 2] = [Name] and x[Person 1] = f)
),
D = S[Duration],
T = List.Sum(D),
R = T / [Duration] ?? 0
][R]
),
Sort[Name]
)
),
Expand = Table.ExpandRecordColumn(Total, "Total", Sort[Name]),
Return = Table.RemoveColumns(Expand, {"Duration"})
in
ReturnPower Query solution 3 for Calculate Spending Time, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Combine = Table.AddColumn(Source, "Month", each [Person 1] & " " & [Person 2]),
X24 = Table.TransformColumns(Combine, {"Duration", each _ * 24}),
Split = Table.TransformColumns(X24, {"Month", each Text.Split(_, " ")}),
Xpand = Table.ExpandListColumn(Split, "Month"),
Group = Table.Group(Xpand, {"Month"}, {{"Sum", each List.Sum([Duration])}, {"All", each _}}),
Xpand1 = Table.ExpandTableColumn(
Group,
"All",
{"Person 1", "Person 2", "Duration"},
{"Person 1", "Person 2", "Duration"}
),
PercentOfTotal = Table.AddColumn(Xpand1, "% of Total", each [Duration] / [Sum], Percentage.Type),
Person2 = Table.AddColumn(
PercentOfTotal,
"Per 2",
each if [Month] = [Person 2] then [Person 1] else [Person 2]
),
Keep = Table.SelectColumns(Person2, {"% of Total", "Per 2", "Month"}),
Sort = Table.Sort(Keep, {{"Per 2", Order.Ascending}}),
Pivot = Table.Pivot(Sort, List.Distinct(Sort[#"Per 2"]), "Per 2", "% of Total", List.Sum),
Rplace = Table.ReplaceValue(Pivot, null, 0, Replacer.ReplaceValue, Table.ColumnNames(Pivot))
in
RplacePower Query solution 4 for Calculate Spending Time, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.RemoveColumns(Source, {"Date"}),
B1 = Table.TransformColumnTypes(A, {{"Duration", type duration}}),
T = Table.ReorderColumns(B1, {"Person 2", "Person 1", "Duration"}),
B2 = Table.RenameColumns(T, {{"Person 2", "Person 1"}, {"Person 1", "Person 2"}}),
Tb = Table.Combine({B1, B2}),
C = Table.AddColumn(
Tb,
"%",
each [Duration] / List.Sum(Table.SelectRows(Tb, (S) => S[Person 1] = [Person 1])[Duration]),
Percentage.Type
),
R = Table.SelectColumns(C, {"Person 1", "Person 2", "%"}),
Sol = Table.Pivot(R, List.Sort(List.Distinct(R[#"Person 2"])), "Person 2", "%", List.Sum)
in
SolPower Query solution 5 for Calculate Spending Time, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ct = Table.TransformColumnTypes(
Source,
{
{"Date", type date},
{"Person 1", type text},
{"Person 2", type text},
{"Duration", type number}
}
),
mc = Table.TransformColumns(ct, {{"Duration", each _ * 24, type number}}),
ft = Table.Combine(
{mc, Table.RenameColumns(mc, {{"Person 1", "Person 2"}, {"Person 2", "Person 1"}})}
),
grp = Table.Group(
ft,
{"Person 1"},
{
{
"a",
each [
t = List.Sum([Duration]),
g = Table.TransformColumns(
Table.Group(_, {"Person 2"}, {{"b", each List.Sum([Duration])}}),
{{"b", (x) => x / t, Percentage.Type}}
),
ttc = Table.ToColumns(g),
tfr = Table.FromRows({{[Person 1]{0}} & ttc{1}}, {"Person"} & ttc{0})
][tfr],
type record
}
}
),
comb = Table.Combine(grp[a]),
sc = Table.ReorderColumns(
comb,
List.Sort(Table.ColumnNames(comb), each if _ = "Person" then "0" else _)
),
sr = Table.Sort(sc, {{"Person", Order.Ascending}})
in
srSolving the challenge of Calculate Spending Time with Excel
Excel solution 1 for Calculate Spending Time, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
C3:C18,
b,
D3:D18,
d,
E3:E18,
PIVOTBY(
VSTACK(
a,
b
),
VSTACK(
b,
a
),
VSTACK(
d,
d
),
PERCENTOF,
,
0,
,
0
)
)Excel solution 2 for Calculate Spending Time, proposed by 🇰🇷 Taeyong Shin:
=LET(p,
C3:D18,
d,
E3:E18,
x,
SORT(
UNIQUE(
TOCOL(
p
)
)
),
y,
TOROW(
x
),
m,
MAP(x&y,
IFNA(
x,
y
),
LAMBDA(a,
b,
SUM(
ISNUMBER(
FIND(
a,
BYROW(
CHOOSECOLS(
p,
1,
2,
1
),
CONCAT
)
)
)*d
)/SUM((p=b)*d))),
HSTACK(
VSTACK(
"Month",
x
),
VSTACK(
y,
m
)
))Excel solution 3 for Calculate Spending Time, proposed by Oscar Mendez Roca Farell:
=LET(
_p1,
C3:C18,
_p2,
D3:D18,
_u,
SORT(
UNIQUE(
TOCOL(
C3:D18
)
)
),
_t,
TOROW(
_u
),
F,
LAMBDA(
x,
y,
SUMIFS(
E3:E18,
x,
_u,
y,
_t
)
),
_f,
F(
_p1,
_p2
)+F(
_p2,
_p1
),
VSTACK(
HSTACK(
"Month",
_t
),
HSTACK(
_u,
_f/IFS(
_f,
BYROW(
_f,
LAMBDA(
r,
SUM(
r
)
)
),
1,
1
)
)
)
)Excel solution 4 for Calculate Spending Time, proposed by Julian Poeltl:
=LET(T,
B3:E18,
PO,
CHOOSECOL(
T,
2
),
PT,
CHOOSECOL(
T,
3
),
Ti,
TAKE(
T,
,
-1
),
PUn,
SORT(
UNIQUE(
VSTACK(
PO,
PT
)
)
),
CPUn,
COUNTA(
PUn
),
R,
MAKEARRAY(CPUn,
CPUn,
LAMBDA(A,
B,
SUM(FILTER(Ti,
(PO=INDEX(
PUn,
A
))*(PT=INDEX(
PUn,
B
)),
0),
FILTER(Ti,
(PT=INDEX(
PUn,
A
))*(PO=INDEX(
PUn,
B
)),
0))/(SUM(
FILTER(
Ti,
PO=INDEX(
PUn,
A
),
0
)
)+SUM(
FILTER(
Ti,
PT=INDEX(
PUn,
A
),
0
)
)))),
HSTACK(
VSTACK(
"Month",
PUn
),
VSTACK(
TRANSPOSE(
PUn
),
R
)
))Excel solution 5 for Calculate Spending Time, proposed by Kris Jaganah:
=LET(a,
C3:C18,
b,
D3:D18,
c,
E3:E18*24,
d,
a&b,
e,
SORT(
UNIQUE(
TOCOL(
C3:D18
)
)
),
f,
TOROW(
e
),
VSTACK(HSTACK(
"Month",
f
),
HSTACK(e,
ROUND(MAP(e&f,
LAMBDA(x,
SUM((IFERROR(
FIND(
HSTACK(
x,
RIGHT(
x
)&LEFT(
x
)
),
d
),
0
)>0)*c)))/MAP(e,
LAMBDA(x,
SUM((IFERROR(
FIND(
x,
d
),
0
)>0)*c))),
2))))Excel solution 6 for Calculate Spending Time, proposed by John Jairo Vergara Domínguez:
=LET(
p,
SORT(
UNIQUE(
TOCOL(
C3:D18
)
)
),
q,
TOROW(
p
),
f,
LAMBDA(
x,
y,
SUMIFS(
E3:E18,
C3:C18,
x,
D3:D18,
y
)
),
b,
f(
p,
q
)+f(
q,
p
),
HSTACK(
VSTACK(
"Month",
p
),
VSTACK(
q,
b/BYROW(
b,
SUM
)
)
)
)Excel solution 7 for Calculate Spending Time, proposed by Sunny Baggu:
=LET( _m,
SORT(
UNIQUE(
TOCOL(
C3:D18
)
)
), _mt,
TOROW(
_m
), _t,
MAP(
_m,
LAMBDA(
b,
SUM(
BYROW(
N(
C3:D18 = b
),
LAMBDA(
a,
SUM(
a
)
)
) * E3:E18
)
)
), _r,
ROUND( MAKEARRAY(
ROWS(
_m
),
COLUMNS(
_mt
),
LAMBDA(
r,
c,
INDEX(
MAP(
_mt,
LAMBDA(
x,
LET(
_a,
C3:D18 = HSTACK(
x,
INDEX(
_m,
r,
1
)
),
_b,
C3:D18 = HSTACK(
INDEX(
_m,
r,
1
),
x
),
SUM(
TAKE(
_a,
,
1
) * TAKE(
_a,
,
-1
) * E3:E18,
TAKE(
_b,
,
1
) * TAKE(
_b,
,
-1
) * E3:E18
)
)
)
),
c
)
)
) / _t, 2 ), VSTACK(
HSTACK(
"Month",
_mt
),
HSTACK(
_m,
_r
)
))Excel solution 8 for Calculate Spending Time, proposed by Hussein SATOUR:
=LET(Pa,
C3:C18,
Pb,
D3:D18,
D,
E3:E18,
a,
SORT(
UNIQUE(
Pb
)
),
b,
TOROW(
a
),
v,
MAP(a&b,
LAMBDA(x,
SUM(FILTER(D,
(Pa&Pb=x)+(Pb&Pa=x),
0)))),
HSTACK(
VSTACK(
"Month",
a
),
VSTACK(
b,
v/BYROW(
v,
SUM
)
)
))