Forum Discussion

uie07923's avatar
uie07923
Frequent Visitor
1 year ago
Solved

Hours Conversion

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.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
    lbendlin
    Super User
    1 year ago

    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-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523