How an assistant should answer questions that depend on tables and spreadsheets

Three kinds of table questions

• Look-up: one value with its context, such as the notice period for one supplier or the price of one part
• Computation: totals, averages, rankings, counts or filters across many rows
• Explanation: what a column means, why a figure changed or which rule produced a value, which usually lives in a document next to the table

Most assistants receive all three mixed together. The design question is how to recognise each kind and send it down a path that can answer it.

Why top-k retrieval cannot add up a column

Retrieval selects the few chunks most similar to the question. A total over three thousand order lines needs all of them, and no similarity ranking will return exactly the rows a filter would. The model then sums whatever it was given and presents the result with confidence. Missing rows look like smaller numbers, not like an error.

Pasting the whole sheet into the prompt avoids the selection problem for small tables, but arithmetic over many numbers in text is still unreliable. A database or a dataframe does that job exactly.

Making rows retrievable for look-ups

For look-ups, index each row as its own search document. Azure AI Search, for example, offers a delimitedText parsing mode that turns each line of a CSV file into a separate document; without it, the whole file becomes one document. Its SQL indexer maps a single table or view, row by row, and keeps decimal and money values as strings because converting them to floating point would lose precision.

Write each row as text that a person could read alone: column names next to values, units and currency spelled out, the sheet or table name and the date of the export. A row that says only 48213, 3, 0.15, PL is unfindable by meaning and ambiguous to the model.

A query path for calculations

For computation, let the model produce a query against a known schema, run it in the database, and answer from the result. LlamaIndex documents this text-to-SQL pattern and advises naming the tables to query, because loading every schema can overflow the context; for many tables it retrieves the relevant schema first. The same guide warns that executing arbitrary SQL is risky and suggests restricted roles, read-only databases and sandboxing.

In practice we add more limits: a database role that sees only approved views, row-level filters for the user asking, timeouts and row caps. For the questions people ask every week, predefined parameterised queries exposed as tools are easier to test than free-form SQL. Either way, the answer should show the query or report used, the result, the row count and the data timestamp, so a reader can check it.

Routing one export, question by question

The example below is invented. One sales export feeds both paths: rows are indexed for look-ups and loaded into a table for queries.

Source: monthly sales export, one row per order line (hypothetical)

Question                                           Route        Why
Discount on line 3 of order 48213?                 retrieval    one line row, found by
                                                                order ID + line number
Which region had the highest revenue in Q2?        query        aggregates every Q2 row
How many Polish orders totalled over 10,000 EUR?   query        sum line amounts per order,
                                                                then count distinct orders
                                                                above the threshold
Why is the Q2 figure lower than in the board pack? explain      needs the definitions note,
                                                                then a query to reconcile
Answer contract for "query":
  show the query or named report used, the result,
  the data timestamp and the row count it covered

Test the router as carefully as either path. A computation question sent to retrieval returns a plausible wrong number, which is harder to notice than an error message.

Spreadsheets that behave like documents

Many business spreadsheets are not tables. They contain merged headers, several tables on one sheet, notes typed into cells and figures that exist only after formulas are calculated. Several copies circulate by email. Before connecting such a file, decide which copy is authoritative and whether the data should first move into a proper table with defined columns. The reconciliation work is often worth more than the assistant.