Convert Text Data type to whole number

[Anonymous]

(1) Hi Power BI folks, can you assist me in how to convert this text dataype which written in terms of day,hour,minute and second 
     to  seconds because I cannot  use the AVERAGE(V_SLA[Business elapsed time]) measure 
(2) I need to convert it into whole number, find the average and then change the format back to original

(3) It returns error when I convert it to duration data type 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have create below column for Hour MIN SEC requirement.

PLease modify it little bit as you have days as well. Incase you are unable to do let me know i will do.

REq_Time_Format = IF(SEARCH("sec",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("min",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Time],1,0)>0,SUBSTITUTE('Table'[Time]," hour ",":"),"00:" & 'Table'[Time])," minute",":"),"00:00:" & 'Table'[Time]),"SEC",""),IF(SEARCH("min",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Time],1,0)>0,SUBSTITUTE('Table'[Time]," hour ",":"),"00:" & 'Table'[Time])," minute",":"),"00:00:" & 'Table'[Time]) & "00")

then go to modelling tab and convert this column to time format and type: HH:MM:SS:TT

then create one more column or you can add calculation in previous formula itself.

In_Seconds = 'Table'[REq_Time_Format]*86400

then perform your calculations &

 REq_Time_Format is already in your required format so you can display it as it is.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have create below column for Hour MIN SEC requirement.

PLease modify it little bit as you have days as well. Incase you are unable to do let me know i will do.

REq_Time_Format = IF(SEARCH("sec",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("min",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Time],1,0)>0,SUBSTITUTE('Table'[Time]," hour ",":"),"00:" & 'Table'[Time])," minute",":"),"00:00:" & 'Table'[Time]),"SEC",""),IF(SEARCH("min",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Time],1,0)>0,SUBSTITUTE('Table'[Time]," hour ",":"),"00:" & 'Table'[Time])," minute",":"),"00:00:" & 'Table'[Time]) & "00")

then go to modelling tab and convert this column to time format and type: HH:MM:SS:TT

then create one more column or you can add calculation in previous formula itself.

In_Seconds = 'Table'[REq_Time_Format]*86400

then perform your calculations &

 REq_Time_Format is already in your required format so you can display it as it is.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Hi @Anonymous 

Create one [Days] Column =

Days = IF(FIND("day",'Table'[Time],1,0)>0,LEFT('Table'[Time],FIND("day",'Table'[Time],1,0)-1),"0")

Create another column for remaining part=

Remaining_Part = IF(FIND("day",'Table'[Time],1,0)>0,MID('Table'[Time],FIND("day",'Table'[Time],1,0)+4,LEN('Table'[Time])-FIND("day",'Table'[Time],1,0)+4),'Table'[Time])

Create time format column 

Time Format = IF(SEARCH("sec",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE(IF(SEARCH("min",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE('Table'[Remaining_Part]," hour ",":"),"00:" & 'Table'[Remaining_Part])," minute",":"),"00:00:" & 'Table'[Remaining_Part]),"sec",""),IF(SEARCH("min",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE('Table'[Remaining_Part]," hour ",":"),"00:" & 'Table'[Remaining_Part])," minute",":"),"00:00:" & 'Table'[Remaining_Part]) & "00")

After that go to modelling tab and change date type = Time and format =HH:MM:SS:TT

and final Second Column/Measure=

In_Seconds = 'Table'[Time Format]*86400+'Table'[Days]*86400

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous · Accepted solution]

@AnonymousHi, I am getting the following error: DAX comparison operations do not support comparing values of type integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values

[Anonymous]

Hi @Anonymous 

Pleae  check your DAX.

In IF part part when condition is true you are returning interger value(Find() function returns integer position.)

In else part you are returning "0" which is string. So instead of using "0" use 0.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

And your DAx is not identical to my dax.

Please cross check it again.

Your IF part should compare your condition with 0 not with LEFT(......).

NOTE: FIND( ) and substitute are case Sensitive.