Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
88 | |
84 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |