Forum Discussion
Can't replace different null values in Power Query
I have a column that has blank cells, which register as null, and cells with the text string "null".
The standard course of action is to replace values with Value To Find = "null" and Replace With = "Unknown" (in my case). This replaces the null blanks, but not the cells containing the "null" text string. Right clicking on those cells and directly selecting replace values, prefilling the Value To Find with null, doesn't work either.
I've attached screenshots of what the cells look like before and after appling replace values. You can see (null) is gone, but the "null" string cells remain.
Any help would be greatly appreciated. Thank you.
BeforeAfter
A little bit of experimentation should get you there.
I suggest adding the Replace Values for the null value ( using the interface) and then editing the M code in the formula bar or Advanced editor to be something like:
Table.ReplaceValue(#"Changed Type","null"......
3 Replies
A little bit of experimentation should get you there.
I suggest adding the Replace Values for the null value ( using the interface) and then editing the M code in the formula bar or Advanced editor to be something like:
Table.ReplaceValue(#"Changed Type","null"......
Didn't even think of checking the advanced editor. You nailed it, needed to put quotes around null to distinguish it as a string as opposed to the usual null. Thank you!
Thank you HotChilli. Editing the M code from null to "null" did the trick!
