Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |