Forum Discussion

jomikk's avatar
jomikk
Helper I
9 years ago
Solved

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?

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

     

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

     

    Example:

     

    • jomikk's avatar
      jomikk
      Helper I
      9 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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"