How to add HH:MM:SS and convert to Minutes only in Power BI

[smjzahid]

I have a column of data type (duration) that stores value in HH:MM:SS format. I want to convert everything in this column to MINUTES only in Power BI, How do I achieve this in Power Query or DAX, (if possible can we do this in 1 single step using Power Query) if not then can we do this using DAX 

Note: The below shown column has more than 1000 rows, (and more data will be added after every reporting period) I have truncated this here for explanation purpose

This is what I have 

This is what I want it to show on Power BI dashboard, (Hours and seconds converted to Minutes) and finally show the total sum of this column in MINUTES only (see below)

[v-angzheng-msft]

Hi, @smjzahid 

If you want to do it in just one step, then the answer @CNENFRNL  provided will work for you.
If you just want it to show up as one step in Power Query, then you can also combine other more steps in one step to make it look like one step. Then the answer @ronrsnfld  provided would work.

In terms of making the steps concise, you could use formula:

code from @CNENFRNL 

#"Total Minutes" = Table.AddColumn(Source, "Total Minutes", each Duration.TotalMinutes(Duration.From([Possession Handed back Time Only])))

or

#"Calculated Total Minutes" = Table.TransformColumns(Table.AddColumn(Source, "TotalMinutes", each Duration.FromText([Duration])),{{"TotalMinutes", Duration.TotalMinutes, type number}})

Result:

If you want to implement it via DAX, then you can create this measure.

_TotalMinutes = 

var _text=MAX('Table'[Duration])
var _len=LEN(_text)

var _p1=FIND(":",_text,1,_len+1)
var _p2=FIND(":",_text,_p1+1,_len+1)

var _hour=VALUE(MID(_text,1,_p1-1))
var _minutes=VALUE(MID(_text,_p1+1,_p2-_p1-1))
return _hour*60+_minutes

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ronrsnfld]

Please show the actual M-Code you are using.

It seems to work fine here, using the data you show in your question:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVGK1YlWMjC0MjSyMjAAcyAiJlYmZmCRWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Possession Handed back Time Only" = _t]),
   
//set datatype to "duration"
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Possession Handed back Time Only", type duration}}),

//remove the empty rows
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Possession Handed back Time Only] <> null)),

//compute the minutes for each row
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Total Minutes Only", each Duration.TotalMinutes([Possession Handed back Time Only]), Int64.Type),
    
//Rename the columns according to your specs
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Possession Handed back Time Only", "HH:MM:SS"}})
in
    #"Renamed Columns"

Then it's just a matter of adding the Totals row to the Table Visual on the Power BI desktop

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyMLQyNLIyMABzICImViZmYJFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Possession Handed back Time Only" = _t]),
    #"Total Minutes" = Table.AddColumn(Source, "Total Minutes", each Duration.TotalMinutes(Duration.From([Possession Handed back Time Only])))
in
    #"Total Minutes"

[ronrsnfld]

I was requesting the code that the OP was using, as your idea was working fine here.  Somehow the connection to his post didn't carry over.

[CNENFRNL]

Duration.TotalMinutes()

[smjzahid]

@CNENFRNL 

Tried your suggestion. but it throws below error, I have tried convertin the column to duration as well as to TEXT, throws the same error, 

any other suggestion please

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyMLQyNLIyMABzICImViZmYJFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Possession Handed back Time Only" = _t]),
    #"Total Minutes" = Table.AddColumn(Source, "Total Minutes", each Duration.TotalMinutes(Duration.From([Possession Handed back Time Only])))
in
    #"Total Minutes"