Forum Discussion
Replace 0 with Max Value in Column Power Query
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!
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.
5 Replies
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.
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.
