Google Sheets query doesn't display all values

9
votes

I have a Google Sheet with the following query formula:

=QUERY('Contact Changes'!B1:T,"SELECT C,D,E,F,G,H,I,J,K,L,M,N,O,R,S WHERE T='RMT'")

The source sheet displays the following results when filtering column T by RMT. So, the Query formula should produce the same results.

enter image description here

However, column L does not display the field values when they are text values: enter image description here

If you look in J126 (city column), you can see the value of asdf which is displayed correctly in the results H2. But, the same value in L126 is not displayed in the results L2. The only way I can get a value to display in this column is by entering numbers.

I have:

  • checked the column data format
  • tried removing and re-adding the column in the Query
  • changing the column order
  • entering the same values in a different column (they display in the results)
  • manually re-entering the values in the same column (they don't display in the results)

Am I missing something obvious? What else can I try?

4
google-sheetsgoogle-sheets-query

4 Answers

12
votes

Google sheets query accepts only one data type per column, it detects automatically:

  • if Col contains more taxt values: returns text
  • if Col contains more numbers: returns numbers.

I solved the same problem with two workarounds:

  • use filter or combine filter with query
  • make extra column and convert all values into text by adiing any char, and then get rid of it.

https://support.google.com/docs/answer/3093343?hl=en

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.

10
votes

In addition to Max' contribution, you can also preprocess the data so that it's all converted to text and THEN wrap the query around this output. Something like

=QUERY(ArrayFormula(TO_TEXT('Contact Changes'!B1:T)),"SELECT Col3, Col4,    Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col17, Col18 WHERE Col19='RMT'")
5
votes

I had the same problem in the past and today I tried to just format the entire column to "plain text" and it worked perfectly.

No problems anymore and every value is displayed.

0
votes

An example of combining with Filter with Query for those who may find it helpful:

=QUERY(FILTER('Original Statement'!A:Z, 'Original Statement'!A:A = "Trades"), "select *")