Google spreadsheets query() is replacing text with null for mostly numeric columns

Question

I have =query(importrange(...);"select * where Col1>' '") formula in my spreadsheet. Importrange() by itself works ok, loading all the cells from source spreadhseet exactly as they are. But Col7 contains few text cells, but mostly numbers, and when query() is applied -- numbers are kept as they are, but text is replaced with blank. I've tried adding options no_format at the end of the query, with no difference.

Here's the contents of Col7, first line gets replaced with blank:

• free
• 41,25
• 34,25
• 34,25
• 48,25
• 41,25

Answer 1

QUERY won't return columns with mixed data types by design:

In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.

The workaround will depend on how you want to use your data afterwards, and what compromises you would be willing to make. For example you could fairly easily convert the entire dataset into text strings, so that everything will be retained, but then all your numbers will be text strings as well.

If you needed to retain numbers as numbers, often the best bet will be to ImportRange the entire dataset somewhere in your spreadsheet (could be on a hidden sheet), and then use an alternative to QUERY on that (namely FILTER).

Answer 2

Change the format of the whole column to plain text. The numbers will still read as numbers, and the minority text values will retain their text values. Nothing will be counted as null.

Answer 3

You can use vlookup for the columns that need to be a mixed data type. Query the unique row identifier and then vlookup the rest. I often use the following formula:

=arrayformula(if(isblank(A:A), "", vlookup(A:A, search_range, col_index, FALSE)))