Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
@vat2do , I am assuming this in hours.
Try a new column
= time(0,0,0) +[Call Handle Time]/24
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |