Date in a colums

[Allorn]

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! 

[CNENFRNL]

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

[Fowmy]

@Allorn 

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

[Allorn]

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

[Fowmy · Accepted solution]

@Allorn 

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

[Allorn]

@Fowmy 

Big thanks!

Can i ask you to help me last time? You code is work ! Can you little bit change him that consider last :00

Now it loks like

P.S Sorry for bad english 🙂

[Fowmy]

@Allorn 

Okay, Change number 14 to 17

Text.Length([Dates]) = 17 

________________________

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 

[Allorn]

@Fowmy 

[Fowmy]

@Allorn 

Make it 19 as you changed the year from 20 to 2020

[Allorn]

@Fowmy 

If i make 19 it will be

if i make 20 - Error. 

[Allorn]

@Fowmy 

I change from 2 to 4 and seems like work! 

Thank you !!!

[Fowmy]

Not clear enough 

[CNENFRNL]

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