cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jomikk Regular Visitor
Regular Visitor

Query editor replace values less than threshold

Hi, is it possible to replace values in a column that are less than e.g. 1 to null in Query editor?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Query editor replace values less than threshold

Hi @jomikk,

 

If you want to add a new column, you could try to add a "Conditional Column". 

 

if [Column1] < 1 or [Column1] = null or Number.IsNaN([Column1]) 
then 9999 
else [Column1]

Query editor replace values less than threshold.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I am still trying to find out a way to replace the value directly.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Query editor replace values less than threshold

Hi @jomikk,

 

If you don't have any "NaN" value, you can try this to replace the values directly. 

1. Right click "Column1", select "Replace Values";

2. Input any numbers, we only need this step to generate code for us;Query editor replace values less than threshold1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Open "Advanced Editor", replace the code in red square with the code below.

each [Column1],
each if [Column1] > -1 
and [Column1] < 1 
or [Column1] = null 
or Number.IsNaN([Column1]) 
then 9999 
else [Column1],

 

Query editor replace values less than threshold2.jpg

 

 

 

 

 

 

 

 

 

 

 

 4. Done, no new column needed. 

 

I wonder if someone can help with the value "NaN".

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
jordi-f
Advisor

Re: Query editor replace values less than threshold

Sure, you can do this by adding a Conditional column. You can find it in the Add Column ribbon. 

 

Example:

 

conditionalColumn.JPG

jomikk Regular Visitor
Regular Visitor

Re: Query editor replace values less than threshold

What do I do if I have null and NaN values in the column?

jordi-f
Advisor

Re: Query editor replace values less than threshold

Not sure if there is an interface for this, but you can use the advanced editor and add a try otherwise.

I added a null value and a text value that is converted to a number so results in an error

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLCMwyBrLySnNywBwTICdRKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientId = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientId", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "ValueNew", each try if [Value] <= 1 then null else [Value] otherwise null )
in
    #"Added Conditional Column"

conditionalColumn.JPG

Community Support Team
Community Support Team

Re: Query editor replace values less than threshold

Hi @jomikk,

 

If you want to add a new column, you could try to add a "Conditional Column". 

 

if [Column1] < 1 or [Column1] = null or Number.IsNaN([Column1]) 
then 9999 
else [Column1]

Query editor replace values less than threshold.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I am still trying to find out a way to replace the value directly.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jomikk Regular Visitor
Regular Visitor

Re: Query editor replace values less than threshold

The optimal solution would be to replace values directly, but this seems to be the best solution at the moment, thanks!

 

Edit: I was maybe too quick on the accept as solution button Smiley Happy What do I do if I have negative values? They are less than 1, but I want to keep them. I only want to edit numbers like 0.0000027 or -0.00028.

 

Is it sufficient to do something like

 

if [Column1] < 1 and [Column1] >-1 or [Column1] = null or Number.IsNaN([Column1]) 
then 9999 
else [Column1]
Community Support Team
Community Support Team

Re: Query editor replace values less than threshold

Hi @jomikk,

 

Yes, you are right! Don't change the order of the if statement.

 

One thing to correct to avoid misleadings to others: it's a "Custom Column", not a "Conditional Column". 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Query editor replace values less than threshold

Hi @jomikk,

 

If you don't have any "NaN" value, you can try this to replace the values directly. 

1. Right click "Column1", select "Replace Values";

2. Input any numbers, we only need this step to generate code for us;Query editor replace values less than threshold1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Open "Advanced Editor", replace the code in red square with the code below.

each [Column1],
each if [Column1] > -1 
and [Column1] < 1 
or [Column1] = null 
or Number.IsNaN([Column1]) 
then 9999 
else [Column1],

 

Query editor replace values less than threshold2.jpg

 

 

 

 

 

 

 

 

 

 

 

 4. Done, no new column needed. 

 

I wonder if someone can help with the value "NaN".

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jomikk Regular Visitor
Regular Visitor

Re: Query editor replace values less than threshold

Perfect!

Highlighted
divya_pwbi Frequent Visitor
Frequent Visitor

Re: Query editor replace values less than threshold

I had a similar scenario whre I need to replace negative values in the column to "Blank". I followed your steps exactly, The code was syntactically correct but my data was not reflecting the change.It was still showing me the negative values:. The  code is below:

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