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:  Time Process Message 25/01/2022 03:00:35 p. m. PO PreProcess PO PreProcess execution started 25/01/2022 03:00:43 p...
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

     

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"