24:00:00 Records Changing Type to Duration

Question

Hi guys, how is everything?&nbsp;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 mentioned columns to duration, the records of 24:00:00 turn to errors, what to do to get the transformation done as 1.00:00:00? Bearing in mind that I got really many of these columns so splitting and merging columns back will be tedious, I read about this solution but I believe it will not work in my scenario.

mahoneypat · Answer

Your duration column will be converted to a decimal column (in days) when you load your data, so you can do that up front.&nbsp; Convert your column to duration (with the errors), and then add a step to then convert it to decimal.&nbsp; Then select your column, right click and choose Replace Errors and enter 1.
&nbsp;
Here's one way to do it in the query editor.&nbsp;&nbsp;To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
letSource = 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"
&nbsp;
Pat

anonymous · Answer

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 · Answer

Ah.&nbsp; 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).
&nbsp;
=&nbsp;(Number.FromText(Text.BeforeDelimiter([DurationText], ":",0)) * 60 * 60 +Number.FromText(Text.BetweenDelimiters([DurationText], ":",":")) * 60 +Number.FromText(Text.AfterDelimiter([DurationText], ":", 1))) / (24 * 60 * 60)
&nbsp;
Pat
&nbsp;

wdx223_daniel · Answer

= Duration.From(List.Sum(List.Transform(List.Zip({List.LastN(Text.SplitAny("0."&amp;"24:00:00",".:"),4),{1,1/24,1/1440,1/86400}}),each Number.From(_{0})*_{1})))

anonymous · Answer

Have you tried just splitting on the ":", and name them "Hours", "Minutes", "Seconds", make them type number, and then making a custom column, like

Table.AddColumn(StepNameAfterSplitting,"Duration", each #duration(0, [Hours], [Minutes], [Seconds]))

The hours should spill into the days.

--Nate

Forum Discussion

24:00:00 Records Changing Type to Duration

7 Replies

Related content

Duration Error over 24:00:00

Time duration is equal 24:00:00 or more does not working

Power BI do not recognize the value 24:00:00 as time

notebook duration time issue

Convert time to seconds (Time is in >24 hours fomat - 45:45:00 Hrs)