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
Helper IWhat do I do if I have null and NaN values in the column?
Microsoft EmployeeHi 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
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!
- 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