Table.ReplaceValue custom step not working

[JortBreij]

I have the following custom replace value step. However it doesn't work. 

Original value of [Woendag]= 8

Value it should replace to = 0

Value it shows instead = 8
Expression:

= Table.ReplaceValue(
    #"Waarde vervangen5",
    each [Woensdag],
    each
    if [StartDateDag]
      = "Wednesday" and Date.WeekOfYear([StartDate])
      = Date.WeekOfYear([Date]) and [HoursFirstDay]
      <= [Woensdag] * [Percentage]
    then
      [Woensdag] * [Percentage]
    else if [StartDateDag]
      = "Wednesday" and Date.WeekOfYear([StartDate])
      = Date.WeekOfYear([Date]) and [HoursFirstDay]
      > [Woensdag] * [Percentage]
    then
      [Woensdag] * [Percentage]
    else if [EndDateDag] = "Wednesday" and Date.WeekOfYear([EndDate]) = Date.WeekOfYear([Date]) and [HoursLastDayZZZ] <> null then [Woensdag] - [HoursLastDayZZZ] else if [EndDateDag] = "Wednesday" and Date.WeekOfYear([EndDate]) = Date.WeekOfYear([Date]) then
      [Woensdag] - [HoursLastDay]
    else if Date.AddDays(Date.StartOfWeek([Date], Day.Monday), 2)
      > [StartDate]
        and Date.WeekOfYear([StartDate])
        = Date.WeekOfYear([Date]) and [PercentageDisablement]
        <> null
    then
      [Woensdag] * [Percentage]
    else if [Date] >= [StartDateZZZ] and [Date] <= [EndDateZZZ] then
      [Woensdag] * [Percentage]
    else if [PercentageDisablement] > 0 then 
    [Woensdag] * [Percentage]
    else
      [Woensdag],
  Replacer.ReplaceValue,
  {"Woensdag"}
)

It should be activated on "if [PercentageDisablement] > 0 then [Woensdag] * [Percentage]
Which would be 8 x 0 = 0

Instead it shows 8

Collumn information on row with problem:

[Date] = 24-1-2023
[Woensdag] = 8
[StartDate] = 18-1-2023

[StartDateZZZ] = null
[StartDateDag] = Wednesday
[EndDate] = 26-1-2023 

[EndDateZZZ] = null

[EndDateDag] = Thursday
[HoursFirstDay] = 8
[HoursLastDay] = 0
[HoursLastDayZZZ] = null
[Percentage] = 0
[PercentageDisablement] = 1

Note: "Woensdag" is Wednesday in Dutch and "Dag" is day in dutch

Already tried replacing the last 'else [Woensdag]' with 'else 0' but still it showed 8.

[Payeras_BI]

Hi,

Actually "if either or both operands are null, the result is the null value" according to this:

M Language Operators - PowerQuery M | Microsoft Learn

I understand it generates an error that propagates till the end of the calculation chain.

[Payeras_BI · Accepted solution]

Hi @JortBreij ,

The issue comes from having null values in [StartDateZZZ] and [EndDateZZZ] as this 

[Date] >= [StartDateZZZ] and [Date] <= [EndDateZZZ]

is evaluated before 

PercentageDisablement] > 0

Try substituting null dates by with a dummy date like 01-01-1900 and it should work like expected.

[JortBreij]

Yes it worked! Does this mean bigger and lesser than statements always result in true when having null as date value?