Process times

[jimrward]

I have a large data set and in simplified form here is a snapshot of the data, Column A Process Stage can be either 0 or 1, column B is a process step ID, column C is a time stamp and column D is a process ID,
I can use the process ID to group the data by for my other calculations on the data, what I would like to do now is as follows

1) for each Process ID calculate the duration from start to finish, Process Stage=0, Process Step ID=1 --> Process Stage=1, Process Step Id=10 effectively total job time
2) same as above starting at Process Stage =1, Process Step ID=1 -->Process Stage=1, Process Step Id=10, effectively Total stage 1 time
3) time in seconds between each stage

so below for for 48955002, total duration is 6m 19s, step 1 duration 5m 21s, time between each step at stage=1 , 32s, 26s, 21s, 28s etc for all 10 steps

Book1
	A	B	C	D
1	StageID	StepID	Timer	ProcessID
2	0	1	16:59:06	48955002
3	0	2	16:59:38	48955002
4	1	1	17:00:04	48955002
5	1	2	17:00:25	48955002
6	1	3	17:00:53	48955002
7	1	4	17:02:05	48955002
8	1	5	17:02:31	48955002
9	1	6	17:03:14	48955002
10	1	7	17:03:59	48955002
11	1	8	17:04:42	48955002
12	1	9	17:05:02	48955002
13	1	10	17:05:25	48955002
14	0	1	15:57:03	48555804
15	0	2	15:57:34	48555804
16	0	3	15:58:38	48555804
17	0	4	15:59:05	48555804
18	0	5	15:59:34	48555804
19	1	1	16:00:19	48555804
20	1	2	16:00:35	48555804
21	1	3	16:01:16	48555804
22	1	4	16:01:58	48555804
23	1	5	16:04:54	48555804
24	1	6	16:05:27	48555804
25	1	7	16:06:05	48555804
26	1	8	16:06:41	48555804
27	1	9	16:07:13	48555804
28	1	10	16:07:45	48555804
29	0	1	17:15:40	48a18404
30	0	2	17:16:14	48a18404
31	0	3	17:16:48	48a18404
32	0	4	17:17:29	48a18404
33	1	1	17:18:22	48a18404
34	1	2	17:18:58	48a18404
35	1	3	17:19:34	48a18404
36	1	4	17:20:14	48a18404
37	1	5	17:20:58	48a18404
38	1	6	17:21:34	48a18404
39	1	7	17:22:07	48a18404
40	1	8	17:22:43	48a18404
41	1	9	17:23:19	48a18404
42	1	10	17:23:57	48a18404
43	0	1	17:13:56	48a1a806
44	0	2	17:14:29	48a1a806
45	0	3	17:15:13	48a1a806
46	1	1	17:15:48	48a1a806
47	1	2	17:16:18	48a1a806
48	1	3	17:16:45	48a1a806
49	1	4	17:17:30	48a1a806
50	1	5	17:18:07	48a1a806
51	1	6	17:18:35	48a1a806
52	1	7	17:19:09	48a1a806
53	1	8	17:19:37	48a1a806
54	1	9	17:20:09	48a1a806
55	1	10	17:20:33	48a1a806
56	0	1	17:48:34	48b8a006
57	0	2	17:49:10	48b8a006
58	0	3	17:49:51	48b8a006
59	0	4	17:50:15	48b8a006
60	0	5	17:50:51	48b8a006
61	1	1	17:51:35	48b8a006
62	1	2	17:52:08	48b8a006
63	1	3	17:52:40	48b8a006
64	1	4	17:53:12	48b8a006
65	1	5	17:53:43	48b8a006
66	1	6	17:54:12	48b8a006
67	1	7	17:54:36	48b8a006
68	1	8	17:55:19	48b8a006
69	1	9	17:55:47	48b8a006
70	1	10	17:56:14	48b8a006
Sheet1

 

[JEC]

time between each step at stage=1 , 32s, 26s, 21s, 28s etc for all 10 steps

You only want this for stage 1? Because the data you are talking about there belongs to stage 0 too.

32s, 26s are in stage 0

 

[jimrward]

Ooops in my haste I included 32s, 26s
I have been looking at using an index column to bring in the previous row into a calculation of (currentrow - previous row) but getting myself in a mess

 

[JEC]

Here is my version. Everything in one Table.Group. I hope it is as expected.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.Group(Source, {"ProcessID"},
        {
      
            {"TotalDur", each List.Max([Timer]) - List.Min([Timer])},
            {"Duration1_10", each

                        let
                            tbl = Table.Buffer(_) ,
                            tbl2 = Table.Sort(Table.SelectRows(tbl , each [StepID] = 1 or [StepID] = 10 ),{{"Timer", Order.Descending}})
                        in
                            tbl2[Timer]{0} - tbl2[Timer]{1}
            },

            {"Steps", each  
  
                        let
                            tbl3 = Table.SelectRows(_,each _[StageID] = 1),
                            y = tbl3[Timer]{0},
                            timeList = List.Accumulate(tbl3[Timer],[prev = y, Result={}],(s,c)=>
                                            if s[Result] = {} then [Result={c-y}]
                                            else if List.Count(s[Result]) = 1 then [prev=c,  Result = s[Result] & {c - y}]
                                            else  [Result = s[Result] & {c - s[prev]} , prev = c]
                                        )    
                        in
                            Text.Combine(List.Skip(List.Transform(timeList[Result], (n) => Text.From(Number.Round(n*60*60*24,0))& "s" )),", ")
            }
         }
    ),
    cTypes = Table.TransformColumnTypes(result,{{"TotalDur", type time}, {"Duration1_10", type time}})

in
    cTypes

Book1
	A	B	C	D
1	ProcessID	TotalDur	Duration1_10	Steps
2	48955002	00:06:20	00:05:21	20s, 29s, 71s, 27s, 43s, 44s, 44s, 20s, 23s
3	48555804	00:10:42	00:07:26	16s, 41s, 41s, 176s, 33s, 38s, 36s, 32s, 32s
4	48a18404	00:08:17	00:05:35	35s, 36s, 40s, 44s, 36s, 33s, 37s, 35s, 38s
5	48a1a806	00:06:37	00:04:45	30s, 27s, 45s, 37s, 28s, 34s, 28s, 32s, 24s
6	48b8a006	00:07:41	00:04:39	33s, 32s, 32s, 31s, 29s, 24s, 43s, 28s, 28s
Table1 (2)

 

[JEC]

So?

 

[jimrward]

@JEC hi apologies for not getting back to you earlier I gave myself a 2 day pass away from excel
I was going to put my programming hat on (not M code I might add) and try and make a few adjustments As follows
Make the base for the timer tbl3 [Timer]{0} the last entry of stage0 timer, so that will give 10 timers for stage=1 as it’s a sequential process, an oversight on my part
For the 10 time slices I am thinking of dropping the “s” and creating 10 columns so I can play further tunes on the slices, MIN,MAX,AVERAGE for each slice, not decided on that yet but having the option later on would be good. I guess this would just be a split of the steps column but I am sure there is a more elegant way
I am a programmer of many years but this M code is quite an abstract beast once you get outside the button pushing in the interface

 

[JEC]

Ok great! You will get there
Cheers