cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ashish_kumar12
Helper I
Helper I

How to Convert Date given in Spanish to English

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:

Capture_2.JPGCapture_3.JPG

 

 

 

Thanks 

1 ACCEPTED SOLUTION
sreenathv
Solution Sage
Solution Sage

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.

 

sreenathv_0-1618306331954.png

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)

View solution in original post

3 REPLIES 3
nmasimore
Frequent Visitor

Another solution that perhaps may work:

 

Right click column -> Change type -> Using locale

 

Search for Spanish

 

nmasimore_0-1642019736837.png

 

 

sreenathv
Solution Sage
Solution Sage

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.

 

sreenathv_0-1618306331954.png

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)

Thank you @sreenathv  .

 

Really appreicate your help.

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

Ted's Dev Camp - October 6, 2022

Watch the playback of Session 26 with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors