Powerquery changing month to day

[hamohd70]

Hello,

while using power query on a folder with multiple file, I noticed that power query has changed the date field when imported from one of the files. To make it clear, I have a date/time field that is 11/05/2023 02:00:00 referring to 11 May 2023 (the cell is formatted as dd-mmm-yy h:mm:ss). However, power query imported it as 5 November 2023 !. how can I revert the date back to the correct format?

Note that the regional date is set to 11 May 2023.

 

[RoryA]

It sounds like the format of the cell is not the same as the displayed value, which suggests the data is actually stored as text. That means that Power Query will use your regional settings by default. In PQ go to File - Options and Settings - Query options, then check the regional settings:

 

[hamohd70]

thanks for the reply. the field is configured as Date/Time

and the setting is

 

[RoryA]

That’s the problem. You need to change the region to one that doesn’t expect the month before the day (basically anywhere but the US)

 

[hamohd70]

I changed to English-UK but nothing changed (I changed it for the existing imported data).

 

[RoryA]

You need to change it in the workbook with the query, then refresh the query.

 

[hamohd70]

Do you mean the source cell? it is formatted as (dd-mmm-yy h:mm:ss).

 

[RoryA]

No, I mean the PQ settings need to be changed in the workbook that has the query in it.

 

[hamohd70]

ok. i will try it

 

[hamohd70]

thanks RoyA. problem solved.