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})}})
CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity 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's avatar
    Shelley
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

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

  • osama_ayoub's avatar
    osama_ayoub
    Icon for Helper III rankHelper 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's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you so much, this worked.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This also replaces nulls with 0

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

    @CNENFRNL How can I use a list with the names of the columns where I want to make that replacement? That is, I want to perform this replacement in more than one column, and I have the data of the names of the columns in a list.

    Grcs