How do I convert text column to a custom "yyyy-mm-dd" format

[eKat]

Hi there,

I have Power BI linked to a folder with Excel files, in which date appears in a custom text format as "28-Feb-20". When I load the files to Power BI, it automatically recognizes the date columns as Short Date turning February 28, 2020 into 20 February 2028. To avoid this, I have disabled auto-recognition of the format. However, I still need to convert the custom text into a date and when I try using FORMAT function it gives me an error saying FORMAT isn't recognized or spelled correctly.

I also tried adding VALUE function to extract value from [ORDER DATE] and it didn't work. 

Any ideas on how to go about it?

Thanking you in advance!

Katherine

[Greg_Deckler]

FORMAT is a DAX function, you are writing M code.

[eKat]

Thank you, Greg.

Do you know by any chance which M script to write to convert custom text format of "dd-mmm-yyyy" to  "yyyy-mm-dd'? 🙂 

[v-frfei-msft · Accepted solution]

Hi @eKat ,

Changing local shoud help you in your scenario. Please refer to the steps as the screen shots below.