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.
I've extracted a timing report from the system. The original format is "h:m:s", but the final result is the whole number. How can I convert the number into the original format "h:m:s"
This is the original format in the system
and this is the extracted report
Thanks in advance 🙂
Hi @Anonymous ,
You can transform the data source table in Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZA5EsQgDAT/4tiBDgR4vuLy/7+xIwHZVhFQQ9M63veCCM91n8t3v5cNaCCcacDmiaGG1jdqT2WMzBCaaeS90iZQhQVTR5toC6aTsmQV0iCzUp/gKVjTseF/jSU3EI1pIwff5ViE7jR0KHvbtIAv8RQtM4GKB6xn/9kcaelLEvCOpjW08N+SPDAnn32Q0OewofkxzVyQLranwbM7qEPG2hDBwYcaj7VjnsWJpa+m1lJ8Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ACD Time" = _t, #"ACW Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ACD Time", type text}, {"ACW Time", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ACD Time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"ACD Time.1", "ACD Time.2", "ACD Time.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ACD Time.1", Int64.Type}, {"ACD Time.2", Int64.Type}, {"ACD Time.3", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "ACW Time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"ACW Time.1", "ACW Time.2", "ACW Time.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"ACW Time.1", Int64.Type}, {"ACW Time.2", Int64.Type}, {"ACW Time.3", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",null,0,Replacer.ReplaceValue,{"ACD Time.1", "ACW Time.1"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "ACD Time", each [ACD Time.1] * 3600 + [ACD Time.2] * 60 + [ACD Time.3],Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "ACW Time", each [ACW Time.1] * 3600 + [ACW Time.2] *60 + [ACW Time.3],Int64.Type),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"ACD Time", "ACW Time"})
in
#"Removed Other Columns"
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Sir,
This is my original file in Power BI.
https://drive.google.com/file/d/1CKe-cuDi3HvEynVyZ-cDAtqjgopmsauy/view?usp=sharing
Please help to transform from number to duration for columns "ACD Time" & "ACW Time". Sorry but I still don't understand the solution that you shared 😞
Please see this video for a great way to aggregate time values and format it as desired.
(7) It's About Time - Part Two - YouTube
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
50 | |
40 | |
18 | |
14 | |
13 |
User | Count |
---|---|
100 | |
63 | |
28 | |
18 | |
13 |