power query in excel automatically changes date format

Question

I have data for dates in a column with YYYY-MM-DD format:

I have created a power query for this data to build a pivot table with. No matter what I try as soon as I bring in my data it forces the dates into a DD/MM/YYYY format with any leading zeros removed (ie changes 2023-05-19 to 5/19/2023 or changes 2023/05/06 to 5/6/2023)

The data in this column is used for the pivot table sorting and having the dates without the leading zeros gets everything out of order (ie provides "5/1/2023, 5/10/2023, 5/11/2023, 5/2/2023, 5/3/2023, ..." instead of "5/1/2023, 5/2/2023, 5/3/2023, ... 5/10/2023, 5/11/2023". google has failed me in providing the correct way to solve this. I really don't want to change my locale because I'm not sure what other unintended consequences that will have with other column calculations. So how can I force it to stay in the format the data is in? Or what formula can I add to my "changed type" step to force it into a format that I want?

Answer 1

Any further questions?

Andreas.

Yeah, show a screen shot of the power query editor and how you would change your date to be in YYYY/MM/DD format.

if all I'm doing is building a pivot table off of data in the sheet it works fine. my problem from the beginning has been that the data I'm bringing into the power query editor is automatically getting changed (before any "change type" actions are performed) from YYYY-MM-DD to MM/DD/YYYY. your data is in DD/MM/YYYY and I'm going to guess that your locale is set to the same. which means that your power query would remain in the same format your data is in...and again isn't answering the question asked.

Answer 2

my problem from the beginning has been that the data I'm bringing into the power query editor is automatically getting changed (before any "change type" actions are performed) from YYYY-MM-DD to MM/DD/YYYY. your data is in DD/MM/YYYY

Again: The data in Power Query has no format, the Data Model has not cells nor uses any format at all. If we create a date in Power Query or read dates from Excel or external sources stores numbers into the Data Model.

The date format you see inside Power Query or Power Pivot is an illusion, it's just a "helper view" for the developer.

If the data is written back into a sheet the cell contains a number, then Excel applied a date format and so convert the number to the D/M/Y format you're talking about.

Open my file, select all cells and format them as General. After that you can see what's really in the cells.

There are no dates nor times nor currencies or accounting in Excel, all that's an illusion for humans. Computers can only calculate with numbers.

Andreas.