Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shelley
Continued Contributor
Continued Contributor

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

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

11 REPLIES 11
DomLar
New Member

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

 

DomLar_0-1703153568620.png

 

CNENFRNL
Community Champion
Community Champion

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@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

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

maschiav_0-1677517169352.png

Grcs

Anonymous
Not applicable

This also replaces nulls with 0

Anonymous
Not applicable

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

Anonymous
Not applicable

Thank you so much, this worked.

Shelley
Continued Contributor
Continued Contributor

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

AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

 

AlB
Super User
Super User

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 

SU18_powerbi_badge

 

Shelley
Continued Contributor
Continued Contributor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.