Replace Date Errors with Another Column in Excel Power Query
Question details
Replace errors in a Power Query date column with the corresponding values from another column.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A query has a Day column and an Earl.start column that can provide fallback dates.
- Observed behavior
- Some values in Day produce errors; the desired result is to use Earl.start for those rows while keeping valid Day values.
Open the query in Power Query Editor and confirm the exact names of the date and fallback columns. Check that the fallback values can be converted to dates.
Use a custom column to replace date errors
Use try...otherwise to keep valid Day dates and substitute the corresponding Earl.start value when Day produces an error.
In Excel, select Data > Queries & Connections, then double-click the query to open Power Query Editor.
Select Add Column > Custom Column. Name the column NewDay and enter: try Date.From([Day]) otherwise Date.From([Earl.start]). Select OK.
Select NewDay, then choose Transform > Data Type > Date.
Right-click the original Day column and select Remove. Right-click NewDay, select Rename, and enter Day. Drag the column to its original position if needed.
Select Home > Close & Load to return the updated query results to Excel.

Work with Excel Date Tables in WPS Spreadsheet
WPS Office is a free, lightweight Microsoft Office alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with date tables in WPS Spreadsheet; it does not fix Excel Power Query errors.

Frequently Asked Questions
Why use try...otherwise instead of Replace Errors?
A custom column with try...otherwise can evaluate the fallback column for each row. Enter try Date.From([Day]) otherwise Date.From([Earl.start]) under Add Column > Custom Column.
Will this formula replace blank dates?
No. A null Day value is not an error, so the formula leaves it as null. Handling blanks requires a separate null check.
What if the fallback column also has invalid dates?
Date.From([Earl.start]) will produce an error if the fallback value cannot be converted. Check that column in Power Query Editor or add separate error handling for the fallback.
Do I need to use Advanced Editor?
No. Add Column > Custom Column is enough for this approach. Advanced Editor is an alternative if you prefer to edit the query's M code directly.




