Forum Discussion
Replace Values not working
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!
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"})
6 Replies
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.
Hi PowerAppsUser ,
Try with putting single space inside the "Value to find" section.
Thanks,
Samarth
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!
