Round up date time column to remove miliseconds

pls try this code

= Time.From(Time.ToText(DateTime.Time([Date]), "hh:mm:ss"))

Hi Anonymous ,
v1

but still stored as 2024-01-01T01:33:06.7200000

v2

stored as you want 01-Jan-24 1:33:07 AM

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zUyUTC0Mja2MjDTMzcyAAEFR1+l2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    AddedCustom = Table.AddColumn(Source, "Custom", each 
        [ a = Text.BetweenDelimiters([Column1], ":", " ", {0, RelativePosition.FromEnd}),
          b = Text.PadStart(Text.From(Number.Round(Number.From(a, "en-US"))), 2, "0"),
          c = Text.BeforeDelimiter([Column1], ":", {0, RelativePosition.FromEnd}),
          d = Text.AfterDelimiter([Column1], " ", {0, RelativePosition.FromEnd}),
          e = c & ":" & b & " " & d
        ][e] )
in
    AddedCustom

Hi,

Thanks for the solution Ahmedx and dufoq3 offered, and i want to offer some more information for user to refer to.

hello Anonymous , you can also refer to the following code in advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zUyUTC0Mja2MjDTMzcyAAEFR1+l2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let a=Text.Replace(Text.AfterDelimiter([Column1],":",1),"AM",""),
b=Text.BeforeDelimiter([Column1],":",1),
c=Number.Round(Decimal.From(a))
in DateTime.FromText(b&":"&Text.From(c)))
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zUyUTC0Mja2MjDTMzcyAAEFR1+lWB0UBYZWppZARIKKAJwqjJBUxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    已添加自定义 = Table.AddColumn(
        Source, 
        "Custom_ok", 
        each let 
            a = DateTime.From([Column1]), 
            b = Number.Round(Duration.TotalSeconds(a - #datetime(2000, 1, 1, 0, 0, 0)), 0, 2)
        in 
            #datetime(2000, 1, 1, 0, 0, 0) + #duration(0, 0, 0, b)
    )
in
    已添加自定义

w/o text conversion

let
    Source = your_table,
    rounding = Table.AddColumn(
        Source,
        "rounding", 
        (x) => 
            [dtr = DateTime.ToRecord(x[datetime_column]), 
            roundup_seconds = #datetime(
                dtr[Year], 
                dtr[Month],
                dtr[Day], 
                dtr[Hour], 
                dtr[Minute], 
                0
            ) + #duration(0, 0, 0, Number.RoundUp(dtr[Second], 0))][roundup_seconds],
        type datetime
    )
in
    rounding