HHMMSS without leading Zeros to HH:MM:SS

[joshua1990]

Hi all!

I have a column that shows me the time of the day but without leading zeros:

For instance:

• Instead of 00:00:02 it is 2
• Instead of 02:00:30 it is 20030

How can I change this within power query?

Thanks in advance

[BA_Pete · Accepted solution]

Hi @joshua1990 ,

Add a new custom column like this:

let _time = Text.PadStart(Text.From([time]), 6, "0") in
Text.Combine(
    {
        Text.Start(_time, 2),
        Text.Middle(_time, 2, 2),
        Text.End(_time, 2)
    }, ":"
)

Working example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1QGSBgbGBmCWsamZAYRlaWRuaghmGRoamRqbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [time = _t]),
    chgToInteger = Table.TransformColumnTypes(Source,{{"time", Int64.Type}}),
    addTimeFormatted = Table.AddColumn(chgToInteger, "timeFormatted", each
    let
        _time = Text.PadStart(Text.From([time]), 6, "0")
    in
        Text.Combine(
            {
                Text.Start(_time, 2),
                Text.Middle(_time, 2, 2),
                Text.End(_time, 2)
            }, ":"
        )
    )
in
    addTimeFormatted

Example output:

Pete