Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JortBreij
Helper I
Helper I

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.

1 ACCEPTED SOLUTION
Payeras_BI
Super User
Super User

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.

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

3 REPLIES 3
Payeras_BI
Super User
Super User

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.

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Payeras_BI
Super User
Super User

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.

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors