Power Query Problems

How to Preserve and Sort Dates Correctly in Excel Power Query

Natalie Taylor Oct 22, 2026 868 views

Question details

The user needs to properly format, preserve, and chronologically sort date values in Power Query without them defaulting to incorrect text or system locale formats.

How to Preserve and Sort Dates Correctly in Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Importing and transforming data containing dates where formatting might affect sorting in PivotTables or data models.
Observed behavior: Dates may appear in the system locale format or as text, causing alphabetical rather than chronological sorting issues if not converted to proper real Date data types.

Before you start

Review the current format of your date columns in the Power Query Editor to determine if they are currently stored as text values (ABC) or actual dates.

Solution 1Recommended

Convert Text to Date Data Type Using Locale Settings

Use the locale conversion feature to ensure date strings are accurately parsed into real date values, enabling chronological sorting.

To sort dates properly in Excel PivotTables or the Data Model, the dates must be stored as real date values in Power Query, not as text.

If your source data uses a specific regional date format, you can bypass your system's default locale by specifying the exact source locale during the data type conversion.

Select the target column

Open the Power Query Editor, locate the column containing your dates, and right-click on the column header.

Change data type with locale

From the context menu, navigate to 'Change Type' and select 'Using Locale...' at the bottom of the list.

Specify the exact locale

In the dialog box, set the Data Type to 'Date' and choose the locale that matches the original format of your text dates, then click OK.

Convert Text to Date Data Type Using Locale Settings

Correct Chronological Sorting: Once transformed into real date values, they will sort chronologically in your Excel PivotTables regardless of their final visual display format.

Solution 2

Apply Visual Date Formatting After Loading into Excel

Manage the visual presentation of dates (such as adding leading zeros) inside Excel rather than altering the core data values in Power Query.

Free Microsoft Office alternative

Manage and Format Spreadsheet Data Effortlessly with WPS Office

If you need a streamlined, highly compatible environment for managing complex datasets, PivotTables, and date formatting without the overhead of heavy software, consider WPS Office. It provides a lightweight, free alternative to Microsoft Office with intuitive data handling tools.

1. Download the software: Visit the official WPS website and download the free WPS Office suite.
2. Install and launch: Follow the simple installation steps and open WPS Spreadsheet.
3. Open existing workbooks: Open your .xlsx files to easily manage date formats, build PivotTables, and analyze your data seamlessly.

Completely free to download and use with a highly familiar user interfaceFully compatible with Microsoft Excel (.xlsx) formats and PivotTable structuresIntuitive cell formatting and date conversion tools that are easy to masterLightweight installation with incredibly fast performance on older or lower-spec devices

microsoft office alternative - wps office

Frequently Asked Questions

Why do my dates sort alphabetically instead of chronologically in PivotTables?

This happens when your date values are being interpreted and stored as text rather than real date numbers. To fix this, you must convert the column to the 'Date' data type within Power Query before loading it into your PivotTable.

Can I use 'Replace Values' in Power Query to reformat my dates?

No, it is highly advised not to use 'Replace Values' for date formatting. Replace Values alters text strings. Date formatting should be handled by converting the column to a Date type in Power Query, and then applying a display format in Excel.

How do I handle dates with missing leading zeros in Power Query?

Leading zeros are purely a display formatting issue and do not affect the chronological order of real dates. Focus on establishing the column as a Date data type in Power Query, then apply a custom cell format like 'yyyy-mm-dd' after the data is loaded into Excel.

Do I need to change my computer's regional settings to fix Power Query date errors?

No, it is unnecessary to alter your system settings. You can utilize the 'Change Type > Using Locale' option directly inside the Power Query editor to parse specific foreign or non-standard date formats correctly.

Excel & Data Problems