Return only filled cells in Google Sheets QUERY

Question

I have this formula in my sheet:

=query('Character Analysis'!$H62:$L83,"select H,I,J,K,L where H is not null order by L DESC",0)

Only the first two of the source rows have data in them, but on the sheet with the query formula it appears to be pulling all the rows in the range, even the blank ones. If I type something in the 3rd row on the query formula sheet, it gives me an error saying "Array result was not expanded because it would overwrite data in ________." But it doesn't need that room because there are only two rows of data in the query result.

I tried adding the "is not null" language in hopes that it would limit the returned result to only filled cells, but it's not working.

How can I tell my query to only pull data from filled cells in the source range?

Answer 1

I figured out a workaround, at least to the degree that it works for me. It's not a true answer as I'd still like to know why the "is not null" language isn't working, but this is giving me exactly what I need: You can just limit the number of returned rows to the number of source rows with data by counting them:

=query('Character Analysis'!$H62:$L83,"select H,I,J,K,L order by L DESC limit "&COUNT('Character Analysis'!$L62:$L83)&"",0)

Answer 2

According to source

You can:

Using a ‘where’ clause to eliminate blank rows

If a named range is defined using entire column (ie including blank rows) you may find these blanks appear in the query result (which, depending on the sort order, could be at the top!). To stop these appearing include a where clause using this syntax (assuming column A):

"...where A <> ' ' " (for text fields)

"...where A <>0" (for numeric fields)

This means ‘where values in column a are not zero-length text.