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
Hi mahoneypat - Thanks for the reply.
I've been playing with the idea to try and simply INSERT a new row at the start of each Shift, with a StateDateTime of either 06:00:00 or 18:00:00 (depending on which shift it is). The insert then simply needs to copy the EquipmentState from the previous row (Last EquipmentState from the previous ShiftIndex)... Thus a new EquipmentState will always be 'logged' or inserted at the start of each shift, which should help to address the issue? Again, have had no joy in being able to insert a row, with values from a previous row...
I have come up with different ways of dealing with the problem, but all of them still tangled.
I have chosen this, which I hope is clear enough, as well as correspond to what is requested.
If necessary, I can explain the regions of the various steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/BDoIwDAbgVzE7k7AVZpAzmnjwIt4Ih0WnLoGKpIs+vhuYILLDkmb90r+tKlaSIl24dzKtZhHbPq3pWo00NNxHeTdX2uNFv1kdVQwg5lkMHPiKy1xyL0j1ZDtXiQVZ58KTo0U0eHMV/BMBeeLJQRkkjQrPOsjSke2UaWwfJtlIprAkQFI5HGWpeLxwMsnvznx+VhogME9aELH5TpmSJKvrDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
ct = Table.TransformColumnTypes(#"Promoted Headers",{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}),
idx=List.RemoveLastN(List.Distinct(ct[ShiftIndex]),1),
listRows=(ru,rl)=>
let
d=ru[StateDateTime],
dl=rl[StateDateTime],
shHour=if Time.Hour(dl) < 18 then 6 else 18,
shDay= if Time.Hour(d) > Time.Hour(dl) then 1 else 0,
uRow=ru&[StateDateTime=#datetime(Date.Year(d),Date.Month(d),Date.Day(d)+shDay,shHour,0,0)],
lRow=ru&[StateDateTime=#datetime(Date.Year(d),Date.Month(d),Date.Day(d)+shDay,shHour,0,0),ShiftIndex=ru[ShiftIndex]+1]
in {uRow,lRow},
nt=List.Accumulate(idx,ct, (s,c)=> let pos=List.PositionOf(s[ShiftIndex],c,Occurrence.Last) in Table.InsertRows(s,pos+1,listRows(s{pos},s{pos+1}))),
#"Added Index" = Table.AddIndexColumn(nt, "i", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Duration", each try if [ShiftIndex]=nt[ShiftIndex]{[i]+1} then Duration.TotalMinutes(nt[StateDateTime]{[i]+1}-[StateDateTime]) else "" otherwise"")
in
#"Added Custom"
PS
As the solution makes use of the List.Accumulate function, you should take into account its limitations in terms of the size of the table that can be handled.
Check out what ziying35 experienced in this post:
Thanks Anonymous ... the solution supplied works well!
However it does seem like the List.Accumulate function is causing some constraints.
The datasets provided are example datasets of what we're working with, but in fact the actual datasets can be much bigger as it has multiple equipment, and each equipment can have up to 50 or 60 different states during each shift... hence if we want to look at a months worth of data for example, across the fleet, the List.Accumulate seems to becoming a limitation of sorts.
I'll check out the post by @ziying35 to see if it adds value, thank you!
DYd you try the code on your real dataset?
How many rows you table has?
If you explain all relevant details of you problem, you have chance to get more spwcific and usefull answer.
If needs; I can modify the code to manage big dataset.
Thanks Anonymous ,
I ran the code against a sub-set of the actual Dataset (limited to 2000 rows) - in xlsx format and it took approx 20min to run... Most of the results seem ok, except if we have a case where we log an Event before the close of a shift, and only log another event 2 shifts later... Example we log a State at 16:00:00 (DayShift or ShiftIndex =4 for example) and the next State is only logged at 08:00:00 the following day (DayShift or ShiftIndex = 6 for example) - then the code does not make an entry at 18:00:00 - so we have not entry for ShiftIndex = 5.
The current complete Dataset contains approx 5000 rows, and would grow by roughly 2000 entires each month.
I'm not sure how I get you a copy of the actual dataset, but happy to share.