Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

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? 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors