Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Replacing blank values in Power Query

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? 

 

 

Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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