Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jagostinhoCT
Post Partisan
Post Partisan

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

1 ACCEPTED SOLUTION

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:

uselocale.png

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

8 REPLIES 8
HotChilli
Super User
Super User

Right-click (change Type->Using Locale) and select US , datetime.

The format looks like it will be accepted via this feature.

If I change the locale will that not affect all of my other PBIX queries?

Changing data type and using locale only affects that column. 

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.

 

jagostinhoCT_1-1685086259225.pngjagostinhoCT_2-1685086269554.png

 

 

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:

uselocale.png

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

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.

jagostinhoCT_0-1685087574466.png

 



Alternatively, perhaps the comma is the culprit?

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

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors