Replace 0 with Max Value in Column Power Query

[EMSSS22]

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!

[v-stephen-msft · Accepted 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.           

[jgeddes]

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.

[EMSSS22]

Thanks, so I've tried that and got the following:

Tried to click on Table and doesn't load...

[jgeddes]

Can you share the code you used? 

[EMSSS22]

Table.AddColumn(#"Replaced Value", "MaxIfZero", each if [CurrentMonthForActualsNo] = 0 then List.Max(#"Replaced Value"[CurrentMonthForActualsNo]) else [CurrentMonthForActualsNo])