Error transforming dd.mm.yyyy text field to date

[fBSDmon]

It feels liek I treid everything under the sun and PowerBI desktop just refuses to convert my MM.DD.YYYY formated date field I get from my bank in semicolon separated CSV to a proper date field.

By all accounts, a simple "transform" of "data type" to "date" should work.

I tried multiple things I found in this forum, but nothing worked. For example, the following should add a colmun, trim out any white spaces and convert to date

= Table.AddColumn(#"Reordered Columns", "Parse", each Date.From(Text.Trim([Buchungstag])), type date)

But ti doesn't 🙂

I always get the following error:

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
    29.12.2017

Any ideas what I am doing wrong here?

[v-lili6-msft · Accepted solution]

hi @fBSDmon 

The date format is based on your current PC, You could change the region setting to keep the same with the data.

You could also Changed Type with Locale in edit queries as below:

For example, I choose Belarus

Result:

And if you change type directly

Regards,

Lin

[Ashish_Mathur]

Hi,

As can be seen in the screenshot, when i import the Table into the Query Editor, the . seperator automatically converts into a - seperator thereby converting the text date into a proper date.  Perhaps this happens because my Windows Date and Time settings are set to the Indian system (which is dd-mm-yyyy).

[parry2k]

@fBSDmon based on your example date, it doen't seems like MM.DD.YY, it is DD.MM.YY and that is going to be the reason for converting it to date

[fBSDmon]

I made a typo in the description, the date is DD.MM.YYYY format as stated in the subject.

In any case, I am surprised that Power BI cannot create a date object form DD.MM.YYYY format 🙂 Astonished in fact.

Anyway, I learned a bit of Power Query and wrote the folowing fix:

= Table.AddColumn(#"Reordered Columns", "Date", each Date.From(Text.End([Buchungstag], 4) & Text.Middle([Buchungstag], 3, 2) & Text.Start([Buchungstag], 2)), type date)