Forum Discussion
Solved
Convert number with text to number
Hello all, I have a Duration column on a report that I need to transform into total minutes. The current outputs are a variation of the follow three formats: 15 s 11 m 57 s 2 h 15 m 8 s I...
Hi SenoirB
I modified AntrikshSharma 's solution as below. You can use this code to create a custom column.
let result = Text.Replace ( Text.Replace ( Text.Replace ( Text.Replace ( [Time], " ", "" ), "h", "*60+" ), "m", "*1+" ), "s", "/60" ) in if Text.EndsWith(result, "+") then Expression.Evaluate(Text.Range(result, 0, Text.Length(result)-1)) else Expression.Evaluate(result)Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
To transform the Duration column into total minutes in Power BI, you can follow these steps:
- Create a new column: Right-click on the table, select "Add Column" -> "Custom Column".
- In the formula bar, use the following formula to extract the values of seconds, minutes, and hours from the Duration column:
= let
durationList = Text.Split([Duration], " "),
durationSeconds =
if List.Count(durationList) = 1 then
Number.FromText(durationList{0})
else if List.Count(durationList) = 3 then
Number.FromText(durationList{0}) * 3600 +
Number.FromText(durationList{1}) * 60 +
Number.FromText(durationList{2})
else if List.Count(durationList) = 4 then
Number.FromText(durationList{0}) * 86400 +
Number.FromText(durationList{1}) * 3600 +
Number.FromText(durationList{2}) * 60 +
Number.FromText(durationList{3})
else null
in
durationSeconds - This formula splits the Duration column into a list of values, and then calculates the total seconds by multiplying the hours by 3600, the minutes by 60, and adding up the seconds. If the Duration is in seconds or days, it returns the appropriate value.
- After creating the new column, you can change the data type to "Duration" or "Time" and format it to display in minutes.
- You can also create a new measure to average the total time in h:m:s:
Average Time = AVERAGE('Table'[Duration in Minutes])
