Forum Discussion

swinings's avatar
swinings
Helper I
4 years ago
Solved

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 ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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"
swinings's avatar
swinings
Helper I
4 years ago

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"