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 pow...
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

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