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"
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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

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