Forum Discussion
Replace Values not working
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"})
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.
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"})
Your right! I tried your code with "null" and it worked! But I still wonder why the 'Replace Values' dint work.
Thank you, Alexis!
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.