cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fBSDmon
New Member

Error transforming dd.mm.yyyy text field to date

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?

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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:

6.JPG

For example, I choose Belarus

7.JPG

Result:

8.JPG

And if you change type directly

9.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

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:

6.JPG

For example, I choose Belarus

7.JPG

Result:

8.JPG

And if you change type directly

9.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ashish_Mathur
Super User III
Super User III

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).

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User III
Super User III

@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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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)

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors