- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Cannot format column to Date format
Hello
I am using an exported CSV as the source.
The Data/Time column is formated this way
5/24/23, 12:59:22 PM |
If I try to format it as Date/Time in the PowerBI Query I get this error
DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
5/24/23, 12:59:22 PM
I am using the Regional Settings for English (UK) as I have other sources using this locale.
Any ideas on how can I solve this?
Thank you in advance
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You're mixing up a few things or I'm misunderstanding you. You don't need to change the locale of your Windows environment or of the "file". You only need to change the column data type and select "use locale" and only that column will be affected:
Then select "date/time" and the right locale.
If you mean that you've appended a bunch of files together and only some of them had the "wrong" locale, then yes that would be a problem. You have to split out the ones that have a different locale and append them after fixing the data type. PowerQuery processes data type operations on the entire column, you can't set a different locale for each cell.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Right-click (change Type->Using Locale) and select US , datetime.
The format looks like it will be accepted via this feature.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I change the locale will that not affect all of my other PBIX queries?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Changing data type and using locale only affects that column.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you are right. I missed that the locale only affected the current file.
Setting it to US resolves it for this data source but breaks it for other sources, sadly 😞
The snapshots below are from another data source after I change the locale from UK to US. They show how the field's values show before and after I change their type to DATE.
It is strange, though, since it seems that it formats it correctly for some date/time entries but not for all.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You're mixing up a few things or I'm misunderstanding you. You don't need to change the locale of your Windows environment or of the "file". You only need to change the column data type and select "use locale" and only that column will be affected:
Then select "date/time" and the right locale.
If you mean that you've appended a bunch of files together and only some of them had the "wrong" locale, then yes that would be a problem. You have to split out the ones that have a different locale and append them after fixing the data type. PowerQuery processes data type operations on the entire column, you can't set a different locale for each cell.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I was certainly mixing things up!😊
Learned a couple of new things today. Thank you very much!
Everything formated correctly now after I use the "Using Locale" menu option.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Alternatively, perhaps the comma is the culprit?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
good spot.
But this error still comes about after removing the comma
DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
5/24/23 12:59:22 PM
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-16-2024 02:25 PM | |||
12-08-2023 02:47 AM | |||
02-23-2024 06:19 AM | |||
05-27-2024 05:37 AM | |||
08-09-2024 08:28 AM |
User | Count |
---|---|
126 | |
80 | |
59 | |
57 | |
43 |
User | Count |
---|---|
183 | |
111 | |
82 | |
65 | |
50 |