Forum Discussion
dealing with blank values when consuming xlsx?
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?)
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
1 Reply
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