cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vanduongls68
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

Vanduongls68_1-1633971571306.png

 

and this is the extracted report

Vanduongls68_2-1633971634441.png

 

Thanks in advance 🙂

 

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @Vanduongls68 ,

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"

vyingjl_0-1634175566337.png

 

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 😞

mahoneypat
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

 

Pat

 





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

Announcements
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!