Forum Discussion
Helper IQuery 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]
I am still trying to find out a way to replace the value directly.
Best Regards!
Dale
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
Sure, you can do this by adding a Conditional column. You can find it in the Add Column ribbon.
Example:
jomikkWhat do I do if I have null and NaN values in the column?
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"
v-jiascu-msftMicrosoft Employee
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]