TRIM and CLEAN function doesn't work

[Anonymous]

Hi community 🙂

I have an issue with my query which should be a quick fix, but turned out to cost me hours.. hopefully someone knows what I should do to resolve the problem!

I work in a large dataset with multiple date columns. most of them are automatically categorised as a 'date' type. for 2 columns however this is not the case. when converting to date I get many errors. 

I looked around for a solution, and the best option was to:

1.  change the column to text

2. apply CLEAN and TRIM (also tried seperately)

3. change column to 'date' type. 

still the outcome is as follows:

the worst thing is, I solved this exact problem before, because I saw an old written note of mine where I discover the blank space makes it impossible to convert to date, but I did not write down the solution! (internal scream)

Does anyone have an idea what possibly can the solution be? how come the blank spaces are not removed after using both TRIM and CLEAN? I love PowerBI but this drives me crazy 😂

I appreciate any tips, kind regards

Robin 

[ImkeF · Accepted solution]

Hi @Anonymous ,
this doesn't look like a problem with the mentioned text functions, but with the locale when converting to text.
Please use a non-american locale and it should work.