Convert number of minutes to hours

[DeyaM]

Hello,

I am working on this report with a live database that already has the column showing how long it took for a ticket to be solved in MINUTES.  Here is the column:

For the visuals, I need to show the duration in HOURS

What I tried to do was make a new column that shows the data from the time in minutes divided by 60.

[requester_wait_time_in_minutes.business]/60)

I got this:

Which I realised does not work as the numbers after the decimal point is not reflecting the minutes correctly.

I looked online for solutions but none of them seemed to be exactly for my issue.

Is there a specific formula or transformation in Power Query I can use to get the column to show the hours and minutes correctly?

For example on the columns shown:

The second row is 98 which is currently divided by 60 is 1.63 and it should be 1.38. I don't particularly need it to be in the format of 1h 38m it can still be just a decimal number but if that can be done it will be good too.

I know I can calculate that by multiplying 0.63 by 60 and that will give me the minutes but how do apply this method in Power Query language?

Thanks!

[vojtechsima]

Hi, @DeyaM,
see my solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrRQitWJVjI0NjIAM0wslWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Time", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each let duration = #duration(0,0,[Time],0)
in Text.From(Duration.Hours(duration)) & "h " & Text.From(Duration.Minutes(duration)) & "m")
in
    #"Added Custom"

Code for the new column only:

let duration = #duration(0,0,[Time],0)
in Text.From(Duration.Hours(duration)) & "h " & Text.From(Duration.Minutes(duration)) & "m"

[AlexisOlson]

You may want to include a component for days too or 23hrs and 1 day 23 hours are not distinguished.

[vojtechsima]

@AlexisOlson  Thank you for reminding me of that fact, I didn't realize.
@DeyaM With that your new Custom Column can look like this:

let duration = #duration(0,0,[Time],0)
in Text.From(Duration.Days(duration)) & "d " & Text.From(Duration.Hours(duration)) & "h " & Text.From(Duration.Minutes(duration)) & "m"

[Fowmy]

@DeyaM 

Try this as a new custom column:

Number.IntegerDivide([Minuits],60) +  Number.Mod([Minuits],60)/100

[AlexisOlson · Accepted solution]

Divide by 1440 (minutes per day) and change the data type to Duration.

This can be done in the ribbon as follows:

1. Transform tab > (Number Column pane) Standard > Divide > Enter 1440

2. Transform tab > (Any Column page) Date Type > Choose "Duration"

Sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrRQitWJVjI0NjIAM0wsIZSRhYlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [minutes = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"minutes", Int64.Type}}),
    #"Divided Column" = Table.TransformColumns(#"Changed Type", {{"minutes", each _ / 1440, type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Divided Column",{{"minutes", type duration}})
in
    #"Changed Type1"

Start: 

Result:

[smjparsons]

I had the same problem. Here is what worked for me:

Transform tab in ribbon> Standard> Divide> Enter 60

[DeyaM]

Thank you! It worked!