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

     

     

     

     

     

     

     

     

     

     

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Microsoft Employee rankMicrosoft 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