cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bahare76
Regular Visitor

Error when converting text to date with Power BI

Dear members,

I have a file which contains all the transactions and tickets generated for the last month from our ticketing system. The thing is the column for created time is in text format. Issue happens when i wanted to convert the column to Date. As a result, some fields were successfully converted and some fields have "Error". Please refer the image below After using the first row as headerAfter using the first row as headeremove these errors as they will affect my reporting and tracking process. The error message is "DataFormat.Error: We couldn't parse the input provided as a DateTime value". The sample is available here for your reference   Sample Data Set.xls

1 ACCEPTED SOLUTION
selimovd
Community Champion
Community Champion

Hey @bahare76 ,

 

for me it worked without any errors with your data.

Can you tell me the error?

 

Otherwise this happens for me once in a while when I import data in another locale format, like US format and I'm in Europe.

Try to import as with locale. Important, you have to do that when the column is still in format text.

Right click on the column --> Change Type --> Using Locale:

8.png

 

Then chose Date/Time and Locale English (US):

9.png

 

Afterwards he tries to make a datetime field from the US format.

Let me know if that worked.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

4 REPLIES 4
selimovd
Community Champion
Community Champion

Hey @bahare76 ,

 

for me it worked without any errors with your data.

Can you tell me the error?

 

Otherwise this happens for me once in a while when I import data in another locale format, like US format and I'm in Europe.

Try to import as with locale. Important, you have to do that when the column is still in format text.

Right click on the column --> Change Type --> Using Locale:

8.png

 

Then chose Date/Time and Locale English (US):

9.png

 

Afterwards he tries to make a datetime field from the US format.

Let me know if that worked.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

Hi @selimovd ,

Thanks, it worked. What i did was changing the regional setting before converting to Date Format.

selimovd
Community Champion
Community Champion

Hey @bahare76 ,

 

that's great to hear! Yes, you have to change it before the conversion, otherwise the error already exists 😊
Don't forget to mark the post as solution ✔️, like that you help the next person to find the result without reading through the whole conversation.

 

Best regards and have great Easter

Denis

amitchandak
Super User IV
Super User IV

@bahare76 , I do not see any date in the excel link.

This can be because your date is in dd/mm/yyyy format and you have a setting of windows mm/dd/yyyy

 

Other way is to convert date to other format - I dicussed in this video https://www.youtube.com/watch?v=vky4wPqm0O0

 

 

One way is this setting - https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors