Query editor replace values less than threshold

[jomikk]

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

[v-jiascu-msft · Accepted solution]

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]

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

Best Regards!

Dale

[Anonymous]

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

Example:

[jomikk]

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

[v-jiascu-msft · Accepted solution]

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]

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

Best Regards!

Dale

[jomikk]

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 🙂 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]

[v-jiascu-msft · Accepted solution]

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;

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],

 4. Done, no new column needed. 

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

Best Regards!

Dale

[jomikk]

Perfect!

[Anonymous]

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

[v-jiascu-msft]

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

[Anonymous]

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"