Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 d...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you! That worked perfectly 🙂