Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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.
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.
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.
Yes it worked! Does this mean bigger and lesser than statements always result in true when having null as date value?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
10 | |
10 | |
8 | |
7 |