Forum Discussion
24:00:00 Records Changing Type to Duration
Hi guys, how is everything? I’ve a power query table with many columns that got type of “any” at the beginning, many of these columns got records of 24:00:00, when I change the type of the mentio...
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
Microsoft Employee
Microsoft EmployeeAh. 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