How to transform data in date time differences columm

[Javyellow]

Hi, I'm new but with a difficult question. 

How can I get from this: 

Time	Process	Message
25/01/2022 03:00:35 p. m.	PO PreProcess	PO PreProcess execution started
25/01/2022 03:00:43 p. m.	PO PreProcess	PO PreProcess execution ended
25/01/2022 03:00:47 p. m.	PO Download FS	PO Download FS execution started
25/01/2022 03:01:08 p. m.	PO Download FS	PO Download FS execution ended
25/01/2022 03:01:12 p. m.	PO Salse Order Entry	PO Salse Order Entry execution started
25/01/2022 03:01:18 p. m.	PO Salse Order Entry	PO Salse Order Entry execution ended
25/01/2022 03:01:39 p. m.	PO Download BS	PO Download BS execution started
25/01/2022 03:01:54 p. m.	PO Download BS	PO Download BS execution ended
25/01/2022 03:05:03 p. m.	PO PreProcess	PO PreProcess execution started
25/01/2022 03:05:12 p. m.	PO PreProcess	PO PreProcess execution ended
25/01/2022 03:05:15 p. m.	PO Download FS	PO Download FS execution started
25/01/2022 03:05:35 p. m.	PO Download FS	PO Download FS execution ended
25/01/2022 03:05:38 p. m.	PO Salse Order Entry	PO Salse Order Entry execution started
25/01/2022 03:05:44 p. m.	PO Salse Order Entry	PO Salse Order Entry execution ended

to this:

Starting Time	Ending Time	Process	Execution time
25/01/2022 03:00:35 p. m.	25/01/2022 03:00:43 p. m.	PO PreProcess	00:00:08
25/01/2022 03:00:47 p. m.	25/01/2022 03:01:08 p. m.	PO Download FS	00:00:21
25/01/2022 03:01:12 p. m.	25/01/2022 03:01:18 p. m.	PO Salse Order Entry	00:00:06
25/01/2022 03:01:39 p. m.	25/01/2022 03:01:54 p. m.	PO Download BS	00:00:15
25/01/2022 03:05:03 p. m.	25/01/2022 03:05:12 p. m.	PO PreProcess	00:00:09
25/01/2022 03:05:15 p. m.	25/01/2022 03:05:35 p. m.	PO Download FS	00:00:20
25/01/2022 03:05:38 p. m.	25/01/2022 03:05:44 p. m.	PO Salse Order Entry	00:00:06

The table has more than 10 thousand entries, so the process are the same. One processs starts, then ends and then another one. 

[AlexisOlson]

If you can index the processes, then pivoting is a decent way to do this.

Try pasting this into the Advanced Editor in a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZO7CsMgFEB/RZwL8UlTx9B2KxEyhgwhOhSCglra/n0zhZBgiZjROxzO1WPbQlwQXhBECKACIUE5kA94grIG0mnp7KC9X5+B/ujhFZ7WAB96F7SC3WmDYjQNpY2KgM4z6GrfZrS9AvdmM9hjhQUqU2ERLywwmVFNP3oNaqe0AzcT3Dcy3ueIyxxw1JdetqtX69WrnffIWSos4sUFSgwlasWXT5LT3ATihzXHl58qr7kJlZXGH0fGDmiu+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, Process = _t, Message = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type datetime}}, "en-US"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Message", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text After Delimiter", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Message]), "Message", "Time"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"started", "Starting Time"}, {"ended", "Ending Time"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Execution Time", each [Ending Time]-[Starting Time], type duration)
in
    #"Added Custom"

[TheoC · Accepted solution]

Hi @Javyellow 

Refer to the attached PBIX file.  There are a few steps that I've put together in Power Query.  You can follow them by looking at the Applied Steps pane or you can simply use the attached as the basis.

Your output will be as follows:

Hope this helps! 🙂

Theo