Date format from text

[Anonymous]

I have a column that's coming in as text that supposed to be a date. I want to convert that text to date but when I use Format(Date,"##/##/##") and then go to change it type it says format() is not able to convert text to date.

Example: 81015 = 8/10/15:

[mahoneypat]

You can parse this out with DAX text functions, but this is a great application of Column from Examples in the power query editor.  Please see this link - https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-column-from-example

I used it on similar data and it automatically created a custom column with this M expression

Text.Combine({Text.Reverse(Text.Middle(Text.Reverse(Text.From([Date], "en-US")), 4)), "/", Text.Reverse(Text.Middle(Text.Reverse(Text.From([Date], "en-US")), 2, 2)), "/20", Text.End(Text.From([Date], "en-US")

If you still want to do it with DAX, here is one way to do it

NewDateColumn = Left('Date'[Date],LEN('Date'[Date])-4)&"/"&Left(Right('Date'[Date],4),2)&"/"&RIGHT('Date'[Date],2)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks @mahoneypat  for the insightful information! 

When I tried to use the DAX statement I get this error.

[az38]

@Anonymous 

create a calculated column

Column = DATE(VALUE(RIGHT([DATE], 2)) + 2000, VALUE(LEFT([DATE], LEN([DATE])-4)), VALUE(MID([DATE],LEN([DATE])-3,2)))

[Anonymous]

@az38  Thanks for the information but I get "An argument of function 'LEFT' has the wrong data type or has an invalid value/" error.

[az38]

@Anonymous 

are you sure you copied the statement example as I suggested? it works as appropriated for me, I checked twice

[Anonymous]

@az38

Yes , I've checked that I copied correctly.

[az38]

@Anonymous 

do you have values with 4 digit length?

[Anonymous]

@az38 

The largest I have is 6 digit and the smallest I have is 5 digits. I went through my data set and it looks like I might have some blanks if that would affect it?

[az38 · Accepted solution]

@Anonymous 

yes.

try

Column = 
IF(ISBLANK([DATE]), BLANK(),
DATE(VALUE(RIGHT([DATE], 2)) + 2000, VALUE(LEFT([DATE], LEN([DATE])-4)), VALUE(MID([DATE],LEN([DATE])-3,2)))
)

[az38]

@Anonymous 

I was able to reproduce error if my DATE column has a 3-digits value