cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Date in a colums

@Allorn 

Your data looks like this before extracting the date

 

Fowmy_0-1600546115153.png

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

Fowmy_1-1600546309129.png


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

12 REPLIES 12
Highlighted
Community Champion
Community Champion

Re: Date in a colums

@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

Highlighted
Solution Sage
Solution Sage

Re: Date in a colums

@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

 

Highlighted
New Member

Re: Date in a colums

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

Highlighted
Solution Sage
Solution Sage

Re: Date in a colums

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

Highlighted
Community Champion
Community Champion

Re: Date in a colums

@Allorn 

Your data looks like this before extracting the date

 

Fowmy_0-1600546115153.png

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

Fowmy_1-1600546309129.png


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

Highlighted
New Member

Re: Date in a colums

@Fowmy 

Big thanks!

Allorn_0-1600547475002.png

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

Allorn_1-1600547837765.png

 

P.S Sorry for bad english 🙂

Highlighted
Community Champion
Community Champion

Re: Date in a colums

@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 

Highlighted
New Member

Re: Date in a colums

@Fowmy 

Allorn_1-1600548576332.png

 

Highlighted
Community Champion
Community Champion

Re: Date in a colums

Not clear enough 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors