Forum Discussion
24:00:00 Records Changing Type to Duration
Microsoft EmployeeYour duration column will be converted to a decimal column (in days) when you load your data, so you can do that up front. Convert your column to duration (with the errors), and then add a step to then convert it to decimal. Then select your column, right click and choose Replace Errors and enter 1.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKxMjAAIqVYHSDH2MrExMrYWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DurationText = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DurationText", type duration}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"DurationText", type number}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"DurationText", 1}})
in
#"Replaced Errors"
Pat
The thing is, it won't be 24:00:00 always, it could be any duration that is longer than 23:59:59 which will get an error too, so, replacing values covers 1 scenario whereas we've endless probabilities here.
- mahoneypat
Ah. In that case, you likely need to parse it all out with an expression like this one in a custom column (or adapt it for a TransformColumns step).
= (Number.FromText(Text.BeforeDelimiter([DurationText], ":",0)) * 60 * 60 +
Number.FromText(Text.BetweenDelimiters([DurationText], ":",":")) * 60 +
Number.FromText(Text.AfterDelimiter([DurationText], ":", 1))) / (24 * 60 * 60)Pat