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.
Hello all,
In my data base I have a column duration ISO8601 format, Like :
PT1009H12M9.797S |
PT1009H11M42.082S |
PT10.982S |
I would like to convert all in minutes (or hours).
Any one can please tell me the simple way to do that.
Thank you.
Best,
Solved! Go to Solution.
Hi, @MF_BI
You can try to split the Period Time column into these three columns (hours, minutes, and seconds) in the PowerQuery editor before performing calculations
M query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCggxNDCw9DA08rXUM7c0D1aK1UEIGvqaGOkZWBghRPUswbxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Duration", "Duration2"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Duration2", type duration}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Duration2", type text}}, "en-US"), "Duration2", Splitter.SplitTextByDelimiter(":", QuoteStyle.None), {"Hour(h)", "Minute(m)", "Second(s)"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hour(h)", type number}, {"Minute(m)", Int64.Type}, {"Second(s)", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", " convert all in minutes(m)", each [#"Hour(h)"]*60+[#"Minute(m)"]+[#"Second(s)"]/60),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "convert all in seconds(s)", each [#"Hour(h)"]*3600+[#"Minute(m)"]*60+[#"Second(s)"])
in
#"Added Custom1"
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @MF_BI
You can try to split the Period Time column into these three columns (hours, minutes, and seconds) in the PowerQuery editor before performing calculations
M query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCggxNDCw9DA08rXUM7c0D1aK1UEIGvqaGOkZWBghRPUswbxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Duration", "Duration2"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Duration2", type duration}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Duration2", type text}}, "en-US"), "Duration2", Splitter.SplitTextByDelimiter(":", QuoteStyle.None), {"Hour(h)", "Minute(m)", "Second(s)"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hour(h)", type number}, {"Minute(m)", Int64.Type}, {"Second(s)", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", " convert all in minutes(m)", each [#"Hour(h)"]*60+[#"Minute(m)"]+[#"Second(s)"]/60),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "convert all in seconds(s)", each [#"Hour(h)"]*3600+[#"Minute(m)"]*60+[#"Second(s)"])
in
#"Added Custom1"
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@MF_BI , check if this old solution can help
also, check we should be able to use
https://docs.microsoft.com/en-us/powerquery-m/datetime-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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |