convert Number to Minutes format

[Anonymous]

Hi all,

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

Hours Spent	Minutes
0	0
0.05	5
0.1	1
0.65	65
0:45	45
1	60
1.15	75
14:05	845
14.05	845
14.65	905
14:65	905

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

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]

For more details, please check the pbix as attached.

Regards,

Frank

[Greg_Deckler]

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