Forum Discussion
Converting other language text data to date type
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.
Super UserTransform... Replace Values
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?
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?
- lbendlin
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.
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.