Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

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

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 🙂


Community Support
Community Support

Hi @Vanduongls68 ,

You can transform the data source table in Power Query:

    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"})
    #"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.


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 😞

Super User
Super User

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




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!