Forum Discussion

Pmasuch's avatar
Pmasuch
Regular Visitor
3 years ago

Formatting date and time

Hey all,
I need help formatting date and time columns. In my source, the date and time are displayed as follows:

 

 

The goal is to format the date and time correctly without performing various transformation steps (e.g. function "Text.PadStart"). For the columns that contain the times, I have managed to get the dates to display correctly with a lot of steps. But I'm sure it can be done even easier 😉

 

 

As for the date, transforming it to text and then to date works, but there are also rows that show "0". Here there is then logically an error and the function "Replace error" does not work, because there then a value in the date format is expected.

 

 

Thanks in advance and many greetings,
Paddy

 

 

 

 

 

Need Help
Tips and Tricks

6 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    What do the Uhrzeit_Rewrite values represent? Do you have an algorithm we can use?

    --

    For the date values, you can have null dates but you can't have zero dates.  So you can Replace Values or Replace Errors, whichever is easier for you.

  • Pmasuch's avatar
    Pmasuch
    Regular Visitor
    3 years ago

    Hi,
    thank you, the date looks fine, now!

     

    The column "Uhrzeit_Rewrite" shows the time, but in a wrong format with 11 or 12 digits. Here only the first 4 or 5 are needed, for the representation of hours, minutes and seconds. And this of course in the time format, e.g. 08:15:22 (German time format).

    Greetings



    Greetings

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    OK, for the times we could 'split column by position' (by 6 chars as far right as possible) then Text.PadStart( to 6 chars using "0") and then change datatype to Time.  I don't know if that's any quicker than the steps you have but it should work.

    • Pmasuch's avatar
      Pmasuch
      Regular Visitor
      3 years ago

      These are exactly the steps I have made.
      Isn't there any quicker solution?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Pmasuch ,

         

        Here is my steps. hope this helps.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcnBDcAgDATBXu7Ng1s7Ca4Fuf82UCTmO3uLSczk0ZBJU7WglnrcC+K/5HU4/SXqPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datum_Rewrite = _t, Uhrzeit_Rewrite = _t]),
    Custom1 = Table.TransformColumns(Source,{"Uhrzeit_Rewrite",each Text.Start(_,6)}),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Datum_Rewrite", type date}, {"Uhrzeit_Rewrite", type time}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Datum_Rewrite", type text}, {"Uhrzeit_Rewrite", type text}}, "en-US"),{"Datum_Rewrite", "Uhrzeit_Rewrite"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}})
in
    #"Changed Type1"

         

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum