Timestamp format

[Anonymous]

Hi,

Using Add column I have extracted the time in a seperate column from timestamp, but format is like 0. + 24hr formart time, How to remove 0. from the begining and insert a proper time formate like 14.52.00

[danextian]

hI @Anonymous ,

As tested, 14.52.00 is a text format in Power Query. If you really want the time values to appear this way, you can convert the duration column into text (Text.From), extract the hour, minute and second elements (Text.BetweenDelimiters, etc) and then re-combine them with a period in between each element.

[vojtechsima]

Hi, @Anonymous ,
That's strange, could you please share those lines so I can copy it to my power bi and try to recreate your exact issue with your data please?

[Anonymous]

4/25/2022 2:05:20 PM +00:00

Data type is: date/time/timezone

[vojtechsima]

Hi, @Anonymous 
I did test it on my own and I can't replicate your issue:

Make sure, you put "en" as culture argument when you're choosing the date/time/timezone data type. And then you create new column like this:

Here's my Query, you can copy it to your Blank Query in Power Query and see for yourself:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3MtU3MjAyUjCyMjC1MjJQCPBV0DYwsDIwUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetimezone}},"en"),
    #"Inserted Time" = Table.AddColumn(#"Changed Type", "Time", each DateTime.Time([Date]), type time)
in
    #"Inserted Time"

[Anonymous]

Thanks, the table is from ADX (kusto) and the method is directQuery so I used add column and extracted time from it, but the output was odd. I will try with your Query and check

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Please try to change type and select the locale of origin.

Best Regards,
Community Support Team _ Eason