Replace Values not working

[PowerAppsUser]

Hi,

I am trying to replace the value of a cell with another value in Power Query by right clicking that cell and using 'Replace Value', but it doesnt seem to work. I want to change "null" to "Electricity". The function bar is showing the following function :

= Table.ReplaceValue(#"Trimmed Text",null,"Electricity",Replacer.ReplaceValue,{"Sector"})

But the cell value is not changing. Please let me know if anyone knows how to fix it.

Thanks!

[PowerAppsUser]

Thank you Samarth and Alexis for your help, I tried out both of your suggesions but somehow they dint work. So I tried another work around that finally worked - I changed the case of the column to UPPERCASE and made null as NULL, and then when I tried to change it with 'Replace Value', I was able to change it to Electricity. Then I went back and removed the UPPERCASE.

[AlexisOlson · Accepted solution]

Ah, it sounds like you had the text "null" rather than actual nulls. I didn't pick that up upon the first read.

In that case, you can skip the uppercase transformation and just use "null" instead of null in your original M code (insert the quotes into the formula):

= Table.ReplaceValue(#"Trimmed Text","null","Electricity",Replacer.ReplaceValue,{"Sector"})

[PowerAppsUser]

Your right! I tried your code with "null" and it worked! But I still wonder why the 'Replace Values' dint work.

Thank you, Alexis!

[AlexisOlson]

It's a bit of an odd situation. If "null" gets interpreted as text (as in your case) then what would you put into the box to indicate an actual null value (where there's nothing in the cell)? It appears that Microsoft decided to interpret someone typing in null as the latter rather than the former (probably because that situation is a bit more common). Both interpretations will get it wrong some of the time.

[AlexisOlson]

It might be an empty string "" instead of a null. See if this works:

= Table.ReplaceValue(#"Trimmed Text","","Electricity",Replacer.ReplaceValue,{"Sector"})

Or as @Samarth_18 suggests, it might be a space or some other type of whitespace.

[Samarth_18]

Hi @PowerAppsUser ,

Try with putting single space inside the "Value to find" section.

Thanks,

Samarth