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 🙂