Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
HelenaX
Helper I
Helper I

How to convert min and second from text format to numbers

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?

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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

 

mahoneypat_0-1622078084416.png

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
mahoneypat
Microsoft Employee
Microsoft Employee

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

 

mahoneypat_0-1622078084416.png

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat  Thank you for the explanation! Really appreciate your help!

@mahoneypat  Thank you for your help!! I got it!! 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.