Calculate Equipment State Duration per Shift

[EricSteynMMD]

Hi All, I need help please.

I need to report on the duration that a piece of equipment was running in a specific state, during each shift. The issue is that the dataset only has entries when a state change occurs, hence if a specific state runs across 2 shifts, it becomes a challenge to determine how long the equipment was in that state, for the current shift, as the entry for the state might have been captured during the previous shift, and not the current shift. Shifts are 12 hour durations, and run from 06:00-18:00 each day as Day shift and 18:00-06:00 the enxt day, as Night shift

Here is a snip of the data:

StateDateTime	EquipmentState	ShiftIndex
2020/08/22 05:50: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	Fail 1	2
2020/08/22 18:30:00	Running	3

We want to be able to report, that for ShiftIndex = 2 for example, we have the following EquipmentState Durations and as a percentage of the TotalShiftTime

EquipmentState	Duration in Minutes	Percentage of Shift
StartUp	10	13,8
Running	380	52,7
Maintenance	120	16,7
Fail 1	210	29,2

Any help would be greatly appreciated!

[Anonymous]

hi @EricSteynMMD 

have you tried the code on the complete dataset?
how many rows the input table?
how many the output one?
what is the execution time?

[Icey]

Hi @EricSteynMMD ,

How about using DAX?

1. Create EquipmentState table by entering data and sort "EquipmentState" column by "Order" column.

2. Create measures.

Duration in Minutes =
VAR StartTime =
    YEAR ( MAX ( 'Table'[StateDateTime] ) ) & "/"
        & MONTH ( MAX ( 'Table'[StateDateTime] ) ) & "/"
        & DAY ( MAX ( 'Table'[StateDateTime] ) ) & " "
        & TIME ( 6, 0, 0 )
VAR EndTime =
    YEAR ( MAX ( 'Table'[StateDateTime] ) ) & "/"
        & MONTH ( MAX ( 'Table'[StateDateTime] ) ) & "/"
        & DAY ( MAX ( 'Table'[StateDateTime] ) ) & " "
        & TIME ( 18, 0, 0 )
VAR StartUpTime =
    CALCULATE (
        MAX ( 'Table'[StateDateTime] ),
        'Table'[EquipmentState] = "StartUp"
    )
VAR RunningTime_ =
    CALCULATE (
        MAX ( 'Table'[StateDateTime] ),
        'Table'[EquipmentState] = "Running"
    )
VAR StartUpTime_ =
    IF ( StartUpTime = BLANK (), StartTime, StartUpTime )
VAR MaintenanceTime =
    CALCULATE (
        MAX ( 'Table'[StateDateTime] ),
        'Table'[EquipmentState] = "Maintenance"
    )
VAR FailTime =
    CALCULATE ( MAX ( 'Table'[StateDateTime] ), 'Table'[EquipmentState] = "Fail 1" )
VAR FailTime_ =
    IF ( FailTime = BLANK (), EndTime, FailTime )
RETURN
    SWITCH (
        MAX ( EquipmentState[EquipmentState] ),
        "StartUp", DATEDIFF ( StartUpTime_, RunningTime_, MINUTE ),
        "Running", DATEDIFF ( RunningTime_, MaintenanceTime, MINUTE ),
        "Maintenance", DATEDIFF ( MaintenanceTime, FailTime_, MINUTE ),
        "Fail 1", DATEDIFF ( FailTime_, EndTime, MINUTE )
    )

Percentage of Shift = 
[Duration in Minutes]/SUMX(ALL(EquipmentState),[Duration in Minutes])

BTW, .pbix file attached.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it more quickly.

[EricSteynMMD]

Thanks @Icey  for the detailed solution below.

The only issue we'd have with this approach is that the EquipmentState's are not only limited to the ones I gave in the Example and can in fact be up to 10 different Events/States. They also do not specifically occur in any order and can also sometimes span the duraiton of a number of shifts as well.

Not entirely sure how I send an attachment, but happy to share a copy of the Dataset if it would help?

[mahoneypat]

Here is another way to add shift start and end to your example data that should be performant.  Please take a look.  I'm not sure what ShiftIndex is, so I just Filled Down that to.  Also, I am in a different locale, so changed the input dates.  You may have to change locale at the #"Promoted Headers" step (or just replace the input datetime values).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9ND4IwDAbgv2J2JmErzCBnNPHgRbwtHBadugQqki768x1gRD4OS5r1Sd9WKZaTJpP5d7KVYQHbPp2tK4PUNfxHfrdX2uPFvFkRKJaEACFw4CsuU8lbQLohV/tKTMU6Fa04OkSLN1/BRAhIo1YctEUyqPFsllTcq522pWsWRdKLISmai1h25zjKHi/8J9FvXT4+KJ4LGMdMhdh8ZwwxkhXFBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"StateDateTime", type datetime}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"StateDateTime"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"StateDateTime", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "ShiftList", each {#time(5,59,59), #time(6,0,0), #time(17,59,59), #time(18,0,0)}),
    #"Expanded ShiftList" = Table.ExpandListColumn(#"Added Custom", "ShiftList"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded ShiftList", {{"StateDateTime", type text}, {"ShiftList", type text}}, "en-US"),{"StateDateTime", "ShiftList"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"StateDateTime.1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"StateDateTime.1", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"StateDateTime.1", "StateDateTime"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Changed Type1"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"StateDateTime", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"EquipmentState", "ShiftIndex"})
in
    #"Filled Down"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[EricSteynMMD]

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!

[Anonymous · Accepted solution]

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

[mahoneypat]

The M code has been updated to add a ShiftIndexIncrement to address your first point.  I will re-read and try to address the second point later (time to "go" to work).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9ND4IwDAbgv2J2JmErzCBnNPHgRbwtHBadugQqki768x1gRD4OS5r1Sd9WKZaTJpP5d7KVYQHbPp2tK4PUNfxHfrdX2uPFvFkRKJaEACFw4CsuU8lbQLohV/tKTMU6Fa04OkSLN1/BRAhIo1YctEUyqPFsllTcq522pWsWRdKLISmai1h25zjKHi/8J9FvXT4+KJ4LGMdMhdh8ZwwxkhXFBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"StateDateTime", type datetime}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"StateDateTime"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"StateDateTime", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "ShiftList", each {#time(5,59,59), #time(6,0,0), #time(17,59,59), #time(18,0,0)}),
    #"Expanded ShiftList" = Table.ExpandListColumn(#"Added Custom", "ShiftList"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded ShiftList", {{"StateDateTime", type text}, {"ShiftList", type text}}, "en-US"),{"StateDateTime", "ShiftList"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"StateDateTime.1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"StateDateTime.1", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"StateDateTime.1", "StateDateTime"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "ShiftIndexIncrement", each if Time.Hour([StateDateTime]) = 6 or Time.Hour([StateDateTime]) =18 then 1 else 0, Int64.Type),
    #"Appended Query" = Table.Combine({#"Added Custom1", #"Changed Type1"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"StateDateTime", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"EquipmentState", "ShiftIndex"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,0,Replacer.ReplaceValue,{"ShiftIndexIncrement"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value",{{"ShiftIndex", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type3", "NewShiftIndex", each [ShiftIndex]+[ShiftIndexIncrement]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"ShiftIndexIncrement", "ShiftIndex"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns",{{"NewShiftIndex", type text}})
in
    #"Changed Type4"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[EricSteynMMD]

Hi @mahoneypat  - Thanks for your insights as well... I found a solution to the problem, but does take a while to run, where the solution you gave seem to have given better performance - the only issue still outstanding on your solution was to accomodate for Equipment states that do not change for more that 1 shift (ie. we do not log any EquipmentState for more than 12 hours...) - would have loved to see the solution, if this above mentioned problem could have also been address by your method.

[mahoneypat]

To incorporate that would require going with a different approach in M, but I probably would approach that with DAX instead.  Since you have a solution, I won't spend time on that.

Regards,

Pat

[Anonymous]

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"

[EricSteynMMD]

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]

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

[EricSteynMMD]

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...

[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

[EricSteynMMD]

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 

[Anonymous]

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:

https://community.powerbi.com/t5/Power-Query/Data-overflow-error-when-the-quot-List-Accumulate-quot-...

[EricSteynMMD]

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!

[Anonymous]

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.

[Anonymous]

Waiting for clarification on the structure of the table and the various cases that arise, I propose a different way that avoids the bottleneck of the List.Accumulate function and should deal with lists of thousands of rows in a few minutes.

Speaking of this assertion, could you please test the code on your tables and let us know if and how it goes?

For now I am providing only a sketch of the idea, which can be easily completed using the code of my first post which dealt with a group of lines related to a single shiftIndex.

let
     ct = Table.TransformColumnTypes(tab,{{"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},
    
    lstR=List.Generate(
        ()=>[r=1,pos=List.PositionOf(ct[ShiftIndex],idx{0},Occurrence.Last), lr=listRows(ct{pos},ct{pos+1})], 
        each [r]<List.Count(idx),
        each [r=[r]+1, pos=List.PositionOf(ct[ShiftIndex],idx{[r]},Occurrence.Last),lr=listRows(ct{[pos]},ct{[pos]+1}) ], 
        each [lr]
    ) 
in
Table.Group(Table.Combine({Table.FromRecords(List.Combine(lstR)),tab}), {"ShiftIndex"}, {"shidx", each _})

the code refers to a a query (tab) contaning a table with starting data

[ImkeF]

Hi @Anonymous ,

I don't think that the main cause for the performance problems is the List.Accumulate Function, but instead the way you reference the previous row. 

In this example, I'm using the method I've described here: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/

It should perform well for this task, as long as you don't run it on multiple million rows:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc8xC8IwEAXgvyI3F3q5JKVmFjcXHUuGUIoG5JSS4N+3oUJMm+0NH/feDQMQtdi3hIQH1EYjNHALbg7xvSQBtilJZ0Qi18js+b4k2hJBRiZycZ7DxI7HqcrUys7OP+NcJ/1KcpmsEKXT5EcMp9eHs5H/m7F8S1UIlU07Io6/K7lJg7Vf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}, "de-DE"),
    GetPreviousRow = fnPreviousRow(#"Changed Type1"),
    GetNextRow = fnNextRow( GetPreviousRow ),
    #"Removed Columns" = Table.RemoveColumns(GetNextRow,{"StateDateTime.Prev.Next", "EquipmentState.Prev.Next", "ShiftIndex.Prev.Next"}),
    #"Extracted Time2" = Table.TransformColumns(#"Removed Columns",{{"StateDateTime.Prev", DateTime.Time, type time}, {"StateDateTime.Next", DateTime.Time, type time}, {"StateDateTime", DateTime.Time, type time}}),
    AddPosition = Table.AddColumn(#"Extracted Time2", "Position", each if [ShiftIndex.Prev] = null then "Last" else if [ShiftIndex.Prev] < [ShiftIndex] then "First" else if [ShiftIndex.Next] > [ShiftIndex] then "Last" else "Middle"),
    AddPrevCurr = Table.AddColumn(AddPosition, "PrevCurr", each if [Position] = "First" then { "Previous", "Current"} else {"Current"}),
    #"Expanded Custom2" = Table.ExpandListColumn(AddPrevCurr, "PrevCurr"),
    AddStartShift = Table.AddColumn(#"Expanded Custom2", "StartShift", each if Time.From([StateDateTime]) >= #time(6,0,0) and Time.From( [StateDateTime] ) < #time(18,0,0) then #time(6,0,0) else #time(18,0,0)),
    AddStartDuration = Table.AddColumn(AddStartShift, "StartDuration", each if [Position] = "First" and [PrevCurr] = "Previous" then [StartShift] else [StateDateTime]),
    AddEndDuration = Table.AddColumn(AddStartDuration, "EndDuration", each if [PrevCurr] = "Previous" then [StateDateTime] else if [Position] = "Last" then (if [StartShift] = #time(6,0,0) then #time(18,0,0) else #time(6,0,0)) else [StateDateTime.Next]),
    #"Inserted Time Subtraction" = Table.AddColumn(AddEndDuration, "Duration", each  Duration.TotalMinutes(
    if 
    [EndDuration] < [StartDuration] then ([EndDuration] 
     - [StartDuration]) + #duration(0,24,0,0) else [EndDuration] - [StartDuration]) ),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Time Subtraction",{"ShiftIndex.Next", "Position",  "StartShift", "StartDuration", "EndDuration", "ShiftIndex.Prev", "StateDateTime.Next", "StateDateTime.Prev"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "State", each if [PrevCurr] = "Previous" then [EquipmentState.Prev] else [EquipmentState]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"State", "Duration", "ShiftIndex"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Duration", Int64.Type}})
in
    #"Changed Type"

This code could probly be further optimzied and doesn't deliver correct results for first and last item, but should give a general hint on how a fast performing solution could look like.

This function code used is available in the file attached.