1. Find the running total in hours in each process groups for duration of the steps i.e. Finish Time – Start Time of the steps and then working out Running Total. 2. Find the calendar hours duration for each process group which is max of Finish Time – min of Start Time for a process group.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 98
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Process Time Running Totals with Power Query
Power Query solution 1 for Process Time Running Totals, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Combine(
Table.Group(
Source,
"Process",
{
"T",
each
let
D = List.Accumulate(
Table.AddColumn(
_,
"Du",
each DateTime.From([Finish Time]) - DateTime.From([Start Time])
)[Du],
{},
(s, l) => s & {List.Last(s, 0) + Number.From(l) * 24}
),
E = Table.FromColumns(
Table.ToColumns(_) & {D},
Table.ColumnNames(_) & {"Running Total"}
)
in
Table.AddColumn(
E,
"Duration",
(t) =>
Number.From(
DateTime.From(List.Last([Finish Time])) - DateTime.From([Start Time]{0})
)
* 24
)
}
)[T]
)
in
GroupPower Query solution 2 for Process Time Running Totals, proposed by Zoran Milokanović:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
{{"Start Time", type datetime}, {"Finish Time", type datetime}}
),
S = Table.ExpandTableColumn(
Table.Group(
Source,
{"Process"},
{
{
"All",
each
let
t = Table.AddColumn(_, "D", each Number.From([Finish Time] - [Start Time]) * 24)
in
Table.AddColumn(
t,
"Running Total",
each List.Sum(List.FirstN(t[D], List.PositionOf(t[Steps], [Steps]) + 1))
)
},
{"Duration", each Number.From(List.Max([Finish Time]) - List.Min([Start Time])) * 24}
}
),
"All",
{"Steps", "Start Time", "Finish Time", "Running Total"}
)
in
SPower Query solution 3 for Process Time Running Totals, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Process"},
{
{
"All",
each
let
a = Table.AddIndexColumn(_, "Idx"),
b = Table.AddColumn(
a,
"New",
each Number.Round(
Number.From(
DateTime.From(a[Finish Time]{[Idx]}) - DateTime.From(a[Start Time]{[Idx]})
)
* 24,
1
)
),
c = Table.AddColumn(b, "Running Total", each List.Sum(List.FirstN(b[New], [Idx] + 1))),
d = Table.AddColumn(
c,
"Duration",
each Number.Round(
Number.From(
DateTime.From(a[Finish Time]{List.Last(a[Idx])}) - DateTime.From(a[Start Time]{0})
)
* 24,
1
)
)
in
d
}
}
)[[All]],
Sol = Table.ExpandTableColumn(
Group,
"All",
Table.ColumnNames(Source) & {"Running Total", "Duration"}
)
in
SolPower Query solution 4 for Process Time Running Totals, proposed by Luan Rodrigues:
let
Fonte = Query_Tabela1,
gp = Table.Group(
Fonte,
{"Process"},
{
{
"Contagem",
each [
a = Table.AddColumn(
_,
"Total",
each Number.Round(
Number.From(DateTime.From([Finish Time]) - DateTime.From([Start Time])) * 24,
1
)
),
b = Table.AddIndexColumn(a, "Ind", 1, 1),
c = Table.AddColumn(b, "Total Acc", each List.Sum(List.FirstN(b[Total], [Ind]))),
d = Table.AddColumn(
c,
"Duration",
each Number.Round(
Number.From(
DateTime.From(List.Last(c[Finish Time])) - DateTime.From(List.First(c[Start Time]))
)
* 24,
1
)
)
][d]
}
}
),
res = Table.ExpandTableColumn(
gp,
"Contagem",
List.RemoveFirstN(Table.ColumnNames(Fonte), 1)
& List.LastN(Table.ColumnNames(gp[Contagem]{0}), 2)
)
in
resPower Query solution 5 for Process Time Running Totals, proposed by Eric Laforce:
let
fxDuration.ToHours = (d as duration) => Number.Round(Number.From(d) * 24, 1),
Source = Excel.CurrentWorkbook(){[Name = "tData98"]}[Content],
ChangeType = Table.TransformColumnTypes(
Source,
{{"Start Time", type datetime}, {"Finish Time", type datetime}}
),
Group = Table.Group(
ChangeType,
{"Process"},
{
"G",
(_T) =>
let
_GD = fxDuration.ToHours(List.Max(_T[Finish Time]) - List.Min(_T[Start Time])),
_TR = List.Accumulate(
Table.ToRecords(_T),
[r = {}, rt = 0],
(s, c) =>
let
_SD = fxDuration.ToHours(c[Finish Time] - c[Start Time]),
_RT = s[rt] + _SD,
_NewRecord = Record.Combine({c, [#"Running Total" = _RT, Duration = _GD]})
in
[r = s[r] & {_NewRecord}, rt = _RT]
)
in
_TR[r]
}
),
ToTable = Table.FromRecords(List.Combine(Group[G]))
in
ToTablePower Query solution 6 for Process Time Running Totals, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name="ProcessLength"]}[Content],
GRT = Table.FromColumns(
Table.ToColumns(Source) & {fxGRT( List.Buffer(Source[Start Time]) , List.Buffer(Source[Finish Time]) , List.Buffer(Source[Process]) )},
Table.ColumnNames(Source) & {"Running Total"}
),
Grouped = Table.Group(GRT, {"Process"}, {{"All", each _, type table [Process=text, Steps=text, Start Time=datetime, Finish Time=datetime, Running Total=number]},{"Duration", each 24*Number.From(List.Max([Finish Time])-List.Min([Start Time])), type number}}),
Expanded = Table.ExpandTableColumn(Grouped, "All", {"Steps", "Start Time", "Finish Time", "Running Total"})
in
Expanded
fxGRT with List.Generate:
(start as list, finish as list, grouping as list) as list =>
let
GRTList = List.Generate(
()=> [ GRT = 24*Number.From(finish{0}-start{0}), i = 0 ],
each [i] < List.Count(start),
each try
if grouping{[i]} = grouping{[i] + 1}
then [GRT = [GRT] + 24*Number.From(finish{[i] + 1}-start{[i] + 1}), i = [i] + 1]
else [GRT = 24*Number.From(finish{[i] + 1}-start{[i] + 1}), i = [i] + 1]
otherwise [i = [i] + 1] ,
each [GRT]
)
in
GRTList
Power Query solution 7 for Process Time Running Totals, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S1 = Table.Group(Source, {"Process"}, {{"Count", each _}}),
S2 = Table.TransformColumns(
S1,
{
"Count",
each [
r5 = Table.AddColumn(_, "Subtraction", each [Finish Time] - [Start Time], type duration),
r6 = Table.AddColumn(
r5,
"Total Hours",
each Duration.TotalHours([Subtraction]),
type number
),
#"Added Index" = Table.AddIndexColumn(r6, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(
#"Added Index",
"Running Total",
each List.Sum(List.FirstN(#"Added Index"[Total Hours], [Index]))
),
r2 = Table.AddColumn(
#"Added Custom",
"Duration",
each Duration.TotalHours(
List.Max(#"Added Index"[Finish Time]) - List.Min(#"Added Index"[Start Time])
)
),
r1 = Table.RemoveColumns(r2, {"Process", "Subtraction", "Total Hours", "Index"})
][r1]
}
),
S3 = Table.ExpandTableColumn(
S2,
"Count",
{"Steps", "Start Time", "Finish Time", "Running Total", "Duration"}
),
S4 = Table.TransformColumns(
S3,
{{"Duration", each Number.Round(_, 1)}, {"Running Total", each Number.Round(_, 1)}}
),
S5 = Table.TransformColumnTypes(
S4,
{{"Start Time", type datetime}, {"Finish Time", type datetime}}
)
in
S5Solving the challenge of Process Time Running Totals with Excel
Excel solution 1 for Process Time Running Totals, proposed by Bo Rydobon 🇹🇭:
=LET(p,A2:A15,HSTACK(A2:D15,SCAN(0,p,LAMBDA(a,v,(OFFSET(v,-1,)=v)*a+OFFSET(v,,3)-OFFSET(v,,2)))*24,(XLOOKUP(p,p,D2:D15,,,-1)-XLOOKUP(p,p,C2:C15))*24))Excel solution 2 for Process Time Running Totals, proposed by محمد حلمي:
=MAXIFS(D2:D15,A2:A15,A2:A15)
Not give a result because the numbers are stored as text,
If sum 0 on D2:D15 to be
=MAXIFS(D2:D15+0,A2:A15,A2:A15)Excel solution 3 for Process Time Running Totals, proposed by محمد حلمي:
=LET(i,A2:A15,HSTACK(SCAN(0,i,LAMBDA(a,d,LET(e,24*(OFFSET(d,,3)-OFFSET(d,,2)),IF(d=OFFSET(d,-1,),a+e,e)))),
MAP(i,LAMBDA(a,LET(e,FILTER(C2:D15,i=a)*24,MAX(e)-MIN(e))))))Excel solution 4 for Process Time Running Totals, proposed by Sunny Baggu:
=HSTACK(
A2:D15,
LET(
_p, A2:A15,
HSTACK(
DROP(
REDUCE(
"",
UNIQUE(_p),
LAMBDA(x, y, VSTACK(x, SCAN(0, FILTER((D2:D15 - C2:C15) * 24, _p = y), LAMBDA(a, v, a + v))))
),
1
),
SCAN(0, _p, LAMBDA(a, v, (TAKE(FILTER(D2:D15, _p = v), -1) - TAKE(FILTER(C2:C15, _p = v), 1)) * 24))
)
)
)Excel solution 5 for Process Time Running Totals, proposed by LEONARD OCHEA 🇷🇴:
=LET(t,A1:D15,c,LAMBDA(x,DROP(INDEX(t,,x),1)),r,24*(c(4)-c(3)),f,REDUCE("Running Total",UNIQUE(c(1)),LAMBDA(a,b,VSTACK(a,SCAN(0,FILTER(r,c(1)=b),LAMBDA(c,d,c+d))))),g,REDUCE("Duration",c(1),LAMBDA(a,b,VSTACK(a,24*(MAX(--FILTER(c(4),c(1)=b))-MIN(--FILTER(c(3),c(1)=b)))))),HSTACK(t,f,g))Solving the challenge of Process Time Running Totals with Python
Python solution 1 for Process Time Running Totals, proposed by Murat E.:
import pandas as pd
from datetime import datetime
def fxGRT(start, finish, grouping):
GRTList = []
i = 0
while i < len(start):
GRT = 24 * (finish[i] - start[i]).total_seconds() / 3600
if i + 1 < len(start) and grouping[i] == grouping[i + 1]:
GRT += 24 * (finish[i + 1] - start[i + 1]).total_seconds() / 3600
i += 1
GRTList.append(GRT)
i += 1
return GRTList
data = {
'Process': ['Process1', 'Process1', 'Process2', 'Process2', 'Process2'],
'Steps': ['Step1', 'Step2', 'Step1', 'Step2', 'Step3'],
'Start Time': ['2023-07-20 08:00:00', '2023-07-20 10:30:00', '2023-07-21 12:00:00', '2023-07-21 14:30:00', '2023-07-21 16:00:00'],
'Finish Time': ['2023-07-20 10:00:00', '2023-07-20 12:30:00', '2023-07-21 13:30:00', '2023-07-21 16:00:00', '2023-07-21 18:00:00']
}
df = pd.DataFrame(data)
df['Start Time'] = pd.to_datetime(df['Start Time'])
df['Finish Time'] = pd.to_datetime(df['Finish Time'])
def calculate_duration(row):
return 24 * (row['Finish Time'] - row['Start Time']).total_seconds() / 3600
df['Running Total'] = df.apply(calculate_duration, axis=1)
df['Running Total'] = df.groupby('Process')['Running Total'].cumsum()
result = df[['Process', 'Steps', 'Start Time', 'Finish Time', 'Running Total']]
&&&