Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a table column that has the time in text format. For instance, below are a few examples:
RunTime
35 s
6 min 6 s
6 mim
I need to sum the total RunTime, however the text format is preventing me from doing so. I tried to change the data type to Time but it didn't work. I also tried to use extract function but failed too becuase the format is not consistent (i.e. It is not always in "xx min xx s" format. When it is within a minute, it removes the 0 min)
Anyone has suggestions on how to convert this text format to numbers?
Solved! Go to Solution.
You can add a custom column with this formula (put this is the pop-up box when you hit Add Custom Column).
let
thislist = Text.Split([RunTime], " ")
in
if List.Count(thislist) = 4
then Number.FromText(thislist{0}) * 60 + Number.FromText(thislist{2})
else
if List.Contains(thislist, "s")
then Number.FromText(thislist{0})
else Number.FromText(thislist{0}) * 60
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You can add a custom column with this formula (put this is the pop-up box when you hit Add Custom Column).
let
thislist = Text.Split([RunTime], " ")
in
if List.Count(thislist) = 4
then Number.FromText(thislist{0}) * 60 + Number.FromText(thislist{2})
else
if List.Contains(thislist, "s")
then Number.FromText(thislist{0})
else Number.FromText(thislist{0}) * 60
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Just out of curiosity, what does the 0 and 1 in below formular mean?
===
if List.Count(thislist) = 4
then Number.FromText(thislist{0}) * 60 + Number.FromText(thislist{2})
===
I am guessing it has something to do with the position of the number in the text.
For instance, in the case of text
35 min 4 s
Does the 0 refer to the 35 and the 2 refer to the 4?
If so, why 0 position and 2 position? Why not 1 position?
The Text.Split splits your text into a list with either 2 or 4 elements. Since Power Query starts counting at 0, the {0} and {2} get the first and 3rd elements of the list.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |