Converting elapsed time stored as text to minutes

[ska1903]

Hi,

I am very new to power Bi so this is probably a very simple question...

I have a column of time elapsed (duration) in the format hh:mm:ss that i need to convert to total minutes. I have tried changing the data type to duration but this fails as some of the records have an hour value over 24. e.g

Time Elapsed

06:55:57

07:28:01

24:27:47

02:46:58

00:47:52

01:09:11

16:58:21

03:14:48

185:14:26

110:15:29

Is there a way to get this data into a new column with just the duration in minutes, then I  think I can convert to days, hours, minutes. 

Thanks, 

[MarcelBeug · Accepted solution]

Tab Add Column - Duplicate Column

Select duplicated column - Tab Transform - Split Column - By delimiter (colon, each occurrence)

(This will automatically add a "Changed Type" step).

Tab Add Column - Custom Column, formula = 60 * [#"Time Elapsed  - Copy.1"] + [#"Time Elapsed  - Copy.2"]

Remove columns that are no longer required

Adjust data type of "Minutes" to Whole Number.

Generated code (the first step is because I did it in Excel Power Query):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Time Elapsed ", "Time Elapsed  - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Time Elapsed  - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time Elapsed  - Copy.1", "Time Elapsed  - Copy.2", "Time Elapsed  - Copy.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time Elapsed  - Copy.1", Int64.Type}, {"Time Elapsed  - Copy.2", Int64.Type}, {"Time Elapsed  - Copy.3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Minutes", each 60 * [#"Time Elapsed  - Copy.1"] + [#"Time Elapsed  - Copy.2"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Time Elapsed  - Copy.1", "Time Elapsed  - Copy.2", "Time Elapsed  - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Minutes", Int64.Type}})
in
    #"Changed Type1"

[NA2466]

Hi 

I am importing from a CSV as my data source (snap of the data table below). The resonse time stamp column has a time stamp recorded but the default data type is recorded as Text. My objective is to convert the data type of ResponseTimeStamp from text to time or any other appropriate data/time format for me to generate a time series plot of Execution time.  When I changed the Data Type from text to time from the drop down, its throwing the whole column in error.

The ResponseTimeStamp is captured in mm:ss.nn (minute:seconds.milliseconds) format and would like to retain the same format while converting from test to time. 

I tried to create a custom column with the below formula but its not recognizing "equals"

Time.FromText([ResponseTimeStamp]) equals mm:ss.nn

Would really appreciate some help on this. Thank you in advance.

ResponseTimeStamp                 ExecutionTimeInMilli

00:01.6                                         5

00:02.0                                         4

04:34.4                                         7

[MarcelBeug]

Next time please create a new topic.

If you add posts to an already-solved topic, only the few people that were involved, will notice.

You should prefix your values with "00:" for the hours.

Then you can convert the values to time or duration.

[NA2466]

Hi,

I have a CSV file I am importing as my source and one of the columns I have is ResponseTimeStamp which is being captures as below:

00:02.9

02:59.7

03:00.7

00:01.0

00:01.3

The default data type for this column is text. I need to change the data type to time (mm:ss.nn), minutes: seconds.milliseconds.

When I try to change the data type from the Data Type drop down menu it is throwing the values in the entire column in error.

I even tried to create a custom column using the below formula but it is not able to recognize "equals" 

Time.FromText([ResponseTimeStamp]) equals mm:ss.nn 

Could someone help me with this. Thank you in advance

[ska1903]

Perfect , Thanks