How to replace text with null in powerquery?

Question

I would like to replace "ValueToReplace" with null in powerquery. This does not work (returns error):

Table.ReplaceValue(#"Previous Step","ValueToReplace", null ,Replacer.ReplaceText,{"Col1"})

This works but it returns "" instead of null:

Table.ReplaceValue(#"Previous Step","ValueToReplace","",Replacer.ReplaceText,{"Col1"})

I have noticed that I cannot get null on TEXT type column. First I have to change it to Type ANY. Nevertheless it is strange. Because afterwards I can change column containing null to type TEXT. — Przemyslaw Remin

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-24T15:13:27

I think it's because null isn't a text value, so you need to use ReplaceValue instead of ReplaceText.

Table.ReplaceValue(#"Previous Step","ValueToReplace",null,Replacer.ReplaceValue,{"Col1"})