Forum Discussion

Ocean_PowerBI's avatar
Ocean_PowerBI
Frequent Visitor
6 years ago
Solved

Converting other language text data to date type

I've got a unique problem that I have not seen anyone else run into. I'm pulling data from a 3rd party BI database and I am making a new date column from a data in a text column that contains more th...
  • Ocean_PowerBI's avatar
    Ocean_PowerBI
    6 years ago

    Solved this for Spanish with my poor-man's coding skills.

     

     

    Date_Index = 
	IF(Pro_Report[entryConsoleType] = "DATE_YEAR", 
			IF(LEFT(Pro_Report[values],3) = "ene", COMBINEVALUES("-","jan", RIGHT(Pro_Report[values],7)),
            IF(LEFT(Pro_Report[values],3) = "abr", COMBINEVALUES("-","apr", RIGHT(Pro_Report[values],7)),
            IF(LEFT(Pro_Report[values],3) = "ago", COMBINEVALUES("-","aug", RIGHT(Pro_Report[values],7)),
            SUBSTITUTE(Pro_Report[values],".","")))), BLANK())

     

     

    Looking into it there are only 3 months that cause issues. Jan, Apr, and Aug. The rest overlap with 3 letters. So I just find and replace those 3 months, combine with the last 7 digits (dd-yyyy), then strip out an periods from the remaining months. And that fixes it.....for Spanish. Will not work with Serbian/Ukraine (so far the only other non-english language here).

     

    The real solution is to get the 3rd party BI to not record dates as <first 3 letter of month>-<dd>-<yyyy> and without ios locale stuff. I've got feature requests with that company on that issue. But the code here works.

     

    EDIT: I also wanted to add that I am specifically not using Power Query for a few performance reasons. So instead of using Transform Data to replace values, you can use the SUBSTITUTE command in DAX to accomplish the same thing.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

Remember what I said about the bag of hurt?  Your only way to really solve this is via getting the locale information as part of your source data.

Ocean_PowerBI's avatar
Ocean_PowerBI
Frequent Visitor
6 years ago

Solved this for Spanish with my poor-man's coding skills.

 

 

Date_Index = 
	IF(Pro_Report[entryConsoleType] = "DATE_YEAR", 
			IF(LEFT(Pro_Report[values],3) = "ene", COMBINEVALUES("-","jan", RIGHT(Pro_Report[values],7)),
            IF(LEFT(Pro_Report[values],3) = "abr", COMBINEVALUES("-","apr", RIGHT(Pro_Report[values],7)),
            IF(LEFT(Pro_Report[values],3) = "ago", COMBINEVALUES("-","aug", RIGHT(Pro_Report[values],7)),
            SUBSTITUTE(Pro_Report[values],".","")))), BLANK())

 

 

Looking into it there are only 3 months that cause issues. Jan, Apr, and Aug. The rest overlap with 3 letters. So I just find and replace those 3 months, combine with the last 7 digits (dd-yyyy), then strip out an periods from the remaining months. And that fixes it.....for Spanish. Will not work with Serbian/Ukraine (so far the only other non-english language here).

 

The real solution is to get the 3rd party BI to not record dates as <first 3 letter of month>-<dd>-<yyyy> and without ios locale stuff. I've got feature requests with that company on that issue. But the code here works.

 

EDIT: I also wanted to add that I am specifically not using Power Query for a few performance reasons. So instead of using Transform Data to replace values, you can use the SUBSTITUTE command in DAX to accomplish the same thing.