Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Timestamp format

 

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

Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

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

    Best Regards,
    Community Support Team _ Eason

6 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago
      4/25/2022 2:05:20 PM +00:00

       

      Data type is: date/time/timezone

      • vojtechsima's avatar
        vojtechsima
        Super User
        4 years ago

        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"

         

  • danextian's avatar
    danextian
    Super User
    4 years ago

    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.