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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors