How to Replace Date

Hi IfaZ_1 

OK there are several issues with that Excel file.

1. The row headers for the first 2 columns are across 2 rows, this isn't good practice and leads to a row of null values when imported.

2. The date in Excel is actually 0 which is displayed as 0/01/1900.  When this is imported to Power Query it is interpreted as 31/12/1899.  Your code was checking for 30-Dec-99 so wouldn't work.

Before giving you a solution to this, why is the first column in Excel set to a date of 0?  If it wasn't you wouldn't have this issue.

But, assuming you can't change that, what you need to do is add a new Custom Column in Power Query with this code

if [Last Assessment Date] = #date(1899,12,31) then [Audit_Issues_2.Assessment Date] else [Last Assessment Date]

which gives you this

You may want to read up on how date and time works in Excel.

Regards

Phil