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

[spittingfire]

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.

[Anonymous · Accepted solution]

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]

Thanks you so much v-cgao-msft,

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

Much, much appreciated!!

[ppm1]

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]

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

[adudani]

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]

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