cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
divya_pwbi Frequent Visitor
Frequent Visitor

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"}),

1 ACCEPTED SOLUTION

Accepted Solutions
edhans New Contributor
New Contributor

Re: Replacing negative values to Blank in Advanced editor.

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 8
edhans New Contributor
New Contributor

Re: Replacing negative values to Blank in Advanced editor.

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.

divya_pwbi Frequent Visitor
Frequent Visitor

Re: Replacing negative values to Blank in Advanced editor.

Sorry,I am missing something.. What does the first parameter (SortedRows1) even signify?

And what does "Changed Type" mean in your query?

edhans New Contributor
New Contributor

Re: Replacing negative values to Blank in Advanced editor.

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.

divya_pwbi Frequent Visitor
Frequent Visitor

Re: Replacing negative values to Blank in Advanced editor.

I changed the query to this.

 #"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows1",each {"Pumping Duration (min)"},each if {"Pumping Duration (min)"} < 0 then "" else {"Pumping Duration (min)"},Replacer.ReplaceValue,{"Pumping Duration (min)"}),

 

 

This passes syntactically but the negative values are still not replaced . Not sure what I am missing.

Note:  I wanted to work on the already created column  and not create a new conditional column to achieve this logic. and that is why trying the above approach.

 

edhans New Contributor
New Contributor

Re: Replacing negative values to Blank in Advanced editor.


Note:  I wanted to work on the already created column  and not create a new conditional column to achieve this logic. and that is why trying the above approach.

 


Did you see the Excel file I included in my link? I i didn't create a new conditional column. It acted on the existing column.  And your syntax may not produce an error, but your are passing lists to the function, not fields. It won't work.

 

image.png

The full M code is:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"Value", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Value], each if [Value] < 0 then "" else [Value] ,Replacer.ReplaceValue,{"Value"})
in
    #"Replaced Value"

You cannot arbitrarly substitute [ ] with { }. Those mean very different things to Power Query.

 

divya_pwbi Frequent Visitor
Frequent Visitor

Re: Replacing negative values to Blank in Advanced editor.

So if I chge the { to [,it gives me the error "Invalid identifier" for the   [Pumping Duration (min)],  field.

 

#"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)],

 

 

Note: "Trimmed Text" is from the previous step.

edhans New Contributor
New Contributor

Re: Replacing negative values to Blank in Advanced editor.

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)"},
Community Support Team
Community Support Team

Re: Replacing negative values to Blank in Advanced editor.

Hi @divya_pwbi 

Is this problem sloved? 
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
 
Best Regards
Maggie