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
the following code is based only on the data you have provided, but to be of more general validity you should give more information on the different situations that can occur.
How do you select the group of lines of interest for each shift
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSN7DQNzIwMlAwMLUyNVDSUQouSSwqKS0AsgyVYnVQlZhZGYKUBJXm5WXmpQNZRuhKDI2sjEFKfBMz80pS8xLzklOxKjOBKHNLzMxRMMSqwgKiAmGXsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
ct = Table.TransformColumnTypes(Source,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}),
pos = let idx2=List.PositionOf(ct[ShiftIndex], 2,Occurrence.All) in {List.Min(idx2)-1}&idx2&{List.Max(idx2)+1},
ai = Table.AddIndexColumn(ct, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(ai, "duration", each try Duration.TotalMinutes(List.Min({ai[StateDateTime]{[Index]+1},#datetime(2020,8,22,18,0,0)})-List.Max({[StateDateTime],#datetime(2020,8,22,6,0,0)}))otherwise null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "pc", each [duration]/List.Sum(#"Added Custom"[duration])),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"pc", Percentage.Type}})
in
#"Changed Type"
Thank you!
Yes, for the specific scenario I set out where ShiftIndex = 2, the solution does work. However as you rightfully mentioned, we need to paint out more scenarios. The current limitation is that I need to be able to select a varity of Date's to report on. So we should be able to not only select ShiftIndex=2, but also ShiftIndex=3, or select a different DatTime, like Week, Month, year etc. and we should be able to calculate the Duration of Each State, for the selected Duration.
I've included an expanded dataset, based on your solution below:
| StateDateTime | EquipmentState | ShiftIndex | Index | Duration | PC |
2020/08/22 05:50:00 | Startup | 1 | 0 | 10 | -1.23% |
2020/08/22 06:10:00 | Running | 2 | 1 | 380 | -46.63% |
2020/08/22 12:30:00 | Maintenance | 2 | 2 | 120 | -14.72% |
2020/08/22 14:30:00 | Failure | 2 | 3 | 210 | -25.77% |
2020/08/22 18:30:00 | Running | 3 | 4 | -30 | 3.68% |
2020/08/22 18:45:00 | ShutDown | 3 | 5 | -45 | 5.52% |
2020/08/23 06:00:00 | Startup | 4 | 6 | -720 | 88.34% |
2020/08/23 06:20:00 | Running | 4 | 7 | -740 | 90.80% |
2020/08/23 19:00:00 | ShutDown | 5 | 8 | null | null |
For ShiftIndex=2, the calculation is correct, but if we look at ShiftIndex=3, we should have an entry that Failure=30min, Running=15min, ShautDown=675min.
As for ShiftIndex=4, we would have Startup=20min, Running=700min. and for ShiftIndex=5 we would have Runing=60min and ShutDown = 700min (if we assume we have no more entries for ShiftIndex=5)
- mahoneypat
Microsoft Employee
You can probably generate the table you'll need in M (precalcualted for each shift), but could also be solved with DAX. To do that, you'll also need a disconnected Shifts table that has the start and stop times for each shift. Also, you'll want to split your DateTime column into Date and Time (in the query) to enable the calculation you'll need (it will not be a simple DAX expression though, as you'll need to compare each statechange time to the start/stop time of the shift).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
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:
