Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
79 | |
61 | |
60 | |
58 |
User | Count |
---|---|
151 | |
113 | |
99 | |
80 | |
72 |