Reply
EMSSS22
Helper I
Helper I

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!

 

EMSSS22_0-1686061746871.png

 

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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]

vstephenmsft_0-1686197174751.png

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.           

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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]

vstephenmsft_0-1686197174751.png

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
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

 

EMSSS22_0-1686067477693.png

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

Can you share the code you used? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)