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

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's avatar
    Shelley
    Icon for Post Prodigy rankPost 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!