Please help to convert number to format like "h:m:s"

[Anonymous]

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 🙂

[v-yingjl]

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.

[Anonymous]

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 😞

[mahoneypat]

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