Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date in a colums

Hi! 
Can someone help me? I have in a cloumn values like this 

23.07.20 10:30
23.07.20 12:19
23.07.20 12:28
Wed Jul 29 22:33:51 MSK 2020
Wed Jul 29 22:41:44 MSK 2020
Thu Jul 30 01:21:46 MSK 2020
Thu Jul 30 02:06:18 MSK 2020
Thu Jul 30 06:14:37 MSK 2020
Thu Jul 30 06:53:15 MSK 2020

 

What steps should i do to format this column to date like mm\dd\yyyy ?

Thanks! 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Your data looks like this before extracting the date

     

    Right-click on the Column Header > Choose Transform and select TRIM
    Then Click ADD COLUMN ribbon > CUSTOM COLUMN and paste the below code.

     

    =if Text.Length([Dates]) = 14 then  
#date(Number.From(Text.Middle([Dates],6,2)),Number.From(Text.Middle([Dates],3,2)),Number.From(Text.Middle([Dates],0,2))) 
else 
Date.FromText(Text.Middle([Dates],8,2) &"." & Text.Middle([Dates],4,3)&"."&Text.End([Dates],2))

    Change the Type to Date


    You can apply any formatting in Power BI Model.

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     






     

12 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    It seems your data is not in a uniform pattern, the 1st three rows are different from the rest. Can you confirm if this the way your data is saved? 

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Yes !Half of the data in this column look likes the first three rows , the second half is like the rest.

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago

        Anonymous Ok, then, just tweak the code with appointing the "Source" to your file; and the rest will be coped with by the code.

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Anonymous As long as your data consist of such two patterns, you might try such processing

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcq9CoNQEEThV1msg8zO3uvPPEKClUIKsVNIkdb3j1gpiWnP+caxYJSoS8IcChTT7Zgob78Smz09l9nu69vYGqkIZbeufxhB/ADJldIZDK91BwGDixuorgGFSt5cg+0mRf0P5JDnA5g+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t]),

    #"Extracted Characters" = Table.TransformColumns(Source,
        {{Table.ColumnNames(Source){0},
            each
                if Text.Length(_) < 15 then Text.Start(_, 😎 else 
                            [
                                l = Text.PositionOf(_, " ", Occurrence.All),
                                str = Text.RemoveRange(_, l{2}, l{4} - l{2})
                            ][str]
        }}
    ),
    Custom1 = Table.TransformColumns(#"Extracted Characters", {{"Date Time", each Date.ToText(Date.From(_, "fr"), "MM/dd/yyyy")}})
in
    Custom1