Forum Discussion
Power Query inserts wrong dates to Excel
Hello,
I am trying to connect an external CSV-file and parse the data into Excel, so that I can use it there. The CSV-file contains one column of timestamps in the format of "yyyy-MM-dd hh:mm:ss".
I am able to load the data no problem, but I want to transform it before it is inserted into Excel, so that it is easier to work with. Though, when I seem to do my transformation in power query, the column of type date changes when I load the data into Excel. (See attached photo)
I believe the issue could be due to different locale and region settings, but I have gone over and check everywhere on my computer, my MacBooks settings, excel workbook settings and power query settings, and they are all set to Denmark (da-DK).
I have also tried making a column where I converted the date to plain text, and it seems to get the dates fine there. For some reason, when it is loading the data into Excel, it parses the date completely wrong, and the date value is off by almost 2000.
Any help on this problem is much appreciated.
7 Replies
About two months ago I experienced the issue where the PQ editor insisted on displaying dates in the US format. It completely ignored the system locale . And that was on both Mac and PC. I reported the bug and a few updates later (on the Insider program) the problem was gone on both platforms. Can't remember which update that was though and find nothing specific on it in the September/October release notes either.
I have this same problem on my PC. Can someone explain how to fix it?
Power Query recognises dates from external source in format set in PQ locale setting for the file, or in ISO format. In your case is the latest, dates shall be recognised correctly.
In theory PQ editor shows dates in you computer locale setting format. But recently it starts to show in US format, not sure that's bug or feature. In any case it doesn't matter in which format editor shows the date.
Returned into the grid dates will be shown as in your regional format settings, or in format which you will apply to the range.
It looks like in your case the main problem is date system. PQ uses 1900 date system and your computer is on 1904 date system. Thus dates in year 2024 are shown as dates in year 2028.
I'm not Mac user and not sure if PQ on Mac recognises date system. But if you shift on 1900 these dates shall be shown correctly. Another story other dates in your files will be wrong.
So, above just a guess, perhaps someone who works on Mac could give more details.
I must admit, I was surprised to see the US/Liberia date format as the result of importing ISO dates in text format.
PeterBartholomew1 , what exactly do you mean under importing ISO dates in text format ?
