Google Sheets QUERY Missing Values: Repair Mixed Text and Number Columns

Reproduce mixed types deliberately

Put the booking fixture in A1:C6 in desktop Google Sheets. Set B2, B3 and B5 to numeric 101, 102 and 103; B4 and B6 are the text Studio-A and Studio-B. All C2:C6 values must be numbers. The companion types file describes the intended cell types and is a setup specification, not a second table to append to the bookings.

Put =ISNUMBER(B2) in D2 and fill it down through D6. The intended pattern is TRUE, TRUE, FALSE, TRUE, FALSE. An import that made every room plain text will not reproduce the bug, even if it looks identical. Leave the room cells as mixed types for the diagnostic; changing only their number format does not turn them into text.

Observe the raw query before changing it

Run the first formula in J1 on an otherwise empty area. With numeric values in the majority, the two Studio labels are the minority type. Inspect both those rows and their booking IDs. A select of every column can retain the row while showing a null room; a condition that requires a non-null room can also remove that row from the result.

The second formula makes that filtering consequence visible. B03 and B05 should be absent from this deliberately broken diagnostic report. The row loss comes from the additional where condition, so do not describe every mixed-type query as always deleting entire rows. Keep this test report separate from the repaired output.

For this exact fixture, J2:L6 contains five bookings and 15 hours, with blank room cells beside B03 and B05. N2:P4 contains only B01, B02 and B04, totaling six hours. The two raw expected CSVs encode the query nulls as empty fields, not the literal word null. They are diagnostic results to compare with, not repaired sources to import over A:C.

J1: =QUERY(A1:C6,"select A,B,C",1)
N1: =QUERY(A1:C6,"select A,B,C where B is not null",1)

Choose types according to meaning

A room identifier is a label, so text is appropriate even when one label contains digits only. Hours represent quantities, so converting that whole column to text would solve the wrong problem and complicate totals. Decide each column independently before building a helper table.

Leading zeroes require special care. If a source code 00101 was already imported as the number 101, converting it to text yields a representation of the stored number; it cannot infer how many zeroes the original had. Recover the original text or apply a documented fixed-width rule. Do not invent missing digits from the report.

Build a visible normalized table

Use E1:G6 for the repaired source. Put booking_id, room_text and hours in E1:G1. Enter the three formulas below in row 2 and fill them down to row 6. Keep the original A:C beside it so every transformed room can be inspected directly. This modest helper-table approach is easier to maintain than hiding every conversion in a single long query.

Select B2:B6 and use Format > Number > Custom number format with the pattern 0 before building the helpers. This makes the numeric rooms display as integers without padding. TO_TEXT retains existing formatting: a padded or currency format can become part of the resulting identifier. The fixture expects 101, 102 and 103, so compare F2:F6 with the original values before continuing. The format changes display, not the mixed-type contract; TO_TEXT is the conversion step.

E2: =A2
F2: =TO_TEXT(B2)
G2: =C2

Query the text identifiers with explicit headers

Clear the earlier diagnostic output in J1:L6 after preserving it if needed. Run the repaired query in J1. The source includes one header row, so the last argument is 1. A header guess is unnecessary when you know the shape of your own table. The query references E, F and G because those are the actual helper-sheet columns.

To select Studio-A, use the second formula in a different empty area. The room comparison is now a text comparison and the query literal is enclosed in single quotes inside the formula string. This example uses a fixed synthetic label; constructing a query string from arbitrary user input needs separate escaping and is outside this procedure.

J1: =QUERY(E1:G6,"select E,F,G where E is not null",1)
N1: =QUERY(E1:G6,"select E,F,G where F = 'Studio-A'",1)

Reconcile identifiers as well as totals

Compare the repaired report with query-preserved-expected.csv. You need five bookings, five nonblank room labels and 15 hours. A correct total by itself is insufficient: a select-only query could retain the hours while blanking the corresponding room. Conversely, a count can be correct while one label has been reformatted incorrectly.

Check Studio-A and Studio-B explicitly after adding numeric-looking rooms. Because every helper room is text, the report no longer depends on whether the latest import contains more numeric or more alphabetic room codes. Preserve the source booking ID through the transformation so a discrepancy can be traced to one input row.

Place the checks below in R2:R4, outside both query outputs. They should return 5, 0 and 15 after the repair. COUNTBLANK checks the five expected room positions rather than relying on a total alone. This fixture has no empty booking IDs, no empty room labels and no source errors. If a real booking has a missing room, keep it visible for review; do not substitute a guessed label merely to make the blank count pass.

R2: =COUNTA(J2:J6)
R3: =COUNTBLANK(K2:K6)
R4: =SUM(L2:L6)

Treat numeric data problems as a separate repair

If an amount column contains the text pending, turning all amounts into text is usually not an acceptable accounting rule. Separate valid numeric records from unresolved records and decide whether the report should stop, exclude with a visible count, or wait for correction. Replacing pending with zero silently changes the meaning of the data.

This guide addresses identifier preservation rather than grouped totals. Once types are consistent, the existing QUERY grouping guide can handle aggregation. Extend all helper formulas when adding rows, inspect any new format conventions and rerun the row-and-total reconciliation. The supplied validator checks the fixture contract; it does not reproduce the native QUERY engine or prove a particular import created the intended types.

Verified practice results

This synthetic example was calculated in Google Sheets on October 11, 2026. The raw select returned five bookings with the two Studio room labels blank; adding the nonnull condition returned only B01, B02 and B04, totaling six hours. The text-helper repair returned all five complete labels and 15 hours, and the Studio-A query returned B03 with four hours.

Changing B03 from four to six hours recalculated the repaired total to 17 and the Studio-A result to six. Restoring the source restored the baseline. Typed source cells, formula results and these input changes were checked in the native calculation engine; the repaired result was also inspected in the browser. This does not verify every CSV import route, custom-format convention or spreadsheet locale. Use the source-type and reconciliation checks on your own imported data.