cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smjzahid
Helper IV
Helper IV

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

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 

smjzahid_0-1637513367740.png

 

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)

image.png

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-11-22 151000.png

View solution in original post

7 REPLIES 7
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1637730588902.png

 

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:

vangzhengmsft_1-1637730661912.png

 

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
Responsive Resident
Responsive Resident

Please show the actual M-Code you are using.

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

 

ronrsnfld_1-1637590199498.png

 

 

 

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"

 

ronrsnfld_0-1637590177315.png

 

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

 

 

CNENFRNL
Community Champion
Community Champion

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
Responsive Resident
Responsive Resident

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
Community Champion
Community Champion

Duration.TotalMinutes()

@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

 

smjzahid_0-1637587176422.png

 

CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-11-22 151000.png

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors