Forum Discussion
Convert text value to time value I get error
Hello!
I got a calculated column in a sharepoint list that shows "hh:mm" and i've made is exceed 24 hours. But now I got this problem in power bi when i'm trying to convert the coumn to time or date i get error when the value exceeds 24 hours 😞
Anyone got any ideas?
Left is text Right is time
Hi Anonymous ,
Based on your description, you can try to get the values of hours and minutes and convert them to the "whole number" data type, and then use the "IF" function to get the results you want. You can refer to this below:
1. Get the values of hours and minutes
hour = LEFT('Table'[Column1],2) minute = RIGHT('Table'[Column1],2)2. Convert to the "whole number" data type
3. Use "IF" function
time = IF('Table'[hour]<24&&'Table'[minute]<60,CONCATENATE('Table'[hour]&":",'Table'[minute]),"23:59:59")Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
In the context of time, 26 hours makes no sense as that's a day and two hours - you probably want to look at duration instead, or depending on what your end goal is on your report, convert it just into a count of minutes
Hi Anonymous ,
Based on your description, you can try to get the values of hours and minutes and convert them to the "whole number" data type, and then use the "IF" function to get the results you want. You can refer to this below:
1. Get the values of hours and minutes
hour = LEFT('Table'[Column1],2) minute = RIGHT('Table'[Column1],2)2. Convert to the "whole number" data type
3. Use "IF" function
time = IF('Table'[hour]<24&&'Table'[minute]<60,CONCATENATE('Table'[hour]&":",'Table'[minute]),"23:59:59")Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
