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
Anonymous
Not applicable

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

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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.

 


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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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.

Hi @Anonymous 

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

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.