Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MF_BI
Regular Visitor

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

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, 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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"

74.png

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.

View solution in original post

2 REPLIES 2
v-easonf-msft
Community Support
Community Support

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"

74.png

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
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.