Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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).
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |