Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Javyellow
New Member

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. 

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

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:

TheoC_0-1646086075100.png

 

Hope this helps! 🙂

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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
Super User
Super User

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:

TheoC_0-1646086075100.png

 

Hope this helps! 🙂

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.