calculating seconds from a numbers/text column which represents the time in hours/minute/seconds

Question

In Power Query i have a column which, for example looks like this

9h8m4s

this means 9 hours, 8 minute and 4 second. the challenge now is that i want to convert this value in the column to be the sum up of the hour, minute and second to be only second which actually equals to 32884 seconds.

and ideas about how to convert it in PowerQuery for Power Bi?

Answer 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WssywyDUpVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",
    each let
        h = Number.FromText(Text.BeforeDelimiter([Column1],"h")),
        m = Number.FromText(Text.BetweenDelimiters([Column1],"h","m")),
        s = Number.FromText(Text.BetweenDelimiters([Column1],"m","s"))
    in (h*60*60)+(m*60)+s)
in
    #"Added Custom"