decimal to MM:SS format in Power Bi Desktop

[vat2do]

Hi,

I am using power bi Desktop, I have column in decimal format like below:

Call Handle Time
2.90
3.65
1.23
4.71
1.25
0.90
0.75

i need to create another column showing the decimal values in MM:SS format. as per below output:

Call Handle Time
3.30
4.05
1.23
5.11
1.25
1.30
1.15

kindly support thanks

[v-yangliu-msft]

Hi  @vat2do ,

Here are the steps you can follow：

1. Power Query – Home – Advanced Editor  .

put the code in

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtKzNFCK1YlWMtYzMwUzDPWMjMEMEz1zQ5gIRMoApthAzxwoEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call Handle Time" = _t]),

    #"Added Custom" = Table.AddColumn(Source, "Custom", each Record.FromList(Text.Split([Call Handle Time],"."),{"first","second"}) ),

    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"first", "second"}, {"first", "second"}),

    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Number.ToText(Number.Mod(Number.From([second]),60),"00") ),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom1", each Number.RoundDown(Number.From([second])/60)+Number.From([first])  ),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"first", "second"}),

    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Call Handle Time", "Custom1", "Custom"}),

    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Custom1", type text}}, "en-US"),{"Custom1", "Custom"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged")

in

    #"Merged Columns"

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[vat2do]

Thanks this solutin helped a lot,

A quick question if in case we have a value like "1.09" or "1.04" so while splitting the value into two columns we will get  the value in 2nd column like "9" and "4" respectively but actually we need to have "09" and "04",

any solution to this point thanks in advance

[amitchandak]

@vat2do , I am assuming this in hours.

Try a new column

= time(0,0,0) +[Call Handle Time]/24