Convert String to Duration

[Anonymous]

Hi All,

I just wanted to ask for help on how to convert the Time to first response: Elapsed column, with type string, to duration by still containing the __h __m format?

The business users prefer to sort this column by still seeing the same format but can't since it is in string type. I tried doing things on my end but it is having errors as it does not display those with greater than 24h.

Would greatly appreciate your help.

Thanks.

[wdx223_Daniel · Accepted solution]

=Duration.From(Expression.Evaluate(List.Accumulate({{"h","/24+0"},{"m","/1440"}},[#"Time to first response: Elapsed"],(x,y)=>Text.Replace(x,y{0},y{1}))))

[ppm1]

You can add a custom column in the query editor with this expression (replace ElapsedTime with your actual column name). Once that creates a duration column, it will convert to a decimal # when you load it. Take a look at this article for how to then calculate your measures and format it at the end to show as hours and minutes.

Calculate and Format Durations in DAX – Hoosier BI

=#duration(0, Number.FromText(Text.BeforeDelimiter([ElapsedTime], "h")), Number.FromText(Text.BetweenDelimiters([ElapsedTime], " ", "m")), 0)

Pat