Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I am having troubles when I try converting a column within my dataset to duration. The column in my dataset records the duration as a total of hours,minutes and seconds (for example; 46:31:38 - where the format is hh:mm:ss). However, when I try converting the datatype of this column to durations, any cell with a value greater than 24 hrs shows up as an error.
@artemus I know you have solved something similar before. Can you please look into this and advise.
Solved! Go to Solution.
Try
Duration.From(
(Number.From(Text.BeforeDelimiter([Calculated Duration],":"))*3600 +
Number.From(Text.BetweenDelimiters([Calculated Duration],":",":"))*60 +
Number.From(Text.AfterDelimiter([Calculated Duration],":",RelativePosition.FromEnd))
)/86400)
or
Duration.From(
List.Sum(
List.Transform(
List.Zip(
{List.Transform(Text.Split([Calculated Duration],":"),Number.From),
{3600,60,1}}),
List.Product)
)/86400))
Stéphane
Try
Duration.From(
(Number.From(Text.BeforeDelimiter([Calculated Duration],":"))*3600 +
Number.From(Text.BetweenDelimiters([Calculated Duration],":",":"))*60 +
Number.From(Text.AfterDelimiter([Calculated Duration],":",RelativePosition.FromEnd))
)/86400)
or
Duration.From(
List.Sum(
List.Transform(
List.Zip(
{List.Transform(Text.Split([Calculated Duration],":"),Number.From),
{3600,60,1}}),
List.Product)
)/86400))
Stéphane
Hello @slorin ,
This solution has worked. I have actually only tried the first formula and it worked perfectly.
Thank you for this mate.
Hi,
Duration.From((
Number.From(Text.Start([Calculated Duration],2))*3600 +
Number.From(Text.Middle([Calculated Duration],3,2))*60 +
Number.From(Text.End([Calculated Duration],2)))
/86400)
Stéphane
I am now having an error output for cells with values of the format 103:46:23, can you please help me with modifying the formula to suit such cells as well?