Log In
Register
Home
New Member
August 21, 2023
Offline
Hi guys,
So I am having major issues with date columns in power query.
I have a group of excel files which are exported out from an property management system giving me details for each property. The excel data is shown as follows:
As you can see the dates are in UK format (which is what I want) but showing as text.
When extract this data into Power Query I get the following:
As you can see the Lease To Date is showing correctly but the Lease from Date is showing Date and Month the wrong way round. But for "Lease To Date" it is showing the right way
My region settings on the computer are UK and for the query are also UK. So I am at a loss as to why this is happening especially as the source file is text.
So my two questions are:
1. Why is this happening?
2. How do I get around this (my concern being that it seems query is doing something differently on different date columns - which does not fill me with confidence)?
Thanks in Advance
Moderators
January 31, 2022
Online
Can't see what's in the 1st screenshot but the 2nd one still shows the columns of a data type "Any" (i.e. ABC123). Set the data type to 'Date'. If needed use a the UK locale to be sure that the date come out correctly.
New Member
August 21, 2023
Offline
Thanks for coming back to me, I believe if you click on the screen shot it should zoom in. In the first screen shot the dates are in the form of text and in UK Date format. For example in the first row the first row shows "01/11/2028" in the first column and "31/10/2031" (To be clear the first date is 1st November 2028)
When you load these into Power Query they shows up as "11/01/2028" (i.e. 11th January 2028) which is incorrect and the second column shows up as 31/10/2031 which is correct.
My confusion is why is this happening when I am sure all of my settings are set to UK date format. Secondly, why is it showing correctly in one column but not the other?
Thanks
Moderators
January 31, 2022
Online
Yes now I see the screenshot better.
Could it be that the From column are texts and the To column real dates left justified?
When I load such a table to PQ with a regional setting for English US, I get similar results as you. Check if the PQ settings are indeed English UK like in the picture below. (Get Data, Query Options)
With data as describe above and a US setting the 'text dates' become January dates and the real dates are transformed correctly.
1 Guest(s)
