Forum Discussion
Cannot format column to Date format
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.
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.
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.
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.