dealing with blank values when consuming xlsx?

[Anonymous]

I am in 'power query editor'.

And I tried replacing a blank value with nulls... it was impossible, I tried one space, 2 spaces, and I couldn't replace it.

Then I realized that I can copy paste from there, i copy pasted the blank and I believe its 5 spaces, I used that and it worked.

Is there a less cumbersome way of solving this?  (I am consuming from an xlsx file, we does it come with blank cells as 5 or 6 spaces?)

[themistoklis · Accepted solution]

@Anonymous 

Probably these spaces already exist in the source files.

Best way to deal with this is remove all spaces from this column (if it doesnt affect existing values) and the replace blanks with nulls.

Go to Power Query --> Right click on the column --> Replace Values

Value to Find: press spacebar once

Replace With: Leave it as it. Dont press any button

Click ok and check the reults.

If everything looks ok ten replace blanks with nulls