Hours Conversion

[uie07923]

Hello,

I have a report of compensatory hours. It can bring results in Positive or Negative number of hours.

In the original file, the negative hours have the "-" after the number, eg: 00001:00-

When importing it in Power Query, the positive hours are automatically converted to fraction of a day.

The negative hours, remain in the original format.

I need to convert everything into number of hours, negative and positive. They can be in the decimal format, eg: 1,5 would be equivalent to 36:00.

[Anonymous · Accepted solution]

Hi @uie07923 ,

What type is the Hours column after it is imported into Power BI? If it is text type, you can consider the following method.

According to your description, only the negative value are not converted. You can use IF to judge whether the hour of the current row is negative, if yes then convert it.

Here is my test for your reference.

Calculated column.

_hours = 
VAR _tag = CONTAINSSTRING('Table'[TimeOfHous],":")
VAR _hour = LEFT('Table'[TimeOfHous], SEARCH(":", 'Table'[TimeOfHous]) - 1)
VAR _minute = MID('Table'[TimeOfHous], SEARCH(":", 'Table'[TimeOfHous]) + 1, SEARCH("-", 'Table'[TimeOfHous]) - SEARCH(":", 'Table'[TimeOfHous]) - 1)
RETURN
IF(_tag,"-"&SUBSTITUTE(CONVERT((VALUE(_hour)+VALUE(_minute)/60)/24,STRING),".",","),'Table'[TimeOfHous])

——————————————————————————————————————————————————

If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

Best regards,

Mengmeng Li

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...