Calculating the difference in time where time is in the same column

[Anonymous]

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. 

[Anonymous]

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

[Anonymous]

I would add an index column that starts from 1, then add:

= let PriorTime = List.Max(Table.SelectRows(PriorStep, each Table.Range(_, 0, [Index]))[Timestamp]) in Table.AddColumn(PriorStep, "Durations", each if StepID > 1 then [Timestamp] - PriorTime else null)

--Nate

[lbendlin]

Keep in mind that there are different serial numbers.

[Anonymous]

That being so, after adding the function, you can group by SerialNumber, and use the "Average" aggregation for the Durations column. 

--Nate

[lbendlin]

The serial numbers have different phase 1 timestamps. You can only compute the difference between phase 2 and phase 1 for an individual serial number.

[lbendlin]

Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.

[Anonymous]

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)

[Anonymous]

I made a mistake in seperating the column names it should be, 

SerialNumber, StepID, Class, RecutSerialNumber, UserID, TimestampID,

Sorry!

[lbendlin]

Here is a query that shows average duration between step 1 and all steps 2 of each serialnumber. The sorting step is not required, and should be removed to improve performance.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZY9bsMwDIWvYnguUJEiJVlbkE4tAhQIig5B73+NKjGZOBapDB5i5xN/9Piky2U+HD4AEQHhFDDMb3Nqz9Sepw8gH8r78o7t5wRQA1dapu9Te52X+e/tMn8ejrH9PzcEI23WevrA7QVs11pqoEpxXSqV21JdWossNVmZpBphxQvaeBnjIIUUsPE8xkMSPNj4teTj0eO5omSfneJpyLfmZeGzzccXPErzc7J5fMGHIDzbPIzaF2tUGVEvo7xPf5UOlHUBbHBl4Zdo8/iChyI8+vzZxwMLDja+rb7DSw2yeUvocd5vvvBp5enKszS/GEPITveCxg9YUbVfbP5a/c8Al/RL9vGvAS7NK8nHzz4OOnns46Pi75NrSE+NavJwqKTZG8pLdviH6ZUadW4dvI++pXXjsiFb2lnmnuYalDZUSzvHNGgU2hAt7QyzN3smMXvn3OBOcg863t0uGYojGZizT8u4JENwGvvLpaPSht6U9mPrrCRDbUq7cmm4BjfkooX7bQMN7siFhoXroCVHLoZLbXFVW3L0Eoep6/nCjl7iIHWsJGpjw+CU9vasObSMODtqM+z1CdfUHbn17rql9WBmR269uW5p0MIdufXeuqXVGtlR29DboLLijtxG3taMVW5EvMrtePiNCKU9JyjDtrXLTLjbMgefNttG7SZ4p2nxabNtNzrKZYKKT9ttu+GoeLZxt203Oijd1Pb3Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SerialNumber = _t, StepID = _t, Class = _t, RecutSerialNumber = _t, UserID = _t, Timestamp = _t, V = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"V", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"V", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([StepID] = "2")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"SerialNumber", "StepID", "Timestamp"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "FirstRow", each Table.SelectRows(Source,(k)=> k[SerialNumber]=[SerialNumber] and k[StepID]="1")[Timestamp]{0}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Timestamp", type datetime}, {"FirstRow", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Duration", each Duration.Seconds([Timestamp]-[FirstRow])),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"SerialNumber"}, {{"Average Duration", each List.Average([Duration]), type number}})
in
    #"Grouped Rows"

[lbendlin]

What's the purpose of the last column? Looks like an index (which would be fantastic)

[Anonymous]

It is, I can also add an extra index if needed. I might have sorted the columns by something else before pasting, and I cutoff some extra data to keep it a little simpler.