Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |