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!

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})}})

How to Replace Negative Values in Power Query with 0

11 Replies

CNENFRNL
Community Champion
5 years ago
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})}})
- Shelley
  Post Prodigy
  5 years ago
  Thank you! This appears to have worked on all four columns to which I applied it.
- osama_ayoub
  Helper III
  3 years ago
  CNENFRNL
  can I use this function but not only in one columns but in all my table columns in a dynamic way without mention all the column names of the table ?
  thanks in advance
- Anonymous
  Not applicable
  4 years ago
  Thank you so much, this worked.
AlB
Community Champion
5 years ago
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
AlB
Community Champion
5 years ago
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
- Shelley
  Post Prodigy
  5 years ago
  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!
DomLar
New Member
2 years ago
You could also do a new conditional column - IE if greater than 0 put column data, otherwise put 0 -