Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I would like to replace the 0's in my column with the Max Value which in this case is 4. I need it to be dynamic so can't just replace value as the value (4) will change every month.
I have tried creating a new custom column:
=List.Max([CurrentMonthForActualsNo])
But I get errors. Please help!
Solved! Go to Solution.
Hi @EMSSS22 ,
You could try to add a custom column. Here's an example.
if [Column1]=0 then List.Max(#"Changed Type"[Column1]) else [Column1]
What you need to note is that
List.Max(#"Changed Type"[Column1]), be sure to add the name of the previous step, not List.Max([Column1]). Otherwise, an error will be returned.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @EMSSS22 ,
You could try to add a custom column. Here's an example.
if [Column1]=0 then List.Max(#"Changed Type"[Column1]) else [Column1]
What you need to note is that
List.Max(#"Changed Type"[Column1]), be sure to add the name of the previous step, not List.Max([Column1]). Otherwise, an error will be returned.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try,
Table.AddColumn(#"Previous Step", "maxIfZero", each if [ColumnWithZeros] = 0 then List.Max(#"Previous Step"[ColumnWithZeros]) else [ColumnWithZeros])
Just change the previous step and column name with your info.
Proud to be a Super User! | |
Thanks, so I've tried that and got the following:
Tried to click on Table and doesn't load...
Can you share the code you used?
Proud to be a Super User! | |
Table.AddColumn(#"Replaced Value", "MaxIfZero", each if [CurrentMonthForActualsNo] = 0 then List.Max(#"Replaced Value"[CurrentMonthForActualsNo]) else [CurrentMonthForActualsNo])