Workflow Query

[clarrock]

Hi 

How do I clean this Data? Where there are 3 Mechanics

Mechanic ID # - Date	Length of Assignments
1|1/09/2019	51
1|10/09/2019	59,46
1|11/09/2019	46,55,67,49,60
1|13/09/2019	71,62
1|14/09/2019	40,61,65
1|16/09/2019	70,31,61
1|17/09/2019	57,58,41
1|18/09/2019	62,43
1|19/09/2019	67,32
1|2/09/2019	43,46
1|29/09/2019	42,70,47
1|3/09/2019	51
1|4/09/2019	65,75,33,53,58
1|5/09/2019	32,56,63
1|6/09/2019	41,34,62
1|7/09/2019	49,74,73
1|9/09/2019	47,36
2|1/09/2019	68,37,58
2|10/09/2019	32
2|11/09/2019	65,46,32,42
2|12/09/2019	75
2|13/09/2019	50,36,39
2|14/09/2019	59,63
2|15/09/2019	44
2|16/09/2019	62
2|17/09/2019	50,62,55,71
2|18/09/2019	42,37,33
2|19/09/2019	48,50,32
2|2/09/2019	62
2|20/09/2019	74,49
2|21/09/2019	47,52
2|22/09/2019	46,60,38,45
2|23/09/2019	54,66,69
2|25/09/2019	75,34,47,53,52
2|26/09/2019	58,63,53
2|27/09/2019	57
2|8/09/2019	27,67,73
2|9/09/2019	49,31,44
3|1/09/2019	60,37,71,74,37
3|11/09/2019	75,28
3|12/09/2019	73,48
3|13/09/2019	43
3|15/09/2019	35
3|16/09/2019	71
3|17/09/2019	71,59,30,70
3|18/09/2019	44
3|19/09/2019	42
3|2/09/2019	31,74,72
3|21/09/2019	55
3|22/09/2019	55,39
3|23/09/2019	28,43,65
3|25/09/2019	42,41,36,38
3|26/09/2019	71
3|27/09/2019	31,48
3|28/09/2019	42,47

How do I get the average assignment Length for the Mechanic with the Longest Average assignments?

[jrgo]

@clarrock 

This looks pretty similar to a question on the core certification 🙂😉

You'll need to text to columns the first column then another text to columns on the second, but using the split to rows option. From there, use the Summarize tool to get the answer.

[Jonathan-Sherman · Accepted solution]

Hi @clarrock,

You'll need to use two text to column tools to separate out the fields and summarize the results, grouping by mechanic ID and averaging time.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan