Forum Discussion
Calculating the difference in time where time is in the same column
I am trying to calculate the time difference between steps of a process. This process isn't always 100% clean. A step 1 can generate 4 step 2s for each step 2 they should get a step 3 and 4. I want to know per SerialNumber the difference of time between step 1 and step 2, then I want to average that. NOTE there could be cases where step 2 hasn't generated.
I feel like I might have to transpose this into a horizontal table, but I really don't want to do that.
12 Replies
Ok, new approach: Let's say your last step is named LastStep. I would add a new step named "SelectList". In the formula bar, type:
= LastStep[TimestampID]
Then add a new step named AddDurations, and in the formula bar, type:
= Table.AddColumn(LastStep, "Durations", each if StepID > 1 then [TimestampID] - List.Max(List.Select(SelectList, each _ < [TimestampID])) else null)
--Nate- lbendlin
Super User
Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.
SerialNumber, StepID, ClassRecut, SerialNumber, UserID, TimestampID,
AAD1222121M020 6 AAD1222121M021 6 8/9/2021 11:05:49 PM 79 JAC3121721M234 6 JAC3121721M235 1 8/9/2021 9:04:43 PM 68 AAD1222121M020 9 6 8/9/2021 11:06:31 PM 82 AAD1222121M020 8 6 8/9/2021 11:06:19 PM 81 AAD1222121M020 7 6 8/9/2021 11:06:06 PM 80 AAD1222121M020 5 CC 6 8/9/2021 11:05:21 PM 78 AAD1222121M020 4 CC 6 8/9/2021 11:04:47 PM 77 AAD1222121M020 3 CC 6 8/9/2021 11:04:23 PM 76 AAD1222121M020 2 CC 6 8/9/2021 11:04:00 PM 75 AAD1222121M020 1 6 8/9/2021 11:03:39 PM 74 JAC3121721M237 3 CC 1 8/18/2021 2:39:59 PM 93 JAC3121721M237 2 CC 1 8/18/2021 2:39:18 PM 92 JAC3121721M237 2 S 1 8/18/2021 2:39:05 PM 91 JAC3121721M237 1 1 8/18/2021 2:38:07 PM 90 JAC3121721M235 4 CC 1 8/16/2021 4:38:50 PM 89 JAC3121721M235 3 CC 1 8/10/2021 2:02:29 PM 88 JAC3121721M235 2 U 1 8/10/2021 2:02:27 PM 87 JAC3121721M235 2 K 1 8/10/2021 2:02:25 PM 86 JAC3121721M235 2 S 1 8/10/2021 2:02:16 PM 85 JAC3121721M235 2 CC 1 8/10/2021 2:02:06 PM 84 JAC3121721M235 1 1 8/10/2021 2:01:45 PM 83 JAC3121721M236 2 CC 1 8/9/2021 9:08:37 PM 73 JAC3121721M236 1 1 8/9/2021 9:08:29 PM 72 JAC3121721M234 9 1 8/9/2021 9:05:09 PM 71 JAC3121721M234 8 1 8/9/2021 9:05:02 PM 70 JAC3121721M234 7 1 8/9/2021 9:04:54 PM 69 JAC3121721M234 5 U 1 8/9/2021 9:03:47 PM 67 JAC3121721M234 4 S 1 8/9/2021 9:03:40 PM 66 JAC3121721M234 5 K 1 8/9/2021 9:03:30 PM 65 JAC3121721M234 5 S 1 8/9/2021 9:03:27 PM 64 JAC3121721M234 5 CC 1 8/9/2021 9:03:20 PM 63 JAC3121721M234 4 U 1 8/9/2021 9:03:17 PM 62 JAC3121721M234 4 K 1 8/9/2021 9:03:16 PM 61 JAC3121721M234 4 CC 1 8/9/2021 9:03:09 PM 60 JAC3121721M234 3 U 1 8/9/2021 9:03:00 PM 59 JAC3121721M234 3 K 1 8/9/2021 9:02:44 PM 58 JAC3121721M234 3 S 1 8/9/2021 9:02:35 PM 57 JAC3121721M234 3 CC 1 8/9/2021 9:02:30 PM 56 JAC3121721M234 2 U 1 8/9/2021 9:02:23 PM 55 JAC3121721M234 2 K 1 8/9/2021 9:02:14 PM 54 JAC3121721M234 2 S 1 8/9/2021 9:02:06 PM 53 JAC3121721M234 2 CC 1 8/9/2021 9:01:56 PM 52 JAC3121721M234 1 1 8/9/2021 9:01:41 PM 51 CAW3218321M184 2 U 1 8/9/2021 4:20:45 PM 50 CAW3218321M184 2 K 1 8/9/2021 4:19:45 PM 49 CAW3218321M184 2 S 1 8/9/2021 4:19:38 PM 48 CAW3218321M184 2 CC 1 8/9/2021 4:19:28 PM 47 CAW3218321M184 1 1 8/9/2021 4:19:08 PM 46 I need to turn this into this,
I can do the aggerate things easily, but the time calculations where the time is in the same row and it can branch based on the class is very complicated for me.
- lbendlin
Thank you for providing the sample data.
You cannot have two columns with the same name. What's the difference between columns 1 and 4?
What's the purpose of the last column? Looks like an index (which would be fantastic)
- lbendlin
Keep in mind that there are different serial numbers.
