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:

Image1

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

Image2

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

Image3

 

Image4

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:

Image6

Image9

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

Image7

Image8

TrapReplaceBlanks.xlsx

 

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

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz