I have some data which is pulled as a .CSV from the website where it's generated (it's parking data for local authority). There's multiple systems with data laid out slightly differently so I'm using power query to import and transform the data before analysing.

​

The system in question is American (I'm UK based) so, as expected, the dates export in the format MM/DD/YYYY which in itself is a non-issue. However, trying to manipulate this into something similar to a UK date has proved impossible. Changing the format to date within Power Query editor just gives error values.

​

Things I've tried:

Using the change type to locale through Power Query

Using text to columns within the Excel table to change to DMY

Using a combination of LEFT,RIGHT,MID to alter the order of the numbers between the slashes.

​

Here's where it got a bit peculiar - the date displays as 01/12/2019 for example.

​

Using a LEFT("cell",2) on this cell gives you 00

a MID for the middle you get 43

a RIGHT for the end and I get 3800

​

This isn't something I've seen before but it seems a bit weird - the numbers also seem to have some sort of relationship. 2019, 2 x 19 = 38 etc

​

Using the formula on a typed date cell works fine, but this is what i get on this table...

​

https://imgur.com/a/uxlTAdk

​

Any help much appreciated - monitor is about to go out the window.

​