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

If you say "leading zeros" and "date order" means for me your dates are not dates, you have a text! And that's why your Pivot table did not work.

Furthermore, if you want to show data by weeks you can do that if you group the dates by 7 days with usual Pivot tables. But that works only if you have real dates...

Today's way to accomplish this is to load the data into a Data Model then create a calendar, create a relationship between the dates and create a Pivot table from the Data Model.

Show me your file, I create a sample file for you.

Andreas.

Leading zeros are most certainly a thing in a date. It is why there is a distinction even in the format cells option in excel:

If you look at my screen shots in the original posting of this thread you will see that my data is in fact formatted using a date rather than general or text. And then that column is automatically changed to a different format, before any change type steps have been accomplished by the power query.

Due to PII in the file I cannot share it but it is easily duplicated. Create a workbook where one column is a date in YYYY-MM-DD format and fill with random dates from this year. Import that data to a second workbook using power query. If you get anything other than DD/MM/YYYY then somewhere one of our settings is different.

Answer 2

Hi,

Click on the ABC123 icon next to the Date heading and select Date there. This action will change the data type of that column to Date so sorting will work just fine in your Pivot Table.

Answer 3

Click on the ABC123 icon next to the Date heading and select Date there. This action will change the data type of that column to Date so sorting will work just fine in your Pivot Table.

100% agree.

Christopher,

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

Sample file:

https://www.dropbox.com/s/lwofeosvqph2i4g/9b7b22a3-85ba-4c2d-b7bd-1667532ff507.xlsx?dl=1

You can drag any field from the calendar into the row or column section. As you see I don't care about the cell format, use any format you like.

Andreas.

Answer 4

Hello Christopher,

Unfortunately, I don't have any further troubleshooting to offer.

There are many knowledgeable users active on the forum and I hope that someone else can offer further insight into your issue.

Thanks

Answer 5

Hi,

Click on the ABC123 icon next to the Date heading and select Date there. This action will change the data type of that column to Date so sorting will work just fine in your Pivot Table.

There is no "Date" that formats into YYYY-MM-DD or MM/DD/YYYY that includes leading zeros for months and days. As such the dates get out of order if I select a week like week#19, which starts on 5/8 and ends on 5/12. it orders the dates as such in the pivot table:

5/10/2023

5/11/2023

5/12/2023

5/8/2023

5/9/2023

and it orders them that way regardless of which "sort" order I set for my pivot table (ascending, descending, or data set order).