Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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,






Super User
Super User

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.

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

Hi @Pmasuch ,


Here is my steps. hope this helps.

    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}})
    #"Changed Type1"




Best Regards,

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

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"})
    #"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></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

Regular Visitor

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).



Super User
Super User

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.

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors