Trap in Replacement function in Power Query/M/PowerBI

Recently Power Query’s replacement function greeted me with some unexpected results: I applied the same steps but sometimes replacements of blanks with nothing did what it should and sometimes not.

What was going on?:

Starting with a table with 2 columns like this:


The results came out as expected, blanks were removed by nothing, so all gaps closed:


Then I added the Values-column like this and applied the same steps as above:




Not nice. But having a close look at the M-code that had been generated solved this mystery: The first operation created a Replace.ReplaceText-operation, while the second one was translated to Replace.ReplaceValue. This is due to the automatic detection of column types. In the second try the columns haven’t only been text-types, but also numbers or in this case: A column that has been identified as “any”. That dominates the selection of the replacer-function.

To solve a problem like this you should change the format of your “number-columns” to text before doing the replacements:



Then the Replacer.ReplaceText will do it’s job. Just switch to number-format afterwards:





Just a quick note on a trap to avoid – stay queryious 🙂

Leave a Reply