Date.AddDays with a Date.DayOfWeek based calculation gives cannot convert the value null to type Num

[cholton]

This step of my query

#"Added Custom" = Table.AddColumn(#"Last step", "Ref date", each Date.AddDays([StoredDate],-(Date.DayOfWeek([StoredDate])-5)-7)),

gives

[Expression.Error] We cannot convert the value null to type Number.

I can see this by using the column filter arrow and choosing load more. The Expression.Error then appears below the list of values. The error doesn't stop query processing until several steps later when I try to expand a merge that relies on a derivative field based off the Ref date. (I checked data types and for nulls on both sides of the merge and the field I want to expand before finding this hidden error several steps prior.)

The complete query was running when it was based off of a different date, but I can't find any problems with the new StoredDate field. It doesn't show any errors. I have replaced nulls with a dummy date. It is formatted as date field.

I've been unsuccessful finding this particular problem solved. Grateful for help.

[edhans · Accepted solution]

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.

[cholton]

Thank you. No nulls and no visible row errors in [StoredDate], but try/otherwise worked so there was something.

[edhans]

Glad to help @cholton 

try/otherwise has saved me several times as well.