Query editor replacing column values based on another column values

[Anonymous]

Hi,

I have Rain, QA columns in the table.

Rain	QA
1.5	null
0.5	10
0.7	null
0	0.5

I am trying to change the Rain column values to "0" if the QA column contains any "nonnull" values.

 I am looking for below changes in the query editor

If [QA]!=null then [RAIN]=0 else [RAIN]

Expected Result:

Rain	QA
1.5	null
0	10
0.7	null
0	0.5

I wrote below code in Advanced editor, which is not giving me the result that I expected.

#"Replaced Value" = Table.ReplaceValue(#"5 min Rain fall",each [RAIN], each if [QA]<>"null" then 0 else [RAIN],Replacer.ReplaceValue,{"RAIN"})

Please let me know how to achieve this.

Thank you!

Regards,

Aswini

[amitchandak]

@Anonymous , Right on the column in data transformation mode. Choose replace

from : null

to : 0

null is take as null or blank value

[parry2k]

@Anonymous as @MFelix  suggested you can add a new custom column with the logic and remove the old rain column or you can update replace function like below and that will work as well.

= Table.ReplaceValue(#"Changed Type",each [Rain],each if [QA]=null then 0 else [Rain],Replacer.ReplaceValue,{"Rain"})

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[MFelix]

Hi @Anonymous,

Your first code should work, except for the exclamation point.

Add a new column with the following code

If [QA]=null then [RAIN]=0 else [RAIN]

Be aware that you are creating a new column so you may need to delete the rain Column and rename this new one if you only want a rain Column on your model. 

If ir returns an error can you please tell what it is.