Forum Discussion
Date.AddDays with a Date.DayOfWeek based calculation gives cannot convert the value null to type Num
Several ways to fix. You can wrap the whole thing in an if/then/else statement.
#"Added Custom" = Table.AddColumn(#"Last step", "Ref date", each if [StoredDate] = null then null else Date.AddDays([StoredDate],-(Date.DayOfWeek([StoredDate])-5)-7)),
Or if it evaluates to error, return null
#"Added Custom" = Table.AddColumn(#"Last step", "Ref date", each try Date.AddDays([StoredDate],-(Date.DayOfWeek([StoredDate])-5)-7) otherwise null),
You could also replace the null in [StoredDate] with 0, but while that will not produce an error, it will give you bogus dates as 0 is Dec 31, 1899.
Several ways to fix. You can wrap the whole thing in an if/then/else statement.
#"Added Custom" = Table.AddColumn(#"Last step", "Ref date", each if [StoredDate] = null then null else Date.AddDays([StoredDate],-(Date.DayOfWeek([StoredDate])-5)-7)),
Or if it evaluates to error, return null
#"Added Custom" = Table.AddColumn(#"Last step", "Ref date", each try Date.AddDays([StoredDate],-(Date.DayOfWeek([StoredDate])-5)-7) otherwise null),
You could also replace the null in [StoredDate] with 0, but while that will not produce an error, it will give you bogus dates as 0 is Dec 31, 1899.
