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, Aug 24 '18 at 11:25

score 8 · Accepted Answer · answered Aug 24 '18 at 15:13

8

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"})

answered Aug 24 '18 at 15:13

Alexis Olson

1 Answers1