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
Microsoft EmployeeYou 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:
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.
- mahoneypat
I agree adding rows with shift start and end for each day would be helpful and should be straight forward to do in query. Please provide the start and stop times for each shift, and I will suggest a way to do it (in either M or DAX).
Regards,
Pat
Thanks mahoneypat .
Shifts are Daily, from 06:00:00 to 17:59:59 (Day Shift) and from 18:00:00 to 05:59:59 (Night Shift).
Please see the thread from Anonymous - where he did insert additional rows, however we're running into a limitation on List.Accumulate as the datasets can be rather large and have in excess of 50 or 60 state changes per equipment per shift. If we have a fleet of 10 or 20 pieces of equipment, the proposed solutions seems to struggle witht he dataset size. Happy to have this solved in either M or DAX.
Thanks again for the help