Prevent blank column and line in the google query result

google-sheets google-query-language
jakub picture jakub · Feb 18, 2015 · Viewed 11.8k times · Source

Doing the following query = QUERY(A2:C, "select B, avg(C) group by B pivot A") returns a correct summary of my source data. However, there is blank line and a blank column in the result (see example). How to get rid of them? I can deal with the blank line using offset 1 but what about the column?

Also, using A2:C11 to specify the data, there are no blank lines or columns. But then when data gets added at the end of the source range, it doesn't get included in the query result (obviously). How to prevent the blank line and column?

Answer

JPV picture JPV · Feb 18, 2015

You probably have some blank rows in your source range. Try:

= QUERY(A2:C, "select B, avg(C) where C is not null group by B pivot A")

Related questions

Google SpreadSheet Query: Can I remove column header?

I'm doing this query at my google spreadsheet: =QUERY(H4:L35;"select sum(L) where H='First Week'"; -1) But it returns a little table with "sum" as header and result below it. What I want is just the result! …

Read More
Google spreadsheet "=QUERY" join() equivalent function?

This question is concerning joining two databases in Google spreadsheet using =QUERY function I have a table like so in range A1:C3 a d g b e h c f i I have another table c j m a …

Read More
Google Spreadsheets QUERY() to combine multiple sheets, same workbook

Goal: I'm using QUERY() in gSheets to combine data from multiple sheets in the same workbook. The data is an extract from GA broken down into small segments to prevent sampling. This means it has all the same fields, I'm …

Read More