Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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...
Need Help
  • Fowmy's avatar
    Fowmy
    6 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

     






     

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
6 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