Forum Discussion

Shelley's avatar
Shelley
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

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 ...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    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})}})
AlB's avatar
AlB
Icon for Community Champion rankCommunity 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