Custom column with if contains

[njxfoster]

Hi Guys!

I want to create a custom column for hours in Transform Data which:

If column DURATION contain "hour", return text before "hour delimeter 

If column DURATION doesn't contain "hour", = 0

For example:

3 hours 51 minutes = 3

15 minutes = 0

1 hour = 1

How can I achieve this by using custom column?

Thanks in advance

[davehus]

Hi @Anonymous , No because when you convert the datatype to number, PQ drops any blanks in the column.

[Anonymous]

Two steps. 

1. Add conditional column. 

              If Duration contains "1 hour" return text "1 hours"

              If Duration does not contain Hours, return text "0 hours"

                   else, return column Duration

2. Split column by custom delimeter "Hours"

[davehus]

Hi @njxfoster ,

Create a custom column with the formula below.

if Text.Contains([DURATION],"hour") then Text.Start([DURATION],2) else 0

[njxfoster]

Hi Dave may I also ask if I also want to extract the minutes, How can I acheive it? 

For example:

3 hours 51 minutes = 51

15 minutes = 15

1 hour = 0

Thank you so much

[davehus]

Hi @njxfoster ,

Create a custom column to get the text position of minutes.

Text Position = Text.PositionOf([DURATION],"minutes")

Change datatype to number

Create another customer column

Minutes = if [Text Position]<=3 then Text.Start([DURATION],2) else Text.Middle([DURATION],[Text Position]-3,2)

Convert to numbers

[njxfoster]

Hi Dave!

This gave me error for minutes, Is there anyway I can fix this?

Is there any way can help me run the logic that if contain "minute" then take 3 character prior to "minute", if not contain "minute", then = 0

[v-jingzhang · Accepted solution]

Hi @njxfoster 

Try this code to get minutes:

if [Text Position] >= 3 then Text.Range([DURATION],[Text Position]-3,3) else if [Text Position] = 2 then Text.Range([DURATION],0,1) else 0

[Text Position] = Text.PositionOf([DURATION],"minute")

You need to first check whether Text Position returns the correct result. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[davehus]

Hi @njxfoster , if you click on the error it will give you a reason behind it. Screengrab it and post it if you can. You can change minutes to minute in Text Position Formula and see if that works.

[Anonymous]

This works, but won't it require a "clean" step to remove the space that's returned after the digit for rows that have 2-9 hours.

[njxfoster]

Hi Thank you for answer my question! can you answer the step with M-code form?