Forum Discussion
Replacing negative values to Blank in Advanced editor.
I am trying to replace negative values with Blank (no values) for a particular column. I wrote the M query in Advanced editor.It is not throwing any error butt the vales are not replaced as well.What am I missing here? Below is my code:
#"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows1",each [Sorted Rows1],each if [Sorted Rows1] < 0 then "" else [Sorted Rows1],Replacer.ReplaceValue,{"Sorted Rows1"}),
Your syntax is wrong again. The final arguement has to be a list.
Try this, if if that doesn't work, rename the column "Value" then do the replacement using an exact copy of my formula above. Look at the full syntax of what I've typed in bold red above. Fields are in brackets [ ] and lists are in curly brackets { }. And Table.ReplaceValue expects fields and lists in the right place. You can get Power Query to accept your statement with no errors, but it will not do what you expect, and will likely do nothing.
#"Replaced Value2" = Table.ReplaceValue(#"Trimmed Text",each [Pumping Duration (min)],each if [Pumping Duration (min)] < 0 then "" else [Pumping Duration (min)],Replacer.ReplaceValue,{"Pumping Duration (min)"},
8 Replies
- edhans
Community Champion
This works for me:
= Table.ReplaceValue(#"Changed Type",each [Value], each if [Value] < 0 then "" else [Value] ,Replacer.ReplaceValue,{"Value"})Ensure your [Sorted Rows1] column is numeric. If it is text, the replacement will not happen.
- edhans
In your original post you had this:
#"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows1",each [Sorted Rows1],each if [Sorted Rows1] < 0 then "" else [Sorted Rows1],Replacer.ReplaceValue,{"Sorted Rows1"}),You have two things called "Sorted Rows1" which I didn't quite understand why, but I assumed you knew. That is probably wrong though.
For the part of the function that says:
Table.ReplaceValue(#"Sorted Rows1",
#"Sorted Rows1" is referring to the previous step, and is a table. Table.ReplaceValue accepts a table.
For the each [Sorted Rows1] part, that is asking for a field in the aformentioned table. You probably have no field called [Sorted Rows1] so it does nothing, which is why you have no change.
So in my function:
- = Table.ReplaceValue(#"Changed Type", refers to the previous step in my query. This is the table the Table.ReplaceValue will work on.
- each [Value], - refers to the old value I am looking for. This is what will be replaced. Essentially, everything will be replaced.
- each if [Value] < 0 then "" else [Value] , - this is the replacement value and uses if/then/else logic. If the number in my [Value] field is less than zero, replace with "", else replace it with itself. So it is changing all of them, but it changes positive values and zeros with themselves, so the end result is the same.
- Replacer.ReplaceValue, - Tells it I am replacing values, as opposed to text.
- {"Value"}) - The fields I am running the replace on fed to the Table.ReplaceValue function as a list.
I have more info here, including a sample Excel file so you can play with it.