Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi ,
I have a column WorkedTime as Text. For example 08:55, trying to convert 8.91 hours.
and some of the columns has value morethan 24 hours Eg: 26:55, 31:40.
How can I convert these type of time values into number or am I able to apply filter in advance editor for these values?
getting data from SQL database in import mode.
Appreciate any help.
Thanks
Solved! Go to Solution.
Hi @pratapchava ,
For your requirement, I think you need to split the WorkedTime column firstly. You could follow my steps to have a try.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKzMjVVitWJVjI2tDIxALMMLMBisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkedTime = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkedTime", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "WorkedTime", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"WorkedTime.1", "WorkedTime.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"WorkedTime.1", Int64.Type}, {"WorkedTime.2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [WorkedTime.1] + [WorkedTime.2]/60) in #"Added Custom"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @pratapchava ,
For your requirement, I think you need to split the WorkedTime column firstly. You could follow my steps to have a try.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKzMjVVitWJVjI2tDIxALMMLMBisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkedTime = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkedTime", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "WorkedTime", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"WorkedTime.1", "WorkedTime.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"WorkedTime.1", Int64.Type}, {"WorkedTime.2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [WorkedTime.1] + [WorkedTime.2]/60) in #"Added Custom"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@pratapchava In PowerQuery you can use Duration.FromText
https://docs.microsoft.com/en-us/powerquery-m/duration-fromtext
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |