Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@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

 






 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

12 REPLIES 12
CNENFRNL
Community Champion
Community Champion

@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

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

@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

 






 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

@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 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy 

Allorn_1-1600548576332.png

 

@Allorn 

 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy 

If i make 19 it will be

 

Allorn_0-1600549508949.png

 

if i make 20 - Error. 

Allorn_1-1600549549711.png

 

@Fowmy 

I change from 2 to 4 and seems like work! 

Allorn_1-1600550147064.png

 

 

 

Allorn_0-1600550133891.png

 

Thank you !!!

 

Not clear enough 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

CNENFRNL
Community Champion
Community Champion

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors