Forum Discussion
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 var...
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.
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 TeamIf 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"})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