Formatting date and time

[Pmasuch]

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

[HotChilli]

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]

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

[v-cgao-msft]

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

[Pmasuch]

Hi Gao,

thanks for your solution, but I need not a merged column. Date and time should stay seperated from each other.

It works with the following steps:

    #"Type: Timestamp columns as ""text""" = Table.TransformColumnTypes(Quelle, {{"Uhrzeit_Update_an_Host", type text}, {"Uhrzeit_Rewrite", type text}, {"Uhrzeit_Write", type text}, {"Modif._Uhrzeit", type text}, {"Datum_letzte_Inv.", type text}, {"Modif._Datum", type text}, {"Datum_Update_an_Host", type text}, {"Datum_Write", type text}, {"Datum_Rewrite", type text}, {"Datum_letzter_Zugang", type text}}),

    #"Add: Uhrzeit_Update_an_Host_" = Table.TransformColumnTypes(Table.AddColumn(#"Type: Timestamp columns as ""text""", "Uhrzeit_Update_an_Host_", each Text.PadStart([Uhrzeit_Update_an_Host], 6, "0")), {{"Uhrzeit_Update_an_Host_", type text}}),

    #"Add: Modif._Uhrzeit_" = Table.TransformColumnTypes(Table.AddColumn(#"Add: Uhrzeit_Update_an_Host_", "Modif._Uhrzeit_", each Text.PadStart([Modif._Uhrzeit], 6, "0")), {{"Modif._Uhrzeit_", type text}}),

    #"Add: Uhrzeit_Write_" = Table.TransformColumnTypes(Table.AddColumn(#"Add: Modif._Uhrzeit_", "Uhrzeit_Write_", each Text.PadStart([Uhrzeit_Write], 12, "0")), {{"Uhrzeit_Write_", type text}}),

    #"Add: Uhrzeit_Rewrite_" = Table.TransformColumnTypes(Table.AddColumn(#"Add: Uhrzeit_Write_", "Uhrzeit_Rewrite_", each Text.PadStart([Uhrzeit_Rewrite], 12, "0")), {{"Uhrzeit_Rewrite_", type text}}),

    #"Delete: Not used time columns" = Table.RemoveColumns(#"Add: Uhrzeit_Rewrite_", {"Modif._Uhrzeit", "Uhrzeit_Update_an_Host", "Uhrzeit_Write", "Uhrzeit_Rewrite"}),

    #"Extract first 6 signs in time columns" = Table.TransformColumns(#"Delete: Not used time columns", {{"Uhrzeit_Write_", each Text.Start(Text.From(_), 6), type text}, {"Uhrzeit_Rewrite_", each Text.Start(Text.From(_), 6), type text}}),

    #"Add: Datum_letzte_Inv." = Table.TransformColumnTypes(Table.AddColumn(#"Extract first 6 signs in time columns", "Datum_letzte_Inv._", each if [#"Datum_letzte_Inv."] = "0" then null else Text.End([#"Datum_letzte_Inv."], 2)&"."&Text.Middle([#"Datum_letzte_Inv."],4,2)&"."&Text.Start([#"Datum_letzte_Inv."],4)), {{"Datum_letzte_Inv._", type text}}),

    #"Add: Datum_letzter_Zugang" = Table.TransformColumnTypes(Table.AddColumn(#"Add: Datum_letzte_Inv.", "Datum_letzter_Zugang_", each if [#"Datum_letzter_Zugang"] = "0" then null else Text.End([#"Datum_letzter_Zugang"], 2)&"."&Text.Middle([#"Datum_letzter_Zugang"],4,2)&"."&Text.Start([#"Datum_letzter_Zugang"],4)), {{"Datum_letzter_Zugang_", type text}}),

    #"Add: Modif._Datum" = Table.TransformColumnTypes(Table.AddColumn(#"Add: Datum_letzter_Zugang", "Modif._Datum_", each if [#"Modif._Datum"] = "0" then null else Text.End([#"Modif._Datum"], 2)&"."&Text.Middle([#"Modif._Datum"],4,2)&"."&Text.Start([#"Modif._Datum"],4)), {{"Modif._Datum_", type text}}),

    #"Add: Datum_Update_an_Host" = Table.TransformColumnTypes(Table.AddColumn(#"Add: Modif._Datum", "Datum_Update_an_Host_", each if [#"Datum_Update_an_Host"] = "0" then null else Text.End([#"Datum_Update_an_Host"], 2)&"."&Text.Middle([#"Datum_Update_an_Host"],4,2)&"."&Text.Start([#"Datum_Update_an_Host"],4)), {{"Datum_Update_an_Host_", type text}}),

    #"Add: Datum_Write" = Table.TransformColumnTypes(Table.AddColumn(#"Add: Datum_Update_an_Host", "Datum_Write_", each if [#"Datum_Write"] = "0" then null else Text.End([#"Datum_Write"], 2)&"."&Text.Middle([#"Datum_Write"],4,2)&"."&Text.Start([#"Datum_Write"],4)), {{"Datum_Write_", type text}}),

    #"Add: Datum_Rewrite" = Table.TransformColumnTypes(Table.AddColumn(#"Add: Datum_Write", "Datum_Rewrite_", each if [#"Datum_Rewrite"] = "0" then null else Text.End([#"Datum_Rewrite"], 2)&"."&Text.Middle([#"Datum_Rewrite"],4,2)&"."&Text.Start([#"Datum_Rewrite"],4)), {{"Datum_Rewrite_", type text}}),

    #"Delete: Not used date columns" = Table.RemoveColumns(#"Add: Datum_Rewrite", {"Datum_letzte_Inv.", "Datum_letzter_Zugang", "Datum_Update_an_Host", "Modif._Datum", "Datum_Write", "Datum_Rewrite"})

in

    #"Delete: Not used date columns"

But now there is a new problem: Some columns run into errors when updating the dataflow. Unfortunately, the transformations do not work:

"DataFormat.Error: We couldn't parse the input provided as a Date value. <ccon>07.30.2.02</ccon>."

In the browser, the affected columns look fine, transformed as dates (Image 1).

Unfortunately, when I display the raw dataflow without transformations in Power BI Desktop, this is not the case (image 2).

Do you have an explanation for this?

Thank you and best regards

[Pmasuch]

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]

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.