Struggling with cycle time calculation

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckosSc4wVNJRCnYDkSDsm5hXmgbmGCjF6qAoMQKSRjAlIIahKboSYyBpDFMCYhihKXFzB5EmQByQmJydmJ6Zlw5kmxqgqzLCosoYySwjHI7GUILhaGMDVCVu7iZE2OXmbopFlRmGWWZYVJkAVcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Batch ID" = _t, Item = _t, #"Step ID" = _t, #"Manuf Step" = _t, #"Cumulated Cycle Time" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Batch ID", type text}, {"Item", type text}, {"Manuf Step", type text}, {"Cumulated Cycle Time", Int64.Type}}),
    Transform = (tbl) =>
        let
            #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type)
        in
        Table.AddColumn(#"Added Index", "Step Time", each if [Index]=0 then [Cumulated Cycle Time] else [Cumulated Cycle Time]-#"Added Index"[Cumulated Cycle Time]{
                if Text.StartsWith([Item],"SF") then [Index]-1 else List.Last(Table.SelectRows(#"Added Index",each Text.StartsWith([Item],"SF"))[Index])}),
    #"Grouped Rows" = Table.Group(#"Type modifié", {"Batch ID"}, {{"Rows", each _, type table [Batch ID=nullable text, Item=nullable text, Step ID=nullable text, Manuf Step=nullable text, Cumulated Cycle Time=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Transform([Rows])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Item", "Step ID", "Manuf Step", "Cumulated Cycle Time", "Step Time"}, {"Item", "Step ID", "Manuf Step", "Cumulated Cycle Time", "Step Time"})
in
    #"Expanded Custom"

 

 

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

 

here is the part that does the work

  Transform = (tbl) =>
    let
      #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type)
    in
      Table.AddColumn(
        #"Added Index", 
        "Step Time", 
        each 
          if [Index] = 0 then
            [Cumulated Cycle Time]
          else
            [Cumulated Cycle Time]
              - #"Added Index"
                [Cumulated Cycle Time]
                {
                  
                    if Text.StartsWith([Item], "SF") then
                      [Index] - 1
                    else
                      List.Last(
                        Table.SelectRows(#"Added Index", each Text.StartsWith([Item], "SF"))[Index]
                      )
                }
      )