Wrong values derived when replacing values from another column

[hlin]

Hi,

When I prepared date for work completed, I use the logic below:

= Table.AddColumn(#"Changed Type15", "Custom", each if [WO Work Completed] <> #date(2099, 12, 31) then [WO Work Completed] else if [WO Date As Built Sub] <> #date(2099, 12, 31) then [WO Date As Built Sub] else if [WO Date As Built Rec] <> #date(2099, 12, 31) then [WO Date As Built Rec] else if [Date TECO 1st] <> #date(2099, 12, 31) then [Date TECO 1st] else #date(2099, 12, 31)).

However, the results are shown below:

WORK ORDER	WO Work Completed	WO Date As Built Sub	WO Date As Built Rec	Date TECO 1st	Work Completed Date Derived	Work complete date Expected	Variance
40013831				21/05/2014	21/05/2014	21/05/2014	-
40013249				10/09/2014	9/10/2014	10/09/2014	-     29.00
40013250				10/09/2014	9/10/2014	10/09/2014	-     29.00
40013251				10/09/2014	9/10/2014	10/09/2014	-     29.00
40013253				10/09/2014	9/10/2014	10/09/2014	-     29.00

I found that the month and day were exhanged. how to fix these?

Regards,

Ling 

[v-qiuyu-msft]

Hi @hlin,

I'm not very clear the issue. From your posted power query, there should be a new column named "Custom". But in your results table, there is no "Custom" column returned. 

Please share some sample table data before you add the custom column, and clarify the result you desired. 

Best Regards,
Qiuyun Yu  

[hlin]

Hi Vicky,

Thank you for your effort. actually the column from the calculation should be Work Completed Date Derived. I added a word of "derived" intending to make it clearer, but caused a bit confusion, sorry.

Regards,

Ling