Forum Discussion
Convert String to Duration
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.
=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}))))
2 Replies
=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}))))
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
