Skip to main content
cancel
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

Reply
Pmasuch
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:

 

Pmasuch_0-1685085949115.png

 

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 😉

 

Pmasuch_1-1685086078715.png

 

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.

 

Pmasuch_2-1685086162053.png

 

Thanks in advance and many greetings,
Paddy

 

 

 

 

 

6 REPLIES 6
HotChilli
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.

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"

vcgaomsft_0-1685514938888.png

vcgaomsft_2-1685514983485.png

 

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

 

Pmasuch_0-1685533617647.png

 

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

 

Pmasuch_1-1685533716665.png

 

Do you have an explanation for this?

 

Thank you and best regards

Pmasuch
Regular Visitor

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

Pmasuch_0-1685095405213.png

 

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

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors