Convert ddmmmyyy string to datatype date

[Anonymous]

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).  

[Ivan]

Thank you for the formula. Any idea why I'm unable to change the data type to 'Date'? It gives me an #Error.

[amitchandak · Accepted solution]

@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