in-column replacement "error " using try otherwise and #datetime(YY,MM,DD)

[Swalker]

Hi ,

I have a table with Date, FinancialPeriod (= month) and FinancialYear from entries.

The accounting software uses FP and FY as key parameters

Some Dates are wrongly entered : they do not match the FinancialPeriod : example: 

Date	FinancialPeriod	FinancialYear	Day
28/5/2017	6	2017	28	not okay
3/10/2017	11	2017	3	not okay
31/5/2017	5	2017	31	ok

where [Day] is:

#"Inserted Day" = Table.AddColumn(#"Changed Type1", "Day", each Date.Day([Date]), Int64.Type)

In order to correct this I create an AltDate comprising of :

#"Added AltDate" = try Table.AddColumn(#"Inserted Day", "AltDate", (each #datetime([FinancialYear],[FinancialPeriod],[Day],0,0,0)), type datetime)

However - this works fine for most of the dates but produces <1% errors for the ones listed as below:

Date	FinancialPeriod	FinancialYear	Day	AltDate	AltDate valid
28/5/2017	6	2017	28	28/6/2017	28/6/2017
3/10/2017	11	2017	3	3/11/2017	3/11/2017
31/5/2017	6	2017	31	error	28/6/2017
31/10/2017	11	2017	31	error	28/11/2017
31/1/2017	2	2017	31	error	28/2/2017

For the AltDate it is important that the AltDate is in the FinancialPeriod and FinancialYear - the day may deviate eg 3 days

Thus if we say that in case of error we substract 3 days from Day : [Day-3] then it should always work eg 31-3=28 (works also in february)

My approach was to use try [expression [Day] ]  otherwise  [expression including Date.AddDays([Day],-3]) like this:

#"Added Custom" = try Table.AddColumn(#"Inserted Day", "AltDate1", (each #datetime([FinancialYear],[FinancialPeriod],[Day],0,0,0)), type datetime) otherwise Table.AddColumn(#"Inserted Day", "AltDate1", (each #datetime([FinancialYear],[FinancialPeriod],Date.AddDays([Day],-3),0,0,0)), type datetime)

but this does not work:

the error in the column (for <1% of all AltDate values) is: 

"Expression.Error: The DateTime operation failed because the resulting value falls outside the range of allowed values."

Any help appreciated !

[HotChilli · Accepted solution]

A few alterations should fix it:

#"step" = Table.AddColumn(#"Added Custom1", "AltDate1", each try #date([FinancialYear], [FinancialPeriod], [Day]) 
otherwise   Date.EndOfMonth(#date([FinancialYear], [FinancialPeriod], 1)))

This will put the end of the month in but you can change it if you want to stick with the (subtract 3 days) method

[HotChilli · Accepted solution]

A few alterations should fix it:

#"step" = Table.AddColumn(#"Added Custom1", "AltDate1", each try #date([FinancialYear], [FinancialPeriod], [Day]) 
otherwise   Date.EndOfMonth(#date([FinancialYear], [FinancialPeriod], 1)))

This will put the end of the month in but you can change it if you want to stick with the (subtract 3 days) method

[Swalker · Accepted solution]

Many thanks @HotChilli for this elegant solution !