cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shelley
Continued Contributor
Continued Contributor

How to Replace Negative Values in Power Query with 0

Hi All, This is probably easy, but I can't seem to figure it out. I have some columns in the query editor that I would like to replace all the negative values with 0. I tried this, and it looks like it works when I am on the current step, but then when I go to later steps, it shows negative numbers still exist in this column. Rather than looking for less than 0, I was trying to check the sign on the number to see if it was negative like this:

= Table.ReplaceValue(#"Changed Type4", each [#"Base List Price (ZP00)"], each if Number.Sign([#"Base List Price (ZP00)"]) = -1 then 0 else [#"Base List Price (ZP00)"], Replacer.ReplaceValue, {"Base List Price (ZP00)"}),

 

What am I missing? Any help is greatly appreciated. Thanks!

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

@Shelley , if you're looking to replace negative numbers in some certain column, you might want to try

= Table.TransformColumns(#"Changed Type4", {{"Base List Price (ZP00)", each List.Max({_, 0})}})

View solution in original post

8 REPLIES 8
CNENFRNL
Community Champion
Community Champion

@Shelley , if you're looking to replace negative numbers in some certain column, you might want to try

= Table.TransformColumns(#"Changed Type4", {{"Base List Price (ZP00)", each List.Max({_, 0})}})

View solution in original post

This also replaces nulls with 0

To avoid that first replace nulls with some dummy data like ABCDE then replace 0 with nulls then replace back ABCDE with nulls.

Thank you so much, this worked.

Shelley
Continued Contributor
Continued Contributor

Thank you! This appears to have worked on all four columns to which I applied it. 

AlB
Super User
Super User

@Shelley 

Simple. Taking the max between a number and zero will turn negative numbers into zero and leave positive numbers untouched. 

It works on my end. I'd need to see the real data (or a version with dummy data that reproduces the problem) to be able to see what is going on. I don't see much of a problem with your code either, by the way

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

AlB
Super User
Super User

Hi @Shelley 

Try this

Table.ReplaceValue(#"Changed Type4", each [#"Base List Price (ZP00)"], each List.Max({[#"Base List Price (ZP00)"],0}), Replacer.ReplaceValue, {"Base List Price (ZP00)"})

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

Shelley
Continued Contributor
Continued Contributor

@AlB Thank you for the suggestion, but it doesn't seem to work either. If I'm trying to replace all values that are less than zero, then why the List.Max command? This command seems to be for finding the maximum item in the list, so I don't understand how this would help replace values that are less than zero. Thanks again, and have a good weekend!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.