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

Furthermore, you don't need all that using a calendar, random data:

note that the date is not displayed anywhere in your pivot table, so your displayed table is missing the key piece of info I'm asking about.

Answer 2

Andreas.

not sure what changed between yesterday and today but it apparently decided it wanted to work:

Still doesn't explain how to change the format in the power query editor, which through google searches I've seen quite a few other people ask. I know you can "change locale" but I've been unable to find a list of which locales use which format, and even once I found one that would work I would have to scroll through the couple hundred options which would be super time consuming/confusing. It seems like there should be an option in the query editor that either matches the format that the data came in with or is similar to how I would change a format in a regular cell. But maybe that is too hard?

Answer 3

The key piece is to believe it works. ;-) Here is your date, simply dropped from the Calendar into the PT:

Same file.

I've also created a field "DateCR" inside the Data Model (which is sorted by Date), just to show that you can create any format you like. Any further questions?

Andreas.

Answer 4

Andreas.

Andreas I appreciate the attempts to solve the question. As you can see from the screen shot of my pivot table I need the date displayed where you have your "dayname" info. I'm then using a slicer to show only the week number that the user wants displayed.

Answer 5

As you can see from the screen shot of my pivot table I need the date displayed where you have your "dayname" info. I'm then using a slicer to show only the week number that the user wants displayed.

Again: You can drag&drop any field from my calendar into the row or column section as you like, it works as you expect.

And or course you can use that fields too to create slicers.

I've used the "Year-ISO Week" field for the slicers, because 2023 has KW52 twice! one is from the last week in 2022 and the other is at the end of 2023.

Same file.

Any further questions?

Andreas.