Forum Discussion

Javyellow's avatar
Javyellow
New Member
4 years ago
Solved

How to transform data in date time differences columm

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

How can I get from this: 

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

 

to this:

 

Starting TimeEnding TimeProcessExecution time
25/01/2022 03:00:35 p. m.25/01/2022 03:00:43 p. m.PO PreProcess00:00:08
25/01/2022 03:00:47 p. m.25/01/2022 03:01:08 p. m.PO Download FS00:00:21
25/01/2022 03:01:12 p. m.25/01/2022 03:01:18 p. m.PO Salse Order Entry00:00:06
25/01/2022 03:01:39 p. m.25/01/2022 03:01:54 p. m.PO Download BS00:00:15
25/01/2022 03:05:03 p. m.25/01/2022 03:05:12 p. m.PO PreProcess00:00:09
25/01/2022 03:05:15 p. m.25/01/2022 03:05:35 p. m.PO Download FS00:00:20
25/01/2022 03:05:38 p. m.25/01/2022 03:05:44 p. m.PO Salse Order Entry00: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. 

Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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

     

2 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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

     

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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"