Forum Discussion
Convert the column into duration - Throwing an error
Hi
I have the below column
I am trying to convert it into duration. Getting below error message
Expression.Error: We couldn't parse the Duration literal.
Details:
24:18:34
I come across a similar post and suggested using the below M-Query.
= Table.TransformColumns(PreviousStep,{{"ColumnName", each let parsed = Text.Split(_, ":") in #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{1})), type duration}})
When I used the above query, I am getting the below message
Can anyone advise how to resolve this?
10 Replies
Hi bourne2000
Use the following Custom Column in Power Query:
try
let _arr = List.Transform(Text.Split([Duration],":"),each Number.FromText(_)) in #duration (0 , 0 , (if List.Count (_arr) = 3 then _arr{0} * 60 else 0 ) + _arr{1} , _arr{2}) otherwise nullThis works well and no need for mucking around with additional columns, etc., nor new queries, just a Custom Column.
Once you add the above, you can then just select Duration from the drop down box.
All the best!
Theo
Source: https://community.powerbi.com/t5/Power-Query/Duration-greater-than-24-gives-error/td-p/1084539
If you're going to use the Custom #duration( ) function then no need for the if else:
try
let _arr = List.Transform(Text.Split([Duration],":"),each Number.FromText(_)) in
#duration (
0 , _arr{0} , _arr{1} , _arr{2}
)
otherwise nullThat's a good pick up AllisonKennedy
Hi bourne2000
You need to add Custom Column and then use Duration.FromText ( [Your Column Name] ) then press okay.
Make sure to then click ABC123 and switch to Duration.
All the best,
Theo
TheoC Thanks. I tried
Getting below error message
Expression.Error: We couldn't parse the Duration literal.
Details:
24:18:34Error coming only the duration is more than 24 hrs. Please advise
Hi bourne2000
I believe the error is in your data, not the code itself. The error is basically saying that there is no such time as "24:18:34". It should be 00:18:34 if it is 12:18pm.
Basically what needs to happen here is that you need to split the column up into hours, minutes, seconds, and then divide hours by 24 to get days, hours. From here, you can then merge them back together. It's a quick process using Power Query.
bourne2000 Try something like this (paste into new blank Query in advanced editor)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0MrIAIqVYHSDH2MrA0MrEAMwxMrEytLAyNlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Talk Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Talk Time", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Talk Time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Talk Time.1", "Talk Time.2", "Talk Time.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Talk Time.1", Int64.Type}, {"Talk Time.2", Int64.Type}, {"Talk Time.3", Int64.Type}}),
#"Inserted Integer-Division" = Table.AddColumn(#"Changed Type1", "Integer-Division", each Number.IntegerDivide([Talk Time.1], 24), Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Modulo", each Number.Mod([Talk Time.1], 24), type number),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Modulo", {{"Integer-Division", type text}, {"Modulo", type text}}, "en-NZ"),{"Integer-Division", "Modulo"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Talk Time.2", type text}, {"Talk Time.3", type text}}, "en-NZ"),{"Merged", "Talk Time.2", "Talk Time.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns1",{{"Merged.1", type duration}})
in
#"Changed Type2"
