Text to Time

[swinings]

Hey everyone.

I'm running into a problem I just can't seem to work out. I am trying to convert text like below to a time function that is usable (i.e. decimals or HH:MM:SS).

Instead of 1h 4m 46s, I would like 1:04:46 or in decimals if possible. The issue I am finding is that it wont show 0hr 48m 47s, so I can't uniformly split the columns.

This is what I am given from a data source, and I can't change their categories, so I'm working with what I have available.

Attached is a cleaned copy of my data source (below)

Thanks!!

Shannon

https://drive.google.com/drive/folders/1q9uo2QPfF_QGMKCtfllUyRvgzsYywTLD?usp=sharing 

[swinings]

Thank you everyone. Most of these are working - I am just now stuck on how to make sure I can attach this code to my sharepoint source, instead of the excel document. I'm not as familiar with advanced coding, so I have difficulty making it myself.

my current code

Source = SharePoint.Files("https://meorc365.sharepoint.com/sites/Kelli-Data", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"First name", type text}, {"Last name", type text}, {"Email", type text}, {"Course", type text}, {"Enrolled on", type datetime}, {"Completion date", type datetime}, {"Status", type text}, {"Time", type text}, {"County or COG you work in", type text}, {"Company/Employer Name", type text}, {"Are you currently an active Board Member?", type text}, {"Role", type text}, {"Month", type text}, {"Year", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
Time1 = #"Removed Columns"[Time],
#"Converted to Table" = Table.FromList(Time1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

[AlexisOlson · Accepted solution]

Borrowing the total duration logic suggested by @serpiva64, you can do this in a single add custom column step like this using this formula:

#duration(
    0, 0, 0,
    Expression.Evaluate(
        Text.Replace(
            Text.Replace(
                Text.Replace(
                    Text.Replace([Time], " ", "+"),
                    "h", "*3600"
                ),
                "m", "*60"
            ),
            "s", ""
        )
    )
)

It's slightly easier to understand if you break it into a couple of steps:

Here's the full code for the above. You can paste it into the Advanced Editor in a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TVAxDsQwCPsK6nxDgZD03lJ1z5Lp/i+dUUjoZsDYmPs+uAxS+R3PB5gHlWvicgG36OsgOwML+ovfCdulRmWDmPekDqq78C0rs9RO+n1RIcFhyt5PH14+125LzRO54fQJDQybsG1ugy8ScaRQGEncIJwpEI4jHMQlZATZEMjWO1DiIglpc7z2T2iVTZOSNCSHhqT/MvKgWvPrC8OVIWf5YC+Xk+lOqf7QwLVTS5J/yNpr4bUhaaMe7PkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Added Expression" = Table.AddColumn(Source, "Expression", each
        Text.Replace(
            Text.Replace(
                Text.Replace(
                    Text.Replace([Time], " ", "+"),
                    "h", "*3600"
                ),
                "m", "*60"
            ),
            "s", ""
        ),
        type text),
    #"Added Duration" = Table.AddColumn(#"Added Expression", "Duration", each
        #duration(0, 0, 0, Expression.Evaluate([Expression])),
        type duration)
in
    #"Added Duration"

[Syndicate_Admin]

This is how you should format code and examples in this forum, btw. I wish the admins would start pushing back on unformatted code dumps.

[swinings]

All the others have worked to various degrees - but I think this is the easiest for me to understand.

I have the excel document linked to a sharepoint. Here is the current code for the Time table I am making...how would I add the above code to it?

Basically - my code (below) + the above = working code that will apply when data is updated.

My Code from Advanced Editor:
Source = SharePoint.Files("https://meorc365.sharepoint.com/sites/Kelli-Data", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"First name", type text}, {"Last name", type text}, {"Email", type text}, {"Course", type text}, {"Enrolled on", type datetime}, {"Completion date", type datetime}, {"Status", type text}, {"Time", type text}, {"County or COG you work in", type text}, {"Company/Employer Name", type text}, {"Are you currently an active Board Member?", type text}, {"Role", type text}, {"Month", type text}, {"Year", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
Time1 = #"Removed Columns"[Time],
#"Converted to Table" = Table.FromList(Time1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

[AlexisOlson]

You should be able to Add Column via the GUI and paste in the first formula I gave.

Or you can append the end of my full code at the end of your code, referencing the last defined step. Something like this:

let
    Source = SharePoint.Files([...]),
    [...],
    Time1 = #"Removed Columns"[Time],
    #"Converted to Table" = Table.FromList(Time1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Expression" = Table.AddColumn(#"Converted to Table", "Expression", each
        Text.Replace(
            Text.Replace(
                Text.Replace(
                    Text.Replace([Time], " ", "+"),
                    "h", "*3600"
                ),
                "m", "*60"
            ),
            "s", ""
        ),
        type text),
    #"Added Duration" = Table.AddColumn(#"Added Expression", "Duration", each
        #duration(0, 0, 0, Expression.Evaluate([Expression])),
        type duration)
in
    #"Added Duration"

[swinings]

Thank you!!! I was finally able to get it to work correctly.

[serpiva64]

Hi,

to achieve this:

You need to pass these steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tV1Zcxw3kv4rFX6SIsahwlF98I2maK9jJFkjyuPYmJiHVrMo9rrZrenDNv/9AoWzUF+iUCQnYh48bTMTlUjkffzrX9+9v/7l01X18fxlu1lXn9r1/nB7/O5v3zH2hvE3nFWMX7B59fG9+q1mb2r5hnP9Wy2qS/3b1f7h27Y9tbf6b+RDJfjxu3//7V/f/bTf7L5WX9rH/e62Wp+Pp/1De6iO7eGPzbqtXl3dXLP6tf4b/kbUHZ76gluYAc+FWBrUPTTsoZILg+Zjezjud99ftbtTe2hvq4/b1W6nMd/tD9XNzeUxBre8YMsBBrkYYpALhWH+gh8iL2oJPkQ8VE39MmgajWZxIebgWrj6mhe6lsbQsZkBNPeVun85e0FEis8aAejWPFSMYQb4fL/Z/a5R3/y+2W6P1cfV4VSx/pWzWeDnDo+4EDOA5r6aPVSzCXg4jWdmCCdqSDh9RY2MMa221Y/nnXmK6h0uOhJdSJFA1K8TPBFxX4llINJzL8OguuDgqeg3z+xjvDq0q9PmjzYQ59UPP9c1fx2DUQdmyUfwC4FuWX+AAXx5VGftIKv/4OG826wVnv1OnfyyrmcpeD4A39QAvCI5s6/i/Wq3+qrP++s3A3M5BlP4r+jBXERH7ghwum+rT5uv96fqH+f2qA99VBiu67pJMaQ3K6Ds47Mg+557q3MrMhBLzpUkt+x42P+xuVWASVG7Uujf7tfnB/VvzL18Pqw2+l/F3zO/aJoEt+IoQMRGEbEhnoL/UyWCZCCYgVZrpTQAN/dX8tN+f1u929y18StdXNTcH4vV5icuhySZq1eqFA+bP1XuAExLrVoHmIR6Ulw+VexANJIN0XAW1MIkNYoxAI2g1Buz6u3m/O3b3jzhz+36frff7r8+pnxCQ28WQ+jqJfAG3qy7RiESQNpuAYKeK82lVEqzGELDslcZR+ZY4OlIBUyJFm6Z7v2vP3cPpBMCx+SL7anqCyHDSZnn7wHsRgOedmVZFA24s1pxhSy8tBx1FEoJbk2Rh8tAninPiP4UxrR9hRSrutYJ9Mp+D/N408fqmPxqf9tW+zulpHa35/Wp+r76bX/ovua3drut/tyc7qvrP9qDEthaPr+/rhevYxRKD/IEq9JdHKtGMSt/WQ6WlAl4oaU/NLAd+M2D1oxt9e2wX7fHY3UCDNx/adxIewmYSz00pvir4SVvg1tZvEhAL7VihEZUHV5HybO7qIMh4kBLII8bkSikMnEM2dWiaZDY1zagxXPTbu++v7x9UIc+Kop3SlUbQcejVrI0p3JrTABDbXZfzQN9Piu7ZO5OrVn2sDkpq2qbmG7sdQw46OsIl7pnaKU085KbEI5GLNBI2OeGxEd3F5ZI764vP3Sv96e9elQ7S5k8VH7BwXvSpOezCUxZLwBgYHwohheLKXRg9RAw9IEVuys7iTsiYyc+huL83Pg35FxpH245dmQiOpAFrKVxE6zXX4+ay37Y3z5W71a7r+fV19bY3s5dyJwce9MKuCP2MySxw1APkHKEVFv8TXhTb9WnKFtZof7n5nBWD7Yj4PvV9s/VobPJB993US8HmJA3yiMv/mZ1154eU5oP4TTobS4eCngyf8EQsGZJrW5FliWzgLkXyQlHCpZ8+AgY5KnyJjzyIqVpv5Wl7Me1sYs+X0sRawp8WP2x+arktuK4/92fD9WH9s/qXbs6dBaHUR8JllmCRVzUc/ARihZNuUBRlEj5WHhnNn2cSrTKulD/jKEABJJKPYvJ5OGOu1MkC0ifRXAK9HP8UQP//PhNvcNf7jQG7ci+encdaTZpndMQGWTWD2HAgulCnLMJREJGgIUvgRkjtFMmyi+4x502yNkAZ8/GmBzvaGn4bbtat9X/rA4reKuxOJFOO3Fw4iaKo4yfWLELAgwsa60763CdedgOUOw7ut+Quci1HTGq5oj36QAjr5qZMIGwd3j9n/Nquzk9miCJkjeHo/5/ncphGZXjUCAvMObBMRsInJvwlRYhAnnT/qVOHbGGOe9AcQ3Oy7xh1AOtXUp7iz9ulBo0Ury61Dpxp52KDrwoAM/Rk2x8rKpIrJMvUsFHTydmwnFyp89RA0UxeW0YKoqLF1AXPiUDXDnN5kWRCGHSFGJwfg6FuWZz5U40lg0v1x3Rb+73+5OWsPo+56P3iZ0HzYmOvafTBZwfaWt1dms6fzgfOkv0bbttv6LwZY7kDbAztJFbJFnS5xh+044LJHntg5nllLEga5EeXysgZGC4cMYIsyNmgSCZEeSyyCGsKSXhA76pGOeTKeKOP2BIndca3mfIo9AUydxlg8y4KMQ7KlNip9vBBHzXmQ4SB0QpFsYGt04dNb0Eaz8E7y97CA5FHrR37fRh+WtLeYvr3CbiLanzRLNJ4Pnw7gXMRSgbhGco2odj8yk1elVdJNI9LEjThf7r2YWMAgCLfjw81amOgcosIyYHgBmI2Td18P8nS9+YUx0OoPyWITo0EUWf4BYFsu9iaj87gUXezfKiBgaJdqSm3E2ISY0A1kmKQqnRF84WJrKyO2NVvoAEtTg4SIk0QfhD1qeudqkJk3U4igw++vaQAS9EiGHQp4X0RekgEXh9Uq4DSGyHBdnA99UyCNkX4HjqSrQgQVIj8i2nhpbdXSyS69GoCA/5hb8TXKZOlaMY/SxkL2gpvoxT3R4ecvd1MK4s8OxkXcrE2uYAB9XO88T0K33nDDr+TSRjixx/CBifXfOTJJK7NmAjxZASMJuuLITIYSYso4HmYt1v6GxdcVidgYbYiWmLFp2N18ELLE3pZugpwUV1TMbH1AbJuQxybqfkygMoiCYc1mvoOKp6ZYygSYnQHF5mCBL39CmLIpKjliPJdyFtCwJBT0kcZj4DFkrJEMuaqMKXgImwob0o8oAweSSAtwxmB+1iYHDISZMh9F4u6xBTQkYRNrk8OVpDPiodyRqmaEOGp8Ru1I5BenqBclRd5lcsnyBkIIVgPlKGvOz0JwvRNDgG0si0bAk7p4A4KIKga3BdTVd51gMcmGuTHTr6Qk4AT50eCxkR5Txf0M9hCbtySLomChkXGC/apEqtF0Gl0pbTKlCScO7C3BHxlNUzE/Ngq348Pq7vjZDY34V3/Ordh+vESJU+vOV/ashUtBOYDt7N6XHbHg1YPg4WBl1Z8M27k+//NLnm95v1Yb/tnVyMo4DlvLrE0d7p2/auI3SgyS9f/q/tIr32O+Q4EoHlHFnsnNN/jeZMFMp1Ie4ghM/b0/nQVptdV4Eb0l3dsWfjx4YFDAt/qz8d9n+e7hXd1aHbk4G6HIM6g/bOMtTMv5Q/A0g305mZoXSahYYNhfxh3+m0m/16o26k/xIW41+HnpsWr87E8gD1V1yr/2b/2LbV9e7r6mtrskwa0XwcETSCmpCNmKifZ73UhAu6ofcXFR9lMqgOwjwBuqS08yK47lehvl2dVb0JxQ03p4NJkl0OvWYUhUAnn4fI0XjhDQXbZIAx6a1A/VkT99uhPRE4RIqjAY43QXr3TD7roh1dYbM3de4KrCw4OjRbeEi3a4l6qSwTBfnmtD88ntrttmN/jaBJEaSXq3O1qJSyjpJkJV0MNIJgFCRfYBSwa2G47AqQOpjzUWIzXeSAqWK55VoLhU5Cv9uoq12tHw3sRcF54TtdhOwY1XWRgwntBRafd7XZVtenzX/O7elk2MO0QRCEWDpfFEXZm6BbJlm1IFqzpOOjOkGWqYnOWuh9We+QgOY8GZULvoCiQQkvix1Wrs8ntmzxEUoS1ShShNDB1BQp+BiiNEWEioPyQqnhRWnoRL3xhBog2XsnDjCqlOD37umVH7sfZnLQUc+QmAc1NqW+DpFFoP7CRSge/XvbfvP3+nZ1WnU1Jaa+clBHshzCl+hSF0FHfu7ato6/dwHsT6vdcf8QCjibBAEDH4AaVrSPzIvu1fs0IuVHpg0TJECiqs7iHOuQ8rjCRs7piKetAucDIhOg7qvOiprCJyAU5FEAMuuaAOfCfLzfHO+dPLvcnTbf33xbPSgL9+4U7jMtVBneJ4FoHknUyfG9fgjB4ZFYPTRBqlnrXJnNyiluD8ZCuakHBczoK1CCSreBsfKSUiQPcN1U11JF1CMgp93DAsqLmzQ/zxUlkFTFkRlW/N2gx8UBRi4P7x6jZJFBvGs7s1IzxddjdXk6rda/H821LUevDUYYusDPEhOXVJmwxIGZbIsrsG7X2m/+1Cqzr/VF8/qkXKYn5YOToqCmTnBYF/NTe/y2391qiKd9tYoFq6GJQTRLEYkBIolMbVlYd+UYbcB8uFRMNy2VBmYpachhYZQ00UxWpBMyp4YeXxfncmkBOmxPvkSBiXxfxTnLl4o59riV26ZPlNdsonhCYS2BA8eQaJUhNfakdmo+ODVHjbDLqKbgv5G0dshRqamRR2I0UEu0VHjQuOlORAHDkUjt0OrgjvtQtQu3Jessc9sU8+irIYqz62DL/FfqJMIBiC5FWVov72Rdk8JmkMk6fVuUjx8aVB6wwFQTkUajZcnSRNOiVgoPF/YkNCEQX0AJBBgXPTNjZLLmGQ97QBucrdbG5nKCLUsyLJE+mvsBJGWPi6UygXHY4iuU6gnzNaYUk1mwWpwiVdmZkwV36sSKSCUorjdXqlIU1clQdMD9mboumeHxEOQrETALoUs5okrhiUVQDjJL5Yjw7b2pze7IoTH9vX2s/rnantsOl+s5+VvUcKIJ9vNuvT0fu4jjW4C7SXFLHHBkvnR+YsOjgdoMmWnmG9PT6gwnKItDOe7kA4E8gymdfu6os0y73lBtqbbrk+Ez889d/ut0r+hZvXr36X1Cvpkv8/Y/zb3hOhB67qtGHXVK8M0xN8tQnXa1391tN+oTuiiry698Tm9jruNb6bnhTIAmqOL0pVCnXFB9DFHByqSwKo2ImpLwlDkqOTTAim9CUhbaKBS0JdZdoWTo+dZJBjVudHDxtSJdRkNn4DnPgwUxospouFAWzlP6j0fOMxjwBfMp4V7SWqnxjUdybnrpTN/2tINd4OSEOhKnZRHCvt7PwNbVprM0PDMlGAa/Ankcy8LBEgbGwo8p9D/pmDu2cXWV6BT2p0ikw8uoXHYeBH+BZRRBiSETvb1di82USXWOh4CR41HhiTpdUWNJ3MzbnzK9WiI+3iUQpshr9xHDqAt3I3tQBbmOeC1C1dfEaoG+8e5HA6G626i4YsqLIO+E6cTl0O8LTYFPLPO24DmccCGDQT/pK8hLwdMZWBdlcxKkvFQO0InDFqxuKNfyxVQrjRxVu2rpyEpK5bx5xxKyCTzMw1SzjQYQqdgOz7jny14V3nOdaDfHgSogKRvcBKgujFNBUEd7Si4o8kPb1Ufcrr6dVl+2rfHAXFTbULnxt6d/kkaRo0COVhZNyKYrcA/f3+y3f3jPbj4OFyU6tM0q4+Mq70MJpXV7d94awMsxwHPN/jDS2at6dLWDN5ttu+uu8BqcejkALlF6pgnl16UVTB7FYoAChhp5sB9f4PmCsDe3Yzlg5jy0uoyWT5EftoBZxK6wfDGteiqDAjVDLktzDMNWce5miqCTm+4VVhTucbJ/cGQzthNaGoM60acbNbkZCe47cDoNmjF+NALu9dUpkuYJg4NzJ4chkuK5ZTnAwBW084PLRke4sZ0NgE20bAcvN881jkM4gA3L7cwkT1HEkm4MD6AJ0Xugo72UmU34mjQWqLK6oSBFDo47qkjlP54OYAd28PKGvb5r48YFEPMdpE/yZpx7PLTQgUaV4nNF8znZCsjtqMk6FeICAtOjR3l453QFrwPSpC9ewumBPCocmtoy35uexe0Qd+RiL+N8Bsj395qMHCCkSOeGCk8WG3YCPCq+49HciDHqLuKGXweUmvg4nrysw2BokdIBay/ljxdrxF6HvgGq1RZq6LWz3MsFkIgnWXvYqMnatsU0JUE0JHYcbFQdKsxYXjbW5ge1oQdMbAVgwbwfGRHvjy2Hx4bTd5sgHDIzMG1AiSOoyKMOtTVjw1nA+yWiNLybD+g6zeIBjpnTQRchkmEjkVTq+bJu9i28q1mosy+XZe4LGMCDQmE+0FYasM18CBJEyqYuDiPQL4XBYm0Z1SRkmkDIS2Vwunw3zHVK8IMS+kSQSEdo4+UsUIM4odMDht70vOsolZlqvBxVCRelLKjoBeR8CJgIjkZHLS2NRc+aKCM0tQZFgd1UkvVg42GW8mnzRPsJUIcD9XrIkWEGHEJCLTq6RrN+yRgbzUO4Rb1pShUvsBs9YOTR1RMTib02EPSuOExlaeFbNN11wPHhN8rDXTxMOz5hq1skyO9tzOCGKS0A9A37uHGaIOjP0Bp3v2h5AacsCz5Bc+RgY8d3XlhIGh7awO70XlkqhaKeY9JoIRU1rGlh8iGn5qigsoWIUvKd/1UUiqJPCqtdoza7IsYjr47Dqg09S2d8Krp/8v1WdwcY+Y5ubQFRWQGqmSwwOLvRlHKLqTNHcm8dY9KxkKim9bnJAbeEC2EalP1Pk70DA0ngMRDG9C0LvTRGfQjgW6FxinZmUFH+x822ju3Gbn3SDCaVXQHueOyFYvqZDRtj4PqSJ5sg0Eec9SV9jGcW3m5hBmuWwF3ivXdG3j6l0Ai8u1nGMzfWtSwf5NWbRudBc+KGefiKEYcPZKg9cGJBjpy8TDGHBbkLoYYVi7e+sZ2DxG1snb/MyBNC7vkT4JxHl/or0WEeUDSUPAtca5midSHIbvKA0bAudc+zEDWY8hTox0wPQO+aWsr3Xy31Oxt8B5yOr6gf1ZEV1CHOXBgCT4bj0cilNLhM0hhXlUgT+Stq3A2cwYbAUXiOdQKmLEcOCi8caELKs0gGpFRAPmsOnB2VJZ9SMkJKXeZ1LTj5pPGavbbyvmfmUaFnz0L2vFwP5mgHtXlZ0LHvkjuADQq0ibQECY+rHJIBdzuMDXIlHyDHTYxPm9yNvh8HZ8JmgJdqVMO0Qi7DfGSGNsnseIh23Fzw36kMzu2TDSPtXyCsQ5qhRFxHdsKPle/R6k1E8KCpdsZZNEINRTJzx0X7CxalLi6qQXGbdlELzLxwGcqgBa4AsDPAfZ/KL1+0+2YH1OgUjcikaDL7gTv78uUq2slXAwNGdvIh1mzM3eEQFgqGaBs/6rCbZENRpgRuINfKfh7SvdO1AeZ1/QG59QD9fL8DBGIWc0OJoniIScoKzxoRaNiycT9tokI6RTJ99oIKusio2BQTOOccYLC8HxR5gsMJPCEJGXtmvuApzS2AVDZxjvLZ2tmZsLiD9SoG/VpmbHZ2QZEpLgmnn5NDRbjQMzrLAVKLHhjy0Uyg6Kltqw40XMhsgjlFoIExMgY6Su8/tb1n7rwp1ErfeDe/vBMWfQENnpK9IwOyYmXhUEAPLnLfpsx1GsLnlDYSBWKdZm9omtn5HU1Raw8h1OZ0gNqULUyY9N3bWWAgNzDbZd23og5wb3DxSGy5YW646mTKbG9o47m9BNjzbMK0ES+r3u2/attp+2gsplnGYnLRaVRMLYNqKhrIE9PbwcWSq6tftKd+26668YZdv+uvu3V7OCngJ3v2xdjZtVgnHGVJFNK6s6U0JroClCycrhpQtMojQR5iM7FJJ9N85/Hg5DiXaTA6cTTslOpoXpn7CUIMomSSLUqyOq4btQO75BSjJtPa50ZlEymY+km9RvQ3ETKNPyct0LeT7RhrlHVv7AcVDZni1tiTA9CoG3uy6dTzRHolh8J2HEn68sVoVSC4AGEzuHDsjjGinFaZHlFDeECEV9LCKAMJDdGRZgBZk3m/5s/nPnDtf9I2GS7J6lbilqQFDSARDyo2PzXQ0uiC/WSgjr+p7Zf6DE70Ex536RRS2VUhM8OhIAZ3dlnw8QSahxKWvYTfuJ+e1iMGC2+lAHKkoDxgvOTeuOll+91dUH6RUEPgIbnWNCqvIg6eeo/QGLaItoLSTXHqzNo5YvGQFgWbG4MA8UnXvGal3Q/nzbabp/H5cD72BmlUrz7/dm2HaDp4faK7XkFUxN+ZMOPhtQDYl4NHgAlztDEGx1Wvg+2Xb+1OW3T60POCQ8O2lODW9cyut5u7u836vD1V7pVuThs9XF8jW44ig3MWtHILjX45y9TBkd6viH6CEaRoiydV+uoPV/tp5/FvMOgT5uqXGKUKmjCSNqaIrZtgyFqPFpuXddgFeE2doFjCyumoa+dqr1DsDMd/Xm3VP757f1O9sqAZe1MvbQVMzQewYdl/tGfLT813vK7+DdPAm1HgxJS1uF1KscuxUv9bVTdXv3x6X13/tdL/YXWlJP2x1WhmJWjQfIUm5Fmzfa0RA0byzEFGY4d1YqRIh5qSstBI1wOME9mzYPi+PWy65t6fd7t9HBZvXhOg4t8aVMvQeFt3yrCoPPFxHXH3AEajySR5sFp1UepRNZI7MC3jHd2n1B4GqKhZ0o3JKIlazwha4EU43OS8nWed63HJUkPA9Ew3uEVMl4/RND//m8SUmQe53usNv1Qa6fGoJIIGb6fo58EjF1JX7EgvHUu6w4P89ca0+sk1USPLQ6aNmUm2dZBrC/DQLHAWBUv1JK2DEupdBGlzXB82D5udlQBvY6onTd7+pyUOaMaTTOkJAgS5HWDkV0QLTDW51Tk/tKc/7Sjfj5vT3UrLmVcfPv8W1B19djhBJepMyBthsq8z/U8Mb9gxvYkiv+M2A5ZDsJ07NK2pKY8DTX0uKs+JXBX31D1cicdbzUJ5Z8bmkv3X7X9q8EbuOs15l4QPMjRZQldDe8xy8gScLBrYnsHTOeyJtwvA+dIc5IoSgUeYZsiBZ1SBXDToaCQ2nQNPrZ2UUf/ipDmU3Co+xsPsKf8bXjMbbYrJtnZGYJYDyKi4S0ux8arg2PJfDOEC+ui4PUflqJjStmwZzQPgZoyU+/znFjdk8C9h6Lrbc1CcccPg7cp6uJu2LhHESJr5HBhdC99Mm+s5hgjZZKFYZvyJ+fDRIgVONQtosVzQaxaJgjjWZvNtUJiZLA0r7gXFF7vI2EqubrKZaJIluSX/UzSUfqIVH+gcNczwzkLm1JS4TuQUFStRtrabpA9jBTLoklGXLwUU/4aqSPXRXQ47V8iPr9TtWEB2k1337Wyy0bgPOLlbcyFRCEKEAuGR+UeQT3ydN+rSWPj7LA7O0BhgH0i0t/E5jUo8FLkgf7gOLkho7/d/w3wiJIIDnUlR1CMYsxpPAM9ga6PVV64NaZpjzWy0moOwoB5uR7WQZJNRaRtuQAPHtzQhDfG0w6NiNh6Yg9wryP2MIxHxQs59rCetFczAz6TZnXIuWCuYRwCVhIhakcnVggBI/BsaRBCPO8u7vUhGechISM18cGekDw7wXRZwN7BaEsl2qvE4g4SY7+OMosIgT44+DaJ8VA0/Jbme+RAU15yHISclPdQkdIYzx1E6erysJwMcLdriy8LKmxxgOEozzbfR0W/6Vhmu6om2ndH5efK89G4sObndJc1Rx0hQWX+uFiZ3YFRl/mKLLXOYYZVSMKSLGs9z4NGH9Rc9jEVmae4JpXNp5HeyH5BDgsJvcbnNuPVIQseLTOycSGf5jniqi7gegdk+buT9NqbG3blh+S24hBp0w8eQSRoPNcNrcHNAMzUUrvEuswd3BDKaf9rNnAob7Q772/O6ezynvbI6Vut7Y9FcBy4J4OYpCphOElE9m2ESD/f9/rbVAWsNnheAh2yy9BMn0wLwYd6L2ckHKOojTNqrGc84RodslskhcVWXrdKWqGElFoA8OanIxad86vioM3NuHWag7tV5ezof7PxfMUpeAXdgsKjSClV+rDrJaHDIURwSyqpuFoG3eTtz5dStaVWUr/5xbo+aIS2bNK/78ESdkEzCMSrRuIDyyJTdltdEKFwbEZoEPvAK3inrqO3AvtKCy13Ma/Mls8yX2CwlDAMvelt6ctOeI0BiABteNg/+ngb+84O+3JUGqzD8tFdS8kbJHC8SFhk0NsqGIngiWJKFVWd1VyfOepucAw5UmCO8VJjoUdoIHlJ3IjYJXCAgC0fgPaKMGHzqgmazOJvpICGek7byW1AGFyGqXHQPzlVbhiTD80PfGfTUQKuovzalDogVs0w5sOj268LlRrmTCbwey0wgduPCSgLzORRISgn5lJE4ObosqaFSPNruOCmDR6LSpguae9yk34QqVgmAaO3sIlSSPz8xQyNn8Onqko3yVUoEgqUxWOE+URbqe0cCySRkIkrAzcRlRnRhUjlQGg3z+n2QXZqQ4sjBh0tPl6Hot+gNDuv9skue42XxpM/hFJ5zsT1QbP7ZUZLTJknnsSDtHQzM0cQYCXqBG/WjRHwqRoeQ/MJcRIrbSlfciyi7nN9nFyWNZDAnfSc8KjvthtDnJ25Avgg7TYkB9NGirSniGZHIOq1oJIyuH5Q4ke3duyjlELZfgWivtE70fIwaOcCwIjyar1PO0FksKBo+Ytvk4EkgPBYhXPcCpg2NHacgedEecx4NMJglDIMXvYtg7I4/fPrQcKdjVwlKBJcJlYGsHm5L3WvEo9EouknPisSjb5+YtdTQCyqpz0EU82hQfQWfMvE5i4BpkwoJiHL1lwMOF1k/0TDM3AZePi2j5CI14T0mxOD8HJrpg6Wd4xfs3qwEKNAOFbsrjxdnQ7I4sBskinaJw+gnty1JhHsl/ECY1sjsy/V6f1Yi7stG7xY2nr2LVHpYQ/DI2I/k60+mxOL67k6v2FUs+mPb3n5ZrX/vEPiAZQYBYfwXFedAtnETOIHdrbuFn6DZDZpeiIjbXnlqn8oTn1cGEypalbGfEtUs0HThuLQgWsNAl/rQUAVefj4rnaTkzN0m+WqJ41fKRuOFRRa+cDwKKLk1ZXCblynunJArjktN6lmChLDXZmbtHznZLfpzKYYg4TIv7lm7RFK5vWAyJQsxqy82it1+cmM6+RXlr67eX/v0AXddQNGD90ul8LCACs1xxE/C5pNQHoJ3S1YaT4q/1FkjTjandD5HOFJ6TDwazAzTYFM9PE9Z3iTUxoGbWShxLVRuiEh2hxCqiJDRLtuU3OisPtJPD7i1WvLHzaGtjCiqLvUK+Z3JPyqiixzR/YoDPMbcW3NvD2djsl9u1/v7/ba6/HLWrW0agSxBgMvvQvH/JL1A0woXteoe1WXIHGZzfGGjQXqn1Cy8OlQn5rUmfWxq7g0Licly6YIwcK084A2P7WQJEPgyIQgxiywSsfkgG/l6BExkKemtw0eOIKS3HW6rSeBKGObnpq6XF00C0l1uNvMpInrYeT1I6djsZEGBDA6buFpzItJch6TqCEUWPa2TDKNOMyjurfTMGzd7apGAIQbyNNEeymfHAyBvu1QHal4SIdFR9HYAM7oxMkjf8Wmh6gx4tChAuOU7//5/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Course = _t, #"Enrolled on" = _t, #"Completion date" = _t, Status = _t, Time = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Time", "Time - Copy"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Time"),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","m","*60",Replacer.ReplaceText,{"Time"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","s","",Replacer.ReplaceText,{"Time"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","h","*3600",Replacer.ReplaceText,{"Time"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","d","*86400",Replacer.ReplaceText,{"Time"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value3", "Time", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Time.1", "Time.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Course", type text}, {"Enrolled on", type text}, {"Completion date", type text}, {"Status", type text}, {"Time.1", Int64.Type}, {"Time.2", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Time.2] = null then [Time.1] else [Time.1]*[Time.2]),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Seconds"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Time.1", "Time.2"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Index"}, {{"AllRows", each _, type table [Course=nullable text, Enrolled on=nullable text, Completion date=nullable text, Status=nullable text, Index=number, #"Time - Copy"=nullable text, Seconds=number]}, {"TotSec", each List.Sum([Seconds]), type number}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Course", "Enrolled on", "Completion date", "Status", "Time - Copy"}, {"AllRows.Course", "AllRows.Enrolled on", "AllRows.Completion date", "AllRows.Status", "AllRows.Time - Copy"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded AllRows", {"Index"}),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #duration(0,0,0,[TotSec])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each true)
in
#"Filtered Rows"

I know it isn't a great exemple of code but it function.

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Anonymous]

You can split your column by delimiter "h ", then split by "m ", then by "s". Then replace any nulls with "0". Change your new columns to type number. Then you can make a custom column:

= Table.AddColumn(PriorStepOrTableName, "Times", each #time([Time.1], [Time.2], [Time.3]))

--Nate

[tackytechtom]

Hi @swinings ,

How about this:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTJVTA2KlaK1QGyDXMVTCwgbBMLINscKm6cq2BqAGUbAcVh6jMUgLpNzKA801wFQ0O4jFmughmcA9YF5MUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Hours", each if Text.Contains([Time], "h") then Text.End(Text.BeforeDelimiter([Time], "h"), 2 ) else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Minutes", each if Text.Contains([Time], "m") then Text.End(Text.BeforeDelimiter([Time], "m"), 2 ) else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Seconds", each if Text.Contains([Time], "s") then Text.End(Text.BeforeDelimiter([Time], "s"), 2 ) else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Hours", Int64.Type}, {"Minutes", Int64.Type}, {"Seconds", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type1", "NewTime", each #time([Hours], [Minutes], [Seconds]))
in
    #"Added Custom3"

 Let me know if this works for you! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Greg_Deckler]

@swinings You should be able to modify this to parse your text duration column:

Text Duration Conversion - Microsoft Power BI Community

Then you probably want something like this:

Chelsie Eiden's Duration - Microsoft Power BI Community

[swinings]

I'd love to use this one, but I'm not a coder and this one is a bit more advanced than my know-how. All my attempts are not working, but I'm certain it's user error.