Forum Discussion

spittingfire's avatar
spittingfire
Regular Visitor
3 years ago
Solved

Excel Power Query equivalent for an excel formula for converting negative decimal hours

Hi there,

I am super new to Power Query in Excel and hoping to get some help. I am not sure if what I am asking is possible or not but thought I'd reach out and ask.

I have a column in an excel power query with hours represented in a decimal format. In addition, these are negative hours.

For example.

-0.65

-7.5

-0.083333333

-63.75

Using an excel formula such as "=IF(F2<0, "-" & TEXT(ABS((F2)/24),"[hh]:mm"), F2)" I am able to return a correct text format representation of what I need

For example,

-0.65 = -00:39

-7.5 = -07:30

-0.083333333 = -00:05

-63.75 = -63:45

Is there an equivalent way of achieving this using Power Query in Excel?

If yes can someone assist me with steps and / or formula?

Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi spittingfire ,

     

    Please try this:

    let
  sign = if [Hours] < 0 then "-" else "", 
  hh_1 = Number.RoundDown(Number.Abs([Hours])), 
  hh_2 = if hh_1 >= 10 then Text.From(hh_1) else "0" & Text.From(hh_1), 
  mm_1 = Number.RoundUp((Number.Abs([Hours]) - hh_1) * 60), 
  mm_2 = if mm_1 >= 10 then Text.From(mm_1) else "0" & Text.From(mm_1)
in
  sign & hh_2 & ":" & mm_2

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

6 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    You can do it with a formula like this in the popup box when you add a custom column.

     

    let 
sign = if [Hours] < 0 then "-" else ""
in 
sign & Duration.ToText(Duration.From( Number.Abs([Hours]/24)))

     

    However, not sure if you plan to add those up, etc., but it is better practice to keep them as a decimal (in days, divide by 24) and then FORMAT at the end.

    Calculate and Format Durations in DAX – Hoosier BI

     

    Pat

    • spittingfire's avatar
      spittingfire
      Regular Visitor
      3 years ago

      Thanks Pat,

      These will not be summed up as I expect that the end result will be in text format.

       

      Also once the hours goes over 24 in you solution it changes to -2 days, 15 hours and 45 minutes.  I will like it all to strictly be in hh:mm so in this case -63:45

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi spittingfire ,

     

    Please try this:

    let
  sign = if [Hours] < 0 then "-" else "", 
  hh_1 = Number.RoundDown(Number.Abs([Hours])), 
  hh_2 = if hh_1 >= 10 then Text.From(hh_1) else "0" & Text.From(hh_1), 
  mm_1 = Number.RoundUp((Number.Abs([Hours]) - hh_1) * 60), 
  mm_2 = if mm_1 >= 10 then Text.From(mm_1) else "0" & Text.From(mm_1)
in
  sign & hh_2 & ":" & mm_2

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • spittingfire's avatar
      spittingfire
      Regular Visitor
      3 years ago

      Thanks you so much v-cgao-msft,

      This is exactly the result that I was looking for and expected.

       

      Much, much appreciated!!

  • adudani's avatar
    adudani
    Memorable Member
    3 years ago

    hi spittingfire ,

     

    create a blank query and paste the following code into the advanced editor:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jXQMzNVitUBssz1oAwDPQMLYwiACJgZ65kD5WIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] < 0 then Number.Abs([Column1]/24) else [Column1]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type1", "Merged", each Text.Combine({"-0.", Text.From([Custom], "en-CA")}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Custom"})
    in
    #"Removed Columns"

     

    Kindly confirm on the last value (only different one).

     

    Appreciate a thumbs up if this is helpful.

     

    Please accept this as the solution if the question is resolved.

    • spittingfire's avatar
      spittingfire
      Regular Visitor
      3 years ago

      hi adudani,

       

      Thanks for your help,

       

      As with Pat's suggesstion you both arrived with the same solution however close but not what I am hoping for.

       

      First I am hoping for a solution that will only show the format of hh:mm.

       

      In the last entry it's -2 days, 15 hours and 45 minutes and it should be -63:45