cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Text to Time

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).

swinings_0-1646254126097.png

 

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

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:

AlexisOlson_0-1646340915732.png

 

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"

View solution in original post

11 REPLIES 11
Syndicate_Admin
Administrator
Administrator

@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

 

Syndicate_Admin
Administrator
Administrator

Hi @swinings ,

 

How about this:

tomfox_0-1646259043503.png

 

This is the code in advanced editor:

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/

Syndicate_Admin
Administrator
Administrator

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

Syndicate_Admin
Administrator
Administrator

Hi,

to achieve this:

serpiva64_0-1646261095533.png

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 !

 

Syndicate_Admin
Administrator
Administrator

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:

AlexisOlson_0-1646340915732.png

 

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"

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"

Syndicate_Admin
Administrator
Administrator

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"

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.

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.

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Top Kudoed Authors
Users online (2,203)