Power Query Problems

Replace Date Errors with Another Column in Excel Power Query

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Replace errors in a Power Query date column with the corresponding values from another column.

Replace Date Errors with Values from Another Column in Excel Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query

In Excel, select Data > Queries & Connections, then double-click the query to open Power Query Editor.

2
Add a custom column

Select Add Column > Custom Column. Name the column NewDay and enter: try Date.From([Day]) otherwise Date.From([Earl.start]). Select OK.

3
Set the date type

Select NewDay, then choose Transform > Data Type > Date.

4
Replace the original column

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.

5
Load the result

Select Home > Close & Load to return the updated query results to Excel.

Use a custom column to replace date errors
Check fallback values: If Earl.start also contains an error or cannot be converted to a date, the new column will still show an error for that row. Null values in Day are not errors and will remain null.
Free Microsoft Office alternative

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.

Review and edit date columns in WPS SpreadsheetCompatible with Microsoft Excel XLS and XLSX formatsFamiliar spreadsheet interface for an easier transitionFree and lightweight office suite
microsoft office alternative - wps office

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.