Forum Discussion
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).
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
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"
11 Replies
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"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"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"
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 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
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.
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/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 !
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"
