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

Hi Christopher

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

If Power Query is automatically changing the date format in your data when you load it into a pivot table, you can try changing the date format in the Power Query Editor to prevent this from happening.

Here’s how you can do it:

Open the Power Query Editor by clicking on the Data tab in the ribbon and selecting Edit Queries from the Get & Transform section. In the Power Query Editor, select the column containing your dates. Click on the Transform tab in the ribbon and select Data Type: Date from the Any Column section to set the data type of the column to Date. Click on the Replace Values button in the Any Column section of the Transform tab. In the Replace Values dialog box, enter the current date format (e.g., “M/d/yyyy”) in the Value To Find field and enter your desired date format (e.g., “yyyy-MM-dd”) in the Replace With field. Click on OK to apply the changes and close the Replace Values dialog box.

After making these changes, your dates should be displayed in your desired format when you load your data into a pivot table

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Click on the Replace Values button in the Any Column section of the Transform tab. In the Replace Values dialog box, enter the current date format (e.g., “M/d/yyyy”) in the Value To Find field and enter your desired date format (e.g., “yyyy-MM-dd”) in the Replace With field.

Absolutely nonsense. A date is a number not a string! If you change it into a string you can't create relationships within a Data Model nor make calculations in Power Query anymore.

Power Query shows the data in your locale date format, that's all. What you see in there is a data view, not a users view! A cell format doesn't matter nor exists in Power Query.

Furthermore, why do you want to create such a calendar tool in Excel and load it into Power Query? There are several MCodes available that can create a dynamic calendar automatically from a given data.

https://www.dropbox.com/s/bf5f55g291ybe96/fnCalendar.pq?dl=1

And in Power Pivot you can also create such calendars using the build in functionality.

However, if you load a (real!) date from Power Query into Excel it depends on the cell format how the date is shown, not what you see in the Power Query editor.

Andreas.

Answer 3

Open the Power Query Editor by clicking on the Data tab in the ribbon and selecting Edit Queries from the Get & Transform section. In the Power Query Editor, select the column containing your dates. Click on the Transform tab in the ribbon and select Data Type: Date from the Any Column section to set the data type of the column to Date. Click on the Replace Values button in the Any Column section of the Transform tab. In the Replace Values dialog box, enter the current date format (e.g., “M/d/yyyy”) in the Value To Find field and enter your desired date format (e.g., “yyyy-MM-dd”) in the Replace With field. Click on OK to apply the changes and close the Replace Values dialog box. After making these changes, your dates should be displayed in your desired format when you load your data into a pivot table I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions. Best Regards, AnnaThomas Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Thank you for the attempted answer but from the get go there is problems. I have no "Edit Queries" icon in the "Get & Transform section of the Data tab.

I can get the power query editor open in other ways and attempting to perform the "replace values" recommendation does not work either (likely because it is looking to replace specific values rather than the format):

Answer 4

Power Query shows the data in your locale date format, that's all. What you see in there is a data view, not a users view! A cell format doesn't matter nor exists in Power Query.

Furthermore, why do you want to create such a calendar tool in Excel and load it into Power Query? There are several MCodes available that can create a dynamic calendar automatically from a given data.

https://www.dropbox.com/s/bf5f55g291ybe96/fnCalendar.pq?dl=1

And in Power Pivot you can also create such calendars using the build in functionality.

However, if you load a (real!) date from Power Query into Excel it depends on the cell format how the date is shown, not what you see in the Power Query editor.

Andreas.

Andreas I am not trying to create a calendar tool. The pivot table is being used to show tasks completed for a specific week number on a Monday through Friday basis, which is why it is imperative that the dates be in the correct order instead of having the leading zeros removed.

Answer 5

The pivot table is being used to show tasks completed for a specific week number on a Monday through Friday basis, which is why it is imperative that the dates be in the correct order instead of having the leading zeros removed.

You are on a total wrong track.

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.