Transform Date from DD.MM.YYYY to DD/MM/YYYY gives some errors with blank

[yllecna]

Dear all,

I would to share a problem I have when importing data with dates.

Indeed, I have to transform DD.MM.YYYY to DD/MM/YYYY. I do by replacing "." by "/". I could also "detect automatically". But the result (the error) is the same.

When I do so, main rows are ok but some get errors. When I look closer to those with errors, it appears some blanks have been added at the beginning of the date.

For instance "28.06.2015" is transformed into "    28/06/2016" with four blanks at the beginning:

Thank you for your help.

[MFelix]

Hi @yllecna,

For any change on those specific rows don't you have some spaces also?

Before changing the . to / change space to nothing and then do the dot replacement.

Regards,

MFelix

[yllecna]

Hello MFelix,

Thanks for your answer.

Indeed, I thought there was an issue from my source.

I checked it but actually no. When my rows are in DD.MM.YYYY format, there is no blank at the beginning.

It's really when I convert them to DD/MM/YYYY, it appears to have four blank (space) caracters at the beginning for some rows, not all.

I would add also, I get my data from a folder and file in .txt

Thanks

[v-juanli-msft]

Hi @yllecna

I can reproduce your problem.

Why this error occurs is that power bi recognizes date format as mm/dd/yy by default instead of dd/mm/yy, indeed  there is no date format as dd/mm/yy automatically, you need to recreate this.

If it doesn’t mind transforming it out of Edit queries, You could follow steps below to display date column in DD/MM/YYYY format, and sort it correctly.

1.in Query editor, transform “.” To “/”, the close&&apply,

2.in the Data view, select this column and transform it to date type

3.Use FORMAT function to create a new calculate column in your table

Column = FORMAT('New Text Document'[Column2],"DD/MM/YYYY")

4.Select the new calculate column in Data view, then click "Sort by Column" under Modelingtab, and choose your original date column instead of the default column.

Best Regards

Maggie