Forum Discussion

EMSSS22's avatar
EMSSS22
Helper I
3 years ago
Solved

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 ha...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
jgeddes
Super User
3 years ago

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's avatar
    EMSSS22
    Helper I
    3 years ago

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

     

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

    • jgeddes's avatar
      jgeddes
      Super User
      3 years ago

      Can you share the code you used? 

      • EMSSS22's avatar
        EMSSS22
        Helper I
        3 years ago

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