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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors