A Power Query date error usually means Excel could not interpret one or more values as dates, often because the source uses a different date order or contains invalid values. In Excel for Microsoft 365 on Windows, edit the query, select the affected column, and choose Transform > Data Type > Using Locale to set the correct date format and region; then inspect and correct any values that still fail.
What a Power Query date error means
Power Query reads imported data and assigns each column a type, such as text, number, date, or date/time. A date-conversion error appears when a value cannot be converted using the rules currently applied to that column. The error may be visible as the word Error in a cell in Power Query Editor, or as a query-loading problem if the failing step prevents the result from being produced.
The message often says that Power Query could not parse an input as a Date value. That does not necessarily mean the date is missing. It can mean that the date is written in an unexpected order, includes text, or represents a calendar day that does not exist. For example, 31/12/2025 is a valid day-month-year date but cannot be read as month-day-year because there is no thirty-first month.
Power Query steps are applied in sequence. A type-conversion step may be created automatically when data is imported, and a later step can depend on that conversion. Fixing the source interpretation or the conversion step is usually better than simply hiding the errors.
Common causes of errors in date columns
Several different problems can look alike in the preview. Identifying which one applies helps avoid changing good data unnecessarily.
- Different regional conventions: 04/05/2025 can mean April 5 or May 4. The text alone does not establish which date was intended.
- Invalid calendar dates: 29/02/2025 is not valid because 2025 is not a leap year. Values such as 31/04/2025 are also invalid.
- Mixed values: A column may contain dates alongside labels such as Pending, a dash, or an explanatory note.
- Unwanted spaces or characters: Leading or trailing spaces, nonbreaking spaces, or an attached time-zone label can stop a value from parsing.
- Inconsistent source formats: A file may combine ISO-style dates such as 2025-05-04 with slash-separated dates, or may change format between monthly exports.
- Numbers mistaken for dates: A source may store dates as numeric serial values or as text that looks numeric. Those values need to be interpreted according to their source and date system, not guessed.
A blank is not automatically an error. Some sources use an empty value or null to mean that no date is available. Keep that distinction in mind: replacing every error with a blank can conceal malformed records, while turning genuine blanks into errors creates needless cleanup.
Use the correct locale to interpret dates
Locale controls how Power Query reads regional date conventions, including the order of day, month, and year. For example, a source using day/month/year should not be converted as though it used month/day/year. If all the values follow one known convention, a locale-aware conversion is often the cleanest fix.
In Excel for Microsoft 365 on Windows, open the query from Data > Queries & Connections, then double-click it to launch Power Query Editor. Select the date column and choose Transform > Data Type > Using Locale. In the dialog, set Data Type to Date and choose the locale that matches the source, not merely the language or region of your computer. Select OK and check the preview before loading the result.
For instance, if a supplier documents its dates as day/month/year, choose a locale that uses that convention. The value 04/05/2025 will then be interpreted as 4 May rather than 5 April. Confirm the meaning against a known record before applying the change to a full dataset; ambiguous dates cannot be resolved reliably without knowing the source convention.
The Using Locale command creates a conversion step in the query. If an earlier automatic Changed Type step already converted the column incorrectly, edit or remove that step as appropriate, then apply the locale-aware conversion. Otherwise, the values may already have been changed or may still fail before the new step can help.
Find the rows and values that are failing
Before replacing or removing errors, locate the records and determine whether the problem is a format mismatch or bad source data. In Power Query Editor, select the relevant column and use Home > Keep Rows > Keep Errors to display rows containing errors in that column. This is useful for diagnosis, but remember that the command adds a filtering step; remove that step afterward if you want the final query to include all valid records.
Selecting an error cell can show details about the error in the preview. Depending on the problem, the message may identify a failed conversion or include the value that could not be interpreted. Compare several failing rows with successful rows and with the original file. A pattern often reveals the cause: perhaps only dates after the twelfth day fail under the wrong locale, or only rows containing a text label fail.
Also inspect the Applied Steps list. If the error first appears after a step named Changed Type, that step is a likely point to investigate. Selecting earlier steps helps establish whether the source already contains errors or whether conversion introduced them. Avoid deleting steps at random: later steps may rely on the column having a date type.
A practical repair process
Use this sequence to fix the query while preserving the records that are still valid:
- Open the query and inspect the source. In Excel, select Data > Queries & Connections and open the relevant query. Compare problem values in the preview with the original workbook, CSV, or other source.
- Check the conversion step. In Applied Steps, select the step that assigns the date type. If the source has a consistent regional format, replace or adjust the conversion using Transform > Data Type > Using Locale.
- Separate genuine dates from non-date entries. Decide what labels such as Pending or N/A mean. If they represent missing dates, handle them deliberately rather than treating them as calendar values.
- Correct invalid source records where possible. If a value is mistyped, fixing it in the source can make the repair transparent and repeatable. If the source cannot be changed, use a documented transformation suited to that specific pattern.
- Review the result and load it. Check representative records, confirm the column shows dates rather than text or errors, and choose Home > Close & Load.
If the file is refreshed regularly, test the query against a later or representative source file too. A fix based on one month’s data may fail next month if the export changes its format or introduces a new status label.
Choose whether to correct, replace, or remove errors
The right treatment depends on what an error represents. Correct a value when the intended date is known and the source is wrong. Use a locale-aware conversion when the values are valid but interpreted under the wrong convention. If an error represents a record that cannot be used, removing that row may be reasonable, but it should be a conscious data-quality decision.
Replacing errors with null can be appropriate when the date is optional and the record remains useful without it. In Power Query Editor, you can right-click an error in the column and choose Replace Errors, then provide a replacement such as null. Check how downstream steps handle the result. For example, a date-based grouping or calculation may exclude null dates or require additional handling.
Do not replace errors with an invented date just to make the column load. A default such as the first day of the year can distort sorting, summaries, and reports by making unknown dates look real. Likewise, removing every row with an error may discard useful customer, order, or event information because only one field is malformed. Keep a record of the chosen treatment if the output is used by other people.
Keep date types and source systems in mind
A date and a date/time value are different types. If a source includes both a day and a time, converting it to Date removes the time component; converting to Date/Time preserves it. Choose the type that matches the purpose of the column. A timestamp used to order events should generally not be reduced to a date unless losing the time is intentional.
Excel stores dates as serial numbers in worksheets, while Power Query has its own typed date values and conversion rules. A number that appears as a date in an Excel cell may arrive differently depending on how the source is imported and how the query steps are applied. Excel workbooks can also use the 1900 or 1904 date system, which changes the serial number associated with a calendar date. If a column contains numbers rather than recognizable date text, verify the source’s date system and intended meaning before converting it.
Formatting is not the same as conversion. Applying a date display format to text in the worksheet does not necessarily make that text a true date in Power Query. Conversely, Power Query can hold a typed date that displays differently after loading because of the destination cell format. Confirm the data type in the query preview, then separately check how the loaded column is displayed in Excel.
Prevent errors when queries refresh
A query that works today can fail on refresh if the source changes. Ask the provider to keep date formats and column meanings consistent, and avoid relying on a computer’s default locale when the source convention is known. A locale-aware conversion makes that expectation explicit in the query.
It also helps to check for non-date entries before conversion. If a field legitimately contains status text as well as dates, the source is not a pure date column; decide whether to preserve the status in a separate field, map it to a missing value, or flag it for review. Keeping raw source data unchanged and applying clear, named transformation steps makes later troubleshooting easier.
To confirm a repair, refresh the query and inspect both the preview and the loaded table. In Excel for Windows, use Data > Refresh All to refresh workbook queries. Excel for Mac and Excel for the web may have different Power Query editing or refresh capabilities depending on the version and data source, so the available controls can differ; the locale and type principles remain the same.
Frequently Asked Questions
Why does Power Query say it cannot parse a date?
The value does not match the format or locale used by the conversion step, or it is not a valid date. Check the original value, the Changed Type step, and the source’s day-month-year convention.
How do I change the date format in Power Query?
Select the column in Power Query Editor and choose Transform > Data Type > Using Locale. Set the type to Date and select the locale that matches how the source writes its dates.
Can I replace date errors with blanks?
Yes, if an unknown date should be treated as missing and the row is still useful. Replace Errors with null, then check whether later query steps and reports handle missing dates correctly.
Why do some rows convert and others become errors?
The column may contain mixed formats, invalid calendar dates, extra characters, or text labels alongside dates. Keep only error rows temporarily, inspect the values, and compare them with the source.
Will refreshing bring the date errors back?
It can if new source rows use a different format or contain new invalid values. A locale-aware step prevents ambiguity for a consistent format, but it cannot make an invalid date valid; test each refresh and address new exceptions.
Related
- Contact List Excel Spreadsheet: Create and Manage It
- Conditional Formatting for Errors in Excel: How to Do It
- Contact List Excel Example and Setup Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add a Cover Page in Microsoft Word Step by StepIn Word for Microsoft 365 on Windows, select Insert > Cover Page, choose a design, and replace its…
- Excel Vertical Text: How to Rotate Text in CellsTo make text vertical in Excel for Microsoft 365 on Windows, select the cell or range, then choose…
- Excel Not Responding: How to Save Your Work SafelyIf Excel says Not Responding, first stop clicking and give it time to finish; if the window still…
- How to Set Up Out of Office in OutlookTo set up an out-of-office reply in Outlook, open Automatic Replies, switch them on, write your message, and—if…
- Free Excel Alternatives: Which Spreadsheet Fits You?Yes. LibreOffice Calc is a strong free substitute for Excel on a computer, while Google Sheets is often…
- Insert Time in Google Sheets: Entry, Shortcuts & FormulasTo insert a time in Google Sheets, select a cell and type a value such as 9:30 AM…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.