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