Replacing blank values in Power Query

[Anonymous]

Hi All, 

I have a bit of a weird issue that I can't get my head around. 

I have a Region field in a table that has some blank values. I am replacing these blank values with the text "Undefined" using Replace Values in Power Query. This seems to work without any issues in Power Query. However, when I load the data, this change is not applied being for some reason. I still see blanks where I should be seeing undefined. 

Any thoughts on why this might be happening? 

[BA_Pete]

Hi @Anonymous ,

As far as Power Query is concerned, there's at least three items that would appear as "blanks":

null, "", and " ".

The quickest way to identify any/all of them that might be causing you trouble would be to:

-1- Filter your column, selecting any items in the filter list that appear as blank (or null if required).

-2- Right click on one of the blank cells and select 'Replace Values' and replace with your chosen substitute.

-3- Right click on any other blank cell that remains, repeat step -2-.

-4- Repeat steps -2- & -3- until no blanks remain.

If you check the code generated by each replace step, you'll probably find that what you see as blank values are actually distinct values, like "", "   ", "        " etc.

Pete