Date columns using locale not working correctly

[herr_morty]

Hi all folks,

My first time in this forum, I'm learning PQ and trying my best with M code
I had a super interesting learning on PQ on Udemy by Maven Analytics.

I'm having troubles with locale settings for dates.

- In my sheet dates are in the European format (I'm from Italy)
- Once I load in PQ the dates are reinterpreted in the US standard
- Even applying locale (date type) with IT does not modify the way dates are displayed
- Using locale with as text fields correctly converts the date to European format

I checked the regional setting:
- in the PC (Italy)
- in the PowerQuery workbook options (was US, set to Italy)

Is there some other options to check? Dunno, like "general excel options"? I'm very confused.

I used this very PC for my elearnings with dummy files provided by Maven via Udemy, and at the time locale worked perfectly. I'm not getting what I'm missing...

Sorry if it has already been posted, I tried with the research but found nothing useful

 

[sandy666]

how about this

or use another locale, eg. Italy (not tested)

 

[herr_morty]

how about this

Hi Sandy!
Thanks for your reply.

Unfortunately, that's exactly what I was trying in the first place. I tried Italy and UK as well, just to be sure. None worked, with data type "date" (with data type "text", the date changes to European).
Then I tried to start messing with other options.

Any other ideas? I'm totally clueless at this point

 

[sandy666]

originally your date is as text ?
if so make sure the whole column is as text, then split by delimiter / and merge in proper order / format then set format as date if necessary

 

[herr_morty]

Hey Sandy, I verified.
No, in the original sheet the data are correctly formatted as "date".
It's like PQ is unable to understand that I'm trying to format to a locale different from US

 

[sandy666]

could you share excel file with the date column only (original) ?
use onedrive, googledrive, dropbox or any similar

and I am not asking about format but is it a number or not. Date is a number, if you click on date then on formula bar you should see number, if you see date that means this is a text

 

[herr_morty]

and I am not asking about format but is it a number or not. Date is a number, if you click on date then on formula bar you should see number, if you see date that means this is a text

Sorry, did not understand.
It's a number in the format dd/mm/yyyy in raw data sheet, but in the PQ preview it's becoming mm/dd/yyyy (e.g., 23/09/2020 ->09/23/2020). In the output it's again correct as dd/mm/yyyy.
I also tested the very same file on another PC (private one) I have and the file gives no such problem, so not sure it would be possible to reproduce on your device.

It's like it's a problem liked to this specific PQ setup on my office PC, but I'm not getting which setup option.
Can it be an interference from Power BI app on Excel setup? I red BI has some issues with dates...

Anyway, find an image of the problem: PQDatePreviewIssue_20200728.JPG
Also, here is a dummy file I created with the issue: Provvisorio.xlsx

 

[sandy666]

I just open your file and

all is ok
I even can't test because format is proper
so as I suggested before
1. change column type to text
2. split by delimiter /
3. merge columns in proper order
4. try to set type date with/without locale

Note: I didn't change anything in settings

 

[sandy666]

maybe try also
Clear Formats

From Table
and see what will happen if you change type to Date