Forum Discussion

cgarcia's avatar
cgarcia
Frequent Visitor
9 years ago
Solved

Date Formatting MMDDYY from text

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(FI...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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

GilbertQ's avatar
GilbertQ
Super User
9 years ago

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.