I'm experimenting a bit with US dates and getting them easily into dd/mm/yyyy format. I get the data source into PQ, the US dates come in as usual "Any" format - therefore my Date:Locale option is not available to me. I convert them to dates - but then get a load of error messages for some of them. So then I have the Date: Locale option available to me - but my dates still have errors...what's frustrating is that I tried it last week with sample US dates and it worked fine 🙂 Thanks again. See attached file.
Hi Anne,
Is your source data in Excel, or does it start in a CSV/Text file?
Excel is messing up your dates before you have a chance to fix them with PQ. If the data was in a CSV or Text file then you would be able to use the From Locale as intended.
Mynda
It's really weird. It's a US CSV file which I pulled straight into PQ. in the data set itself (in Excel - where the local format is dd/mm/yyyy) the "odd" dates e.g. 3/31/2018 are showing up as text. in PQ they show up as the ever-ready "Any" and when I change them to Date, those "odd" dates show up as errors. It's not a big deal tbh but I'm really curious as to what is going on... thanks for your help as always 🙂
That's normal for Excel when you open CSV or Text files that have a different locale to the language settings of Excel.
When you change the data type in Power Query you need to right-click the column > Change type > using Locale... and here you can tell Excel that the locale of the CSV file is US. It should then change the dates correctly.
