After creating a query and loading it to a sheet, I'm trying to use the count feature of Excel to do a quick count in a particular column by highlighting the whole column. However, it is counting blanks, which it doesn't ordinarily do. Is this a glitch or or does it count blanks when created via a query?
Thanks!
Hi Tykru,
When you look at those blanks in the Power Query editor to the cells contain the word null or are they blank? If they're blank then that means the cells aren't actually empty. They probably contain a non-printing character.
If so, in the Excel worksheet insert a CODE function that references one of those cells e.g. =CODE(A2), and see what code it returns. That is the character you need to 'Find and Replace' in the Power Query editor. I'd copy the contents of the cell to the clipboard so when you open the Power Query editor you can paste it in the 'Find and Replace' dialog box.
If that's not it, then please upload an Excel file containing a sample of your data.
Mynda
Mynda,
Thanks for the reply. They are blanks in the Power Query editor. I tried the CODE function and it returned #VALUE! but not sure why it's throwing the error. Attached is a sample of the data.
Thanks!
Hi Tykru,
Not sure why those cells are misbehaving, but if you find and replace them in Power Query with null (lower case), it fixes the problem.
Mynda
That did the trick, Mynda. Thank you!
