Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Table.ReplaceValue custom step not working

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.

  • Hi Anonymous ,

    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.

     

3 Replies

  • Hi Anonymous ,

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

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