Solved: Date in a colums

Syndicate_Admin · ‎07-13-2021

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!

Syndicate_Admin · ‎07-13-2021

@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

View solution in original post

Syndicate_Admin · ‎07-13-2021

@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

Syndicate_Admin · ‎07-13-2021

@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

Syndicate_Admin · ‎07-13-2021

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

Syndicate_Admin · ‎07-13-2021

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

Syndicate_Admin · ‎07-13-2021

@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