Forum Discussion
We couldn't parse the input provided as a Time value.
Hello experts,
I have excel file coming from SAP engine not correctly formatted (time displays as a text) see below
(unfortunately, I'm obliged to used it as) 😪
Picture 1
So far, when I try to convert above data into time I have this error message: We couldn't parse the input provided as a Time value.
I know it's because the hour value is greater than 23 but I don't know how to deal with.
Thank you in advance!
Best
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
1 Reply
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