Date columns using locale not working correctly

herr_morty

New Member
Joined
Jul 26, 2020
Messages
6
Office Version
365
Platform
Windows
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 :)
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
how about this

locale.png

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

herr_morty

New Member
Joined
Jul 26, 2020
Messages
6
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
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

New Member
Joined
Jul 26, 2020
Messages
6
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
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
 
Last edited:

herr_morty

New Member
Joined
Jul 26, 2020
Messages
6
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
I just open your file and

locale.png

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

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
maybe try also
Clear Formats
clearformats.png

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

Watch MrExcel Video

Forum statistics

Threads
1,102,561
Messages
5,487,575
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top