24:00:00 Records Changing Type to Duration

[Anonymous]

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 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.

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[Anonymous]

Hi @Anonymous ,

See this post, he solved his problem by converting the column from text to time and replacing value error to 11:59:59 p.m.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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

[wdx223_Daniel]

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

[mahoneypat]

Your 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

[Anonymous]

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