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"
tackytechtom's avatar
tackytechtom
Most Valuable Professional
4 years ago

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/