Convert ISO8601 Duration format to a duration in minutes(or hours)

[MF_BI]

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, 

[v-easonf-msft · Accepted 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.

[amitchandak]

@MF_BI , check if this old solution can help

https://community.powerbi.com/t5/Desktop/Support-for-ISO-8601-Date-Time-format-DateTime-ToString-quo...

also, check we should be able to use

https://docs.microsoft.com/en-us/powerquery-m/datetime-fromtext