Date Formatting MMDDYY from text

[cgarcia]

I'm running into a wall when trying to format text to dates.

My column information looks like this.

080613

011416

000000

111214

I used this formula Column = LEFT(FILE[COLUMN 1],2)&"/"&MID(FILE[COLUMN 1],3,2)&"/"&RIGHT(FILE[COLUMN 1],2)+2000, to get me here.

08/06/2013

01/14/2016

00/00/2000

11/12/2014

I get the following error when I try to change my data type to Date... "Cannot convert value '00/00/2000' of type Text to type Date".

What can I do to make 00/00/2000 recognizable or totally eliminate it?

[v-qiuyu-msft · Accepted solution]

Hi @cgarcia,

As the value "00/00/2000" is not a valid date, when you change the data type to date, the error throws out. You can modify the DAX to get around of this kind of invalid date value:

Column = IF( LEFT(FILE[COLUMN 1],2)="00" && MID(FILE[COLUMN 1],3,2)="00",BLANK(),LEFT(FILE[COLUMN 1],2)&"/"&MID(FILE[COLUMN 1],3,2)&"/"&RIGHT(FILE[COLUMN 1],2)+2000)

Best Regards,
Qiuyun Yu

[cgarcia]

Hi Qiuyun,

This expression worked perfectly! What if i wanted the value to be today's date instead of a blank?

Regards,

Chris

[GilbertQ]

Hi @cgarcia

What I would suggest is to rather create your date column in the query editor.

Not only is it quicker, it is also a lot easier, especially if you have downloaded and installed the latest version of the Power BI Desktop, which has additional enhancements for the Columns from Examples. As well as you can also put in steps if it finds a 00 what to do with it.

And finally when creating it in the Query Editor it also will ensure that your Power BI model is that much more efficient.

[hohlick]

Hi @cgarcia

is 000000 means something? may be you need to replace it with somesing meaningfull or filter it out in query editor (not in DAX)