Forum Discussion

EricSteynMMD's avatar
EricSteynMMD
Frequent Visitor
6 years ago
Solved

Calculate Equipment State Duration per Shift

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 st...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

     

     

     

     

     

     

     

     

     

     

EricSteynMMD's avatar
EricSteynMMD
Frequent Visitor
6 years ago

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:00Startup1
2020/08/22 05:59:00Startup1
2020/08/22 06:00:00Startup1
2020/08/22 06:10:00Running2
2020/08/22 12:30:00Maintenance2
2020/08/22 14:30:00Failure2
2020/08/22 17:59:00Failure2
2020/08/22 18:00:00Failure2
2020/08/22 18:30:00Running3
2020/08/22 18:45:00ShutDown3
2020/08/23 05:59:00ShutDown3
2020/08/23 06:00:00Startup4
2020/08/23 06:00:00Startup4
2020/08/23 06:20:00Running4
2020/08/23 17:59:00Running4
2020/08/23 18:00:00Running4
2020/08/23 19:00:00ShutDown5

 

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:00Startup1 
2020/08/22 05:59:00Startup1 
2020/08/22 06:00:00Startup2from 06:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex
2020/08/22 06:10:00Running2 
2020/08/22 12:30:00Maintenance2 
2020/08/22 14:30:00Failure2 
2020/08/22 17:59:00Failure2 
2020/08/22 18:00:00Failure3from 18:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex
2020/08/22 18:30:00Running3 
2020/08/22 18:45:00ShutDown3 
2020/08/23 05:59:00ShutDown3 
2020/08/23 06:00:00Startup4Correct ShiftIndex, as the State change was logged at 06:00
2020/08/23 06:00:00Startup4 
2020/08/23 06:20:00Running4 
2020/08/23 17:59:00Running4 
2020/08/23 18:00:00Running4from 18:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex
2020/08/23 19:00:00ShutDown5 

 

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:00Running5
2020/08/25 06:15:00ShutDown8

 

Your Solution gives us this output because of the FillDown on ShiftIndex:

 

StateDateTimeEquipmentStateShiftIndex

2020/08/24 05:50:00Running5
2020/08/24 05:59:00Running5
2020/08/24 06:00:00Running5
2020/08/24 17:59:00Running5
2020/08/24 18:00:00Running5
2020/08/25 05:59:00Running5
2020/08/25 06:00:00Running5
2020/08/25 06:15:00ShutDown8
2020/08/25 17:59:00ShutDown8
2020/08/25 18:00:00ShutDown8

 

Where we need the following in terms of ShiftIndex:

 

StateDateTimeEquipmentStateShiftIndex

2020/08/24 05:50:00Running5
2020/08/24 05:59:00Running5
2020/08/24 06:00:00Running6
2020/08/24 17:59:00Running6
2020/08/24 18:00:00Running7
2020/08/25 05:59:00Running7
2020/08/25 06:00:00Running8
2020/08/25 06:15:00ShutDown8
2020/08/25 17:59:00ShutDown8
2020/08/25 18:00:00ShutDown8

 

Thanks again for the time and help on this!

 

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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's avatar
    EricSteynMMD
    Frequent Visitor
    6 years ago

    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's avatar
      mahoneypat
      Microsoft Employee
      6 years ago

      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