Forum Discussion
Convert ddmmmyyy string to datatype date
I have a column with string data like this: "Message Age_28Apr2020.xlsx"
I am using this DAX to extract the date information into a separate real date column:
Date =
var FirstDelimiter = FIND("_";Table[Source.Name];1)
var SecondDelimiter = FIND(".";Table[Source.Name];FirstDelimiter +1)
return
MID(Table[Source.Name];FirstDelimiter +1; SecondDelimiter - FirstDelimiter -1)
This results in 28Apr2020 but I cannot convert this to the data type "date", Power BI and the datevalue function can't recognize this as a string date. I don't care about the format I just need to convert this tho date format so that I can create a relationship with my calendar table (and the other tables).
Anonymous , Recreate this column like. You should be able to change data type to date
new column = left([Datecol],2) &"-"& mid([Datecol],3,3)&"-"& right([Datecol],4)
28-Apr-2020
2 Replies
- amitchandak
Super User
Anonymous , Recreate this column like. You should be able to change data type to date
new column = left([Datecol],2) &"-"& mid([Datecol],3,3)&"-"& right([Datecol],4)
28-Apr-2020
Thank you for the formula. Any idea why I'm unable to change the data type to 'Date'? It gives me an #Error.
