How to Replace Negative Values in Power Query with 0

[Shelley]

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!

[DomLar]

You could also do a new conditional column - IE if greater than 0 put column data, otherwise put 0 -  

[CNENFRNL · Accepted solution]

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

[osama_ayoub]

@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

[Syndicate_Admin]

@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

[Anonymous]

This also replaces nulls with 0

[Anonymous]

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

[Anonymous]

Thank you so much, this worked.

[Shelley]

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

[AlB]

@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]

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]

@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!