Forum Discussion
We couldn't parse the input provided as a Time value.
- 3 years ago
As you point out, these aren't actually times, so how do you want to convert them? What do they represent? A duration?
If so then you can convert these to durations but it takes several steps to format and convert each part of the data (days, hours, minutes, seconds).
let Source = "27:41:51", #"Imported Text" = Lines.FromText(Source), #"Converted to Table" = Table.FromList(#"Imported Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Column1.1", "Hours_"}, {"Column1.2", "Mins"}, {"Column1.3", "Secs"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Days_", each Text.From(Number.From([Hours_]) /24)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Days", each Text.Split([Days_] ,"."){0}), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Hours", each Text.From(Number.From("." & Text.Split([Days_] ,"."){1}) * 24)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Hours_", "Days_"}), #"Added Custom3" = Table.AddColumn(#"Removed Columns", "Duration", each Duration.FromText([Days] & "." & [Hours] & ":" & [Mins] & ":" & [Secs])), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"Duration", type duration}}) in #"Changed Type"That code will convert this
to this
My sample code is only working on 1 column, your image shows several columns. You can combine everything into 1 column to work on it. If you need help doing that please supply your data file so I can work on it.
Regards
Phil
As you point out, these aren't actually times, so how do you want to convert them? What do they represent? A duration?
If so then you can convert these to durations but it takes several steps to format and convert each part of the data (days, hours, minutes, seconds).
let
Source = "27:41:51",
#"Imported Text" = Lines.FromText(Source),
#"Converted to Table" = Table.FromList(#"Imported Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Column1.1", "Hours_"}, {"Column1.2", "Mins"}, {"Column1.3", "Secs"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Days_", each Text.From(Number.From([Hours_]) /24)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Days", each Text.Split([Days_] ,"."){0}),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Hours", each Text.From(Number.From("." & Text.Split([Days_] ,"."){1}) * 24)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Hours_", "Days_"}),
#"Added Custom3" = Table.AddColumn(#"Removed Columns", "Duration", each Duration.FromText([Days] & "." & [Hours] & ":" & [Mins] & ":" & [Secs])),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"Duration", type duration}})
in
#"Changed Type"
That code will convert this
to this
My sample code is only working on 1 column, your image shows several columns. You can combine everything into 1 column to work on it. If you need help doing that please supply your data file so I can work on it.
Regards
Phil