topic Re: Converting other language text data to date type in DAX Commands and Tips

Converting other language text data to date type

Ocean_PowerBI — Fri, 28 Aug 2020 22:35:23 GMT

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 than date info with the following command:

Date_Index = IF(Pro_Report[entryConsoleType] = "DATE_YEAR", Pro_Report[values], BLANK())

This simply pulls the text data from the values column over to the Date_Index column (which is formatted as Date type) whenever the entryConsoleType is "DATE_YEAR". Which worked great until I ran into a unique issue. The data is gathered through a production app running on IOS that allows you to select a different language for the user. When the user selects Spanish, for some reason that changes how the dates are recorded in the text column and I get the following error:

Cannot convert value 'ago.-24-2020' of type Text to type Date.

It fails to recognise that "ago." is August or Agosto in spanish. This simple issue breaks everything. Is there anything that I can do to have it recognise these as actual dates? Is there any way I can recondition the text info?

Re: Converting other language text data to date type

lbendlin — Fri, 28 Aug 2020 23:27:43 GMT

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

Re: Converting other language text data to date type

Ocean_PowerBI — Tue, 01 Sep 2020 20:25:31 GMT

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?

Re: Converting other language text data to date type

lbendlin — Tue, 01 Sep 2020 20:35:01 GMT

Transform... Replace Values

Re: Converting other language text data to date type

Ocean_PowerBI — Wed, 02 Sep 2020 19:55:11 GMT

I don't believe I can blanket remove periods from the values column because it contains info that does also need a period. I'm only taking info from this column based on a value from another column. Am I able to bake in a substitute command into this query:

Date_Index = IF(Pro_Report[entryConsoleType] = "DATE_YEAR", Pro_Report[values], BLANK())

So that after it does the logic it then strips the period from the value it is supposed to bring over? How would I wrap that in?

Re: Converting other language text data to date type

Ocean_PowerBI — Wed, 02 Sep 2020 20:02:59 GMT

Ok, so I was able to get rid of the period like this:

Date_Index = IF(Pro_Report[entryConsoleType] = "DATE_YEAR", SUBSTITUTE(Pro_Report[values],".",""), BLANK())

Now If only there was a way to either convert locale to en-US or write another statement that replaces spanish abbreviations with english. Is there a better way than just looping in a bunch of IF satatements?

Re: Converting other language text data to date type

lbendlin — Wed, 02 Sep 2020 20:05:12 GMT

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.

Re: Converting other language text data to date type

Ocean_PowerBI — Wed, 02 Sep 2020 21:08:24 GMT

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.