Text to Time

[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

[Anonymous · Accepted solution]

Hi @NA2466,

Based on test, current power query seems not support millisecond. In my opinion, I'd like to suggest you split timestamp to multiple columns with different time unit.

Steps:
1. Duplicate timestamp column.
2. Split timestamp to minute and second.
3. Split above second to second and millisecond.
4. add custom column to store real millisecond.(above millisecond *100)

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMjDUM1OK1YGwjfQMIGwTK2MTPROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ResponseTimeStamp = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each [ResponseTimeStamp]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"ResponseTimeStamp", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Minute", "Second"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Second", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Second", "Millisecond * 100"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Minute", Int64.Type}, {"Second", Int64.Type}, {"Millisecond * 100", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Millisecond", each [#"Millisecond * 100"]*100),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Millisecond * 100"})
in
    #"Removed Columns"

You can also refer to below link which has the similar requirement:

Time Duration including milliseconds

Regards,

Xiaoxin Sheng