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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlePen
New Member

Transform time from text to time format

Hi everyone,

 

I have time data in the following format:

12d 8h 34m 12s

1h 3m 3s

23m 2s

15s

 

The data are recognized as text and I want to transform them in time format data.

 

How can i do?

 

thanks in advance

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @AlePen 

 

Your original time data is count period time specially having "12d" element is beyond the TIME elements . Which Time format value do you want to display if the original value contains day part (eg: 12d 8h 34m 12s)?

You can create columns to meet your demand, assuming transfer it to time format "HH:mm:ss" like picture below.

 

Time data = RIGHT(Table1[Time original],LEN(Table1[Time original])-IF(IF(FIND("d",Table1[Time original],1,0)-1<0,0,FIND("d",Table1[Time original],1,0))>0,IF(FIND("d",Table1[Time original],1,0)-1<0,0,FIND("d",Table1[Time original],1,0)+1)))

 

ML = IF(FIND("m",Table1[Time data],1,0)-1<0,0,FIND("m",Table1[Time data],1,0)-1)

 

HL_M = IF(FIND("h",Table1[Time data],1,0)=0,Table1[ML]-FIND("h",Table1[Time data],1,0),Table1[ML]-FIND("h",Table1[Time data],1,0)-1)

 

SL = IF(FIND("s",Table1[Time data],1,0)-1<0,0,FIND("s",Table1[Time data],1,0)-1)

 

ML_S = IF(FIND("m",Table1[Time data],1,0)=0,Table1[SL]-FIND("m",Table1[Time data],1,0),Table1[SL]-FIND("m",Table1[Time data],1,0)-1)

 

Hour = IF(LEN(LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1)))=0,"00",IF(LEN(LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1)))=1,CONCATENATE("0",LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1))),LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1))))

 

Minute = IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M]))=0,"00",IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M]))=1,CONCATENATE("0",RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M])),RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M])))

 

Second = IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S]))=0,"00",IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S]))=1,CONCATENATE("0",RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S])),RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S])))

 

Time Text = CONCATENATE( CONCATENATE(CONCATENATE(CONCATENATE(Table1[Hour],":"),Table1[Minute]),":"),Table1[Second])

 

Time Format = TIMEVALUE(Table1[Time Text])

 

As I don’t know how to treat the value which contains the day part, the final Time Format column doesn’t consider the day part value.

 

1.png

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXB2rDPFDrxImIWimt...

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-xicai
Community Support
Community Support

Hi @AlePen 

 

Your original time data is count period time specially having "12d" element is beyond the TIME elements . Which Time format value do you want to display if the original value contains day part (eg: 12d 8h 34m 12s)?

You can create columns to meet your demand, assuming transfer it to time format "HH:mm:ss" like picture below.

 

Time data = RIGHT(Table1[Time original],LEN(Table1[Time original])-IF(IF(FIND("d",Table1[Time original],1,0)-1<0,0,FIND("d",Table1[Time original],1,0))>0,IF(FIND("d",Table1[Time original],1,0)-1<0,0,FIND("d",Table1[Time original],1,0)+1)))

 

ML = IF(FIND("m",Table1[Time data],1,0)-1<0,0,FIND("m",Table1[Time data],1,0)-1)

 

HL_M = IF(FIND("h",Table1[Time data],1,0)=0,Table1[ML]-FIND("h",Table1[Time data],1,0),Table1[ML]-FIND("h",Table1[Time data],1,0)-1)

 

SL = IF(FIND("s",Table1[Time data],1,0)-1<0,0,FIND("s",Table1[Time data],1,0)-1)

 

ML_S = IF(FIND("m",Table1[Time data],1,0)=0,Table1[SL]-FIND("m",Table1[Time data],1,0),Table1[SL]-FIND("m",Table1[Time data],1,0)-1)

 

Hour = IF(LEN(LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1)))=0,"00",IF(LEN(LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1)))=1,CONCATENATE("0",LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1))),LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1))))

 

Minute = IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M]))=0,"00",IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M]))=1,CONCATENATE("0",RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M])),RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M])))

 

Second = IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S]))=0,"00",IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S]))=1,CONCATENATE("0",RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S])),RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S])))

 

Time Text = CONCATENATE( CONCATENATE(CONCATENATE(CONCATENATE(Table1[Hour],":"),Table1[Minute]),":"),Table1[Second])

 

Time Format = TIMEVALUE(Table1[Time Text])

 

As I don’t know how to treat the value which contains the day part, the final Time Format column doesn’t consider the day part value.

 

1.png

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXB2rDPFDrxImIWimt...

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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