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

Oh, you're in for a whole bag of hurt.  Basically what you will need to do is create a lookup table with all possible date formats created by the iOS app, and then try them in some random order.  Good luck distinguishing "5/10/2020"  from a user in Brasil and a user in the US.

 

 BTW this has nothing at all to do with language. It's the locale setting that you need to be worried about. Can you get the locale out of the iOS data?

 

https://en.wikipedia.org/wiki/Date_format_by_country

Ocean_PowerBI's avatar
Ocean_PowerBI
Frequent Visitor
6 years ago

How about some baby steps. Is there a way I can edit the query to remove and periods ( . ) from the text string while transfering the value to the new column?