How to Convert Date given in Spanish to English

[Ashish_kumar12]

Hi There,

Hope you doing well .

I am stuck a data which have date column given in spanish and its coming in "Text" Format . When i am trying to transform this into power query its giving me an error . 
Can anyone help me out in this how can i convert this given Spanish Date value in English(Date Format)

Please see below screenshot for the same:

Thanks 

[nmasimore]

Another solution that perhaps may work:

Right click column -> Change type -> Using locale

Search for Spanish

[Anonymous · Accepted solution]

In normal translation, you could use the Microsoft Translator API that can be used in Power BI.  Google it. Here you don't have to actually rely on the translation and you could do the translation in DAX itself, based on your sample data, I am giving you the following DAX code. You have to add more months to the SWITCH statement.

Here is the dax code.

DateEN = 
VAR DayOfTheDate = VALUE(LEFT(SpanishCalendar[Date],SEARCH(" ",SpanishCalendar[Date],1,BLANK())-1))
VAR YearOfTheDate = VALUE(RIGHT(SpanishCalendar[Date],4))
VAR MidPortion = MID(SpanishCalendar[Date],SEARCH(" ",SpanishCalendar[Date],1,BLANK())+1,LEN(SpanishCalendar[Date])-5-SEARCH(" ",SpanishCalendar[Date],1,BLANK()))
VAR MonthOfTheDate =
    SWITCH(
        TRUE(),
        MidPortion="de janeiro de",1,
        MidPortion="de fevereiro de",2,
        MidPortion="de marco de",3,
        MidPortion="de abril de",4,
        MidPortion="de novembro de",11,
        MidPortion="de dezembro de",12,
        0
    )
RETURN
DATE(YearOfTheDate,MonthOfTheDate,DayOfTheDate)

[Ashish_kumar12]

Thank you @Anonymous  .

Really appreicate your help.