Forum Discussion
Convert Text Data type to whole number
(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
- 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:TTthen create one more column or you can add calculation in previous formula itself.In_Seconds = 'Table'[REq_Time_Format]*86400then 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.
5 Replies
- 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:TTthen create one more column or you can add calculation in previous formula itself.In_Seconds = 'Table'[REq_Time_Format]*86400then 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.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 columnTime 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:TTand final Second Column/Measure=In_Seconds = 'Table'[Time Format]*86400+'Table'[Days]*86400Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.