Forum Discussion
Calculate Equipment State Duration per Shift
here a solution "complete" (corresponding to the status of the information received 😁) avoiding the use of the function List.Accumulate.
starting table:
output Table:
the code for the output table:
let ct = Table.TransformColumnTypes(Table,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}), idx=List.RemoveLastN(List.Distinct(ct[ShiftIndex]),1), lstR=List.Generate( ()=>[r=1,pos=List.PositionOf(ct[ShiftIndex],idx{0},Occurrence.Last), lr=sfht(ct{pos},ct{pos+1})], each [r]<=List.Count(idx), each [r=[r]+1, pos=List.PositionOf(ct[ShiftIndex],idx{[r]},Occurrence.Last),lr=sfht(ct{pos},ct{pos+1}) ], each [lr] ), tc= Table.Combine({Table.FromRecords(List.Combine(lstR)),Table}), grp = Table.Group(tc, {"ShiftIndex"}, {{"shIdx", each duration(_)}}), #"Expanded shIdx" = Table.ExpandTableColumn(grp, "shIdx", {"StateDateTime", "EquipmentState", "Duration"}, {"StateDateTime", "EquipmentState", "Duration"}) in #"Expanded shIdx"the two functions used:
sfht
let listRows=(rL,rF)=> let shift=#duration(0,0,720,0), dL=rL[StateDateTime], dF=rF[StateDateTime], lr=List.Generate( ()=>[r=rL&[StateDateTime=#datetime(Date.Year(dL),Date.Month(dL),Date.Day(dL)+sh{1},sh{0},0,0)],idx=1], each [r][StateDateTime]<=dF, each [r=if Number.Mod(idx,2)=1 then [r]& [StateDateTime=[r][StateDateTime]+shift] else [r]&[ShiftIndex=[r][ShiftIndex]+1],idx=[idx]+1], each [r] ), sh=if Time.Hour(dL) < 6 then {6,0} else if Time.Hour(dL)<18 then {18,0} else {6,1} in lr in listRowsand finally duration:
let count=(tab)=> let ts=Table.Sort(tab,{"StateDateTime"}), ai = Table.AddIndexColumn(ts, "i", 0, 1) in Table.RemoveColumns(Table.AddColumn(ai, "Duration", each try if [ShiftIndex]=ai[ShiftIndex]{[i]+1} then Duration.TotalMinutes(ai[StateDateTime]{[i]+1}-[StateDateTime]) else "" otherwise""),{"i"}) in count
Thank you mahoneypat - this does help a lot! Thanks for the inputs
The one last thing we need to look at in this scenario is the ShiftIndex that we just FilledDown.
The ShiftIndex is a number that indicates what shift we are referencing. Hence in your code the Fill down gives us this output:
StateDateTimeEquipmentStateShiftIndex
| 2020/08/22 05:50:00 | Startup | 1 |
| 2020/08/22 05:59:00 | Startup | 1 |
| 2020/08/22 06:00:00 | Startup | 1 |
| 2020/08/22 06:10:00 | Running | 2 |
| 2020/08/22 12:30:00 | Maintenance | 2 |
| 2020/08/22 14:30:00 | Failure | 2 |
| 2020/08/22 17:59:00 | Failure | 2 |
| 2020/08/22 18:00:00 | Failure | 2 |
| 2020/08/22 18:30:00 | Running | 3 |
| 2020/08/22 18:45:00 | ShutDown | 3 |
| 2020/08/23 05:59:00 | ShutDown | 3 |
| 2020/08/23 06:00:00 | Startup | 4 |
| 2020/08/23 06:00:00 | Startup | 4 |
| 2020/08/23 06:20:00 | Running | 4 |
| 2020/08/23 17:59:00 | Running | 4 |
| 2020/08/23 18:00:00 | Running | 4 |
| 2020/08/23 19:00:00 | ShutDown | 5 |
Which is correct for all fields and exactly what we need, however the ShiftIndex now becomes a challenge.
Where we would need to increment the ShiftIndex, when we have an entry in the next Shift, like this:
(I've added a comment filed to help explain)
StateDateTimeEquipmentStateShiftIndex Comment
| 2020/08/22 05:50:00 | Startup | 1 | |
| 2020/08/22 05:59:00 | Startup | 1 | |
| 2020/08/22 06:00:00 | Startup | 2 | from 06:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex |
| 2020/08/22 06:10:00 | Running | 2 | |
| 2020/08/22 12:30:00 | Maintenance | 2 | |
| 2020/08/22 14:30:00 | Failure | 2 | |
| 2020/08/22 17:59:00 | Failure | 2 | |
| 2020/08/22 18:00:00 | Failure | 3 | from 18:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex |
| 2020/08/22 18:30:00 | Running | 3 | |
| 2020/08/22 18:45:00 | ShutDown | 3 | |
| 2020/08/23 05:59:00 | ShutDown | 3 | |
| 2020/08/23 06:00:00 | Startup | 4 | Correct ShiftIndex, as the State change was logged at 06:00 |
| 2020/08/23 06:00:00 | Startup | 4 | |
| 2020/08/23 06:20:00 | Running | 4 | |
| 2020/08/23 17:59:00 | Running | 4 | |
| 2020/08/23 18:00:00 | Running | 4 | from 18:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex |
| 2020/08/23 19:00:00 | ShutDown | 5 |
We will also run into an issue with the ShiftIndex, if we have an EquipmentState that has a duration across multiple shifts. So if we use this input dataset for example:
StateDateTimeEquipmentStateShiftIndex
| 2020/08/24 05:50:00 | Running | 5 |
| 2020/08/25 06:15:00 | ShutDown | 8 |
Your Solution gives us this output because of the FillDown on ShiftIndex:
StateDateTimeEquipmentStateShiftIndex
| 2020/08/24 05:50:00 | Running | 5 |
| 2020/08/24 05:59:00 | Running | 5 |
| 2020/08/24 06:00:00 | Running | 5 |
| 2020/08/24 17:59:00 | Running | 5 |
| 2020/08/24 18:00:00 | Running | 5 |
| 2020/08/25 05:59:00 | Running | 5 |
| 2020/08/25 06:00:00 | Running | 5 |
| 2020/08/25 06:15:00 | ShutDown | 8 |
| 2020/08/25 17:59:00 | ShutDown | 8 |
| 2020/08/25 18:00:00 | ShutDown | 8 |
Where we need the following in terms of ShiftIndex:
StateDateTimeEquipmentStateShiftIndex
| 2020/08/24 05:50:00 | Running | 5 |
| 2020/08/24 05:59:00 | Running | 5 |
| 2020/08/24 06:00:00 | Running | 6 |
| 2020/08/24 17:59:00 | Running | 6 |
| 2020/08/24 18:00:00 | Running | 7 |
| 2020/08/25 05:59:00 | Running | 7 |
| 2020/08/25 06:00:00 | Running | 8 |
| 2020/08/25 06:15:00 | ShutDown | 8 |
| 2020/08/25 17:59:00 | ShutDown | 8 |
| 2020/08/25 18:00:00 | ShutDown | 8 |
Thanks again for the time and help on this!
here a solution "complete" (corresponding to the status of the information received 😁) avoiding the use of the function List.Accumulate.
starting table:
output Table:
the code for the output table:
let
ct = Table.TransformColumnTypes(Table,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}),
idx=List.RemoveLastN(List.Distinct(ct[ShiftIndex]),1),
lstR=List.Generate(
()=>[r=1,pos=List.PositionOf(ct[ShiftIndex],idx{0},Occurrence.Last), lr=sfht(ct{pos},ct{pos+1})],
each [r]<=List.Count(idx),
each [r=[r]+1, pos=List.PositionOf(ct[ShiftIndex],idx{[r]},Occurrence.Last),lr=sfht(ct{pos},ct{pos+1}) ],
each [lr]
),
tc= Table.Combine({Table.FromRecords(List.Combine(lstR)),Table}),
grp = Table.Group(tc, {"ShiftIndex"}, {{"shIdx", each duration(_)}}),
#"Expanded shIdx" = Table.ExpandTableColumn(grp, "shIdx", {"StateDateTime", "EquipmentState", "Duration"}, {"StateDateTime", "EquipmentState", "Duration"})
in
#"Expanded shIdx"
the two functions used:
sfht
let
listRows=(rL,rF)=>
let
shift=#duration(0,0,720,0),
dL=rL[StateDateTime],
dF=rF[StateDateTime],
lr=List.Generate(
()=>[r=rL&[StateDateTime=#datetime(Date.Year(dL),Date.Month(dL),Date.Day(dL)+sh{1},sh{0},0,0)],idx=1],
each [r][StateDateTime]<=dF,
each [r=if Number.Mod(idx,2)=1 then [r]& [StateDateTime=[r][StateDateTime]+shift] else [r]&[ShiftIndex=[r][ShiftIndex]+1],idx=[idx]+1],
each [r]
),
sh=if Time.Hour(dL) < 6 then {6,0} else if Time.Hour(dL)<18 then {18,0} else {6,1}
in lr
in listRows
and finally duration:
let
count=(tab)=>
let
ts=Table.Sort(tab,{"StateDateTime"}),
ai = Table.AddIndexColumn(ts, "i", 0, 1)
in
Table.RemoveColumns(Table.AddColumn(ai, "Duration", each try if [ShiftIndex]=ai[ShiftIndex]{[i]+1} then Duration.TotalMinutes(ai[StateDateTime]{[i]+1}-[StateDateTime]) else "" otherwise""),{"i"})
in count