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?

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

    Transform... Replace Values

     

     

    • Ocean_PowerBI's avatar
      Ocean_PowerBI
      Frequent Visitor
      6 years ago

      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?

      • Ocean_PowerBI's avatar
        Ocean_PowerBI
        Frequent Visitor
        6 years ago

        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?