cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vengadeshpalani Regular Visitor
Regular Visitor

convert Number to Minutes format

Hi all,

 

I have data like Hour Spent Field. how can I change into Minutes format? 

 

Hours SpentMinutes
00
0.055
0.11
0.6565
0:4545
160
1.1575
14:05845
14.05845
14.65905
14:65905
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: convert Number to Minutes format

Hi @vengadeshpalani,

 

Please check the followig steps as below.

 

1. Replace all "." to ":" in Hours Spent column in power query.

 

2. Split columns by ":" here is the M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSAeJYHSBLz8AUyDGFcgyBbEMo2wwkYQaVsTIB8UwgPJAqM4h+Qz1DkIQ5VMLECmycBUyhiR46H2yqpQFcPYIfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Hours Spent" = _t, Minutes = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hours Spent", type text}, {"Minutes", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",".",":",Replacer.ReplaceText,{"Hours Spent"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Hours Spent", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Hours Spent.1", "Hours Spent.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hours Spent.1", Int64.Type}, {"Hours Spent.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Hours Spent.1", "Hours"}, {"Hours Spent.2", "Mins"}})
in
    #"Renamed Columns"

3. To create a calcualted column as we need.

 

Minu = Table1[Hours]*60+Table1[Mins]

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User
Super User

Re: convert Number to Minutes format

I think you just need to multiply by 60, looks like a fractional hour. You first few Minutes values seem to be incorrect.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: convert Number to Minutes format

Hi @vengadeshpalani,

 

Please check the followig steps as below.

 

1. Replace all "." to ":" in Hours Spent column in power query.

 

2. Split columns by ":" here is the M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSAeJYHSBLz8AUyDGFcgyBbEMo2wwkYQaVsTIB8UwgPJAqM4h+Qz1DkIQ5VMLECmycBUyhiR46H2yqpQFcPYIfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Hours Spent" = _t, Minutes = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hours Spent", type text}, {"Minutes", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",".",":",Replacer.ReplaceText,{"Hours Spent"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Hours Spent", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Hours Spent.1", "Hours Spent.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hours Spent.1", Int64.Type}, {"Hours Spent.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Hours Spent.1", "Hours"}, {"Hours Spent.2", "Mins"}})
in
    #"Renamed Columns"

3. To create a calcualted column as we need.

 

Minu = Table1[Hours]*60+Table1[Mins]

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

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