Forum Discussion
How to Replace Date
Hello everyone,
I want to replace the Last assessment Date (00-01-00) to Audit_Issues_2.Assessment Date the outcome should be in FTY_ASSESSMENT_DATE.
I have tried using power query with this condition :
It still doesnt work.
Ps: in Excel the date is 00-01-00 however in Power Query is it 30-Dec-1999. I tries using 00-01-
| Last Assessment Date | Audit_Issues_2.Assessment Date | FTY_ASSESSMENT_DATE |
| 00-01-00 | 27-07-20 | 00-01-00 |
| 00-01-00 | 08-09-20 | 00-01-00 |
| 00-01-00 | 13-07-20 | 00-01-00 |
| 00-01-00 | 15-07-20 | 00-01-00 |
| 00-01-00 | 18-12-20 | 00-01-00 |
| 00-01-00 | 04-11-20 | 00-01-00 |
| 00-01-00 | 16-09-20 | 00-01-00 |
| 00-01-00 | 14-09-20 | 00-01-00 |
5 Replies
Hi IfaZ_1
The data in the Last Assessment column aren't dates so your code checking if it is equal to another date will always result in False which si what you are getting. The result is Last Assesment Date
What data are you trying to store in the Last Assessment Date column?
00-01-00 isn't a date.
Regards
Phil
Hi Phil,
Thank you so much for your reply.
I have changed Last Assessment Date, Audit Date and FTY_ASSESSMENT_DATE to Date format already. Still doesnt solve it.
Hi IfaZ_1
Please supply your data or at least a sample of it. I don't know how 00-01-00 is a valid date.
Regards
Phil
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
