Restructuring spreadsheet data into tidy, machine-readable tables

Most SMB knowledge lives in Excel files that were designed to be looked at: merged header cells, one block per month, subtotals in the middle, "red = overdue", a remark in column K. Neither a BI tool nor an AI assistant can read this reliably. The fix is mostly deterministic reshaping (Power Query, a small script), with an LLM used as a helper that writes and explains the transformation code. The result is a clean table that can be refreshed, queried and used for AI.

Problem & value

• Today: a controller or office manager rebuilds the same report by copy and paste every month. Formulas break when someone inserts a column. Questions to a chat assistant on such files return wrong totals because the model cannot tell headers from data or subtotals from rows.
• Value: one refreshable pipeline replaces repeated manual work, numbers become comparable across months, and the same table feeds dashboards, SQL queries and AI tools. This is typically the cheapest data-readiness step with the most visible result.
• Principle: "tidy data" means each variable is a column, each observation a row and each type of observational unit a table, close to normalised database design [1]. Savings depend on how many files you rebuild and how often.

Variants

• Cross-tab to long format: months or products as columns become one row per month and product (unpivot) [2].
• Report-style sheets: several blocks, titles and subtotals; extract blocks, drop totals, recompute them.
• Encoded meaning: colour or comments become explicit columns ("status", "remark").

How it works

1. spreadsheets (file share / SharePoint / Drive)
2. profile layout (sheets, header rows, merged cells, colours)
3. LLM proposes transformation (Power Query M / Python) from headers + 5 sample rows
4. human reviews and runs it; totals reconciled with the original
5. tidy table (Parquet/CSV/SQLite/Postgres) + data dictionary + reject log
6. scheduled refresh (Power Automate / n8n)
7. BI, search, AI assistant

• Deterministic core: the transformation itself is code, not a model guess. Power Query records every step and re-runs it on refresh; the "Unpivot other columns" option also catches columns added later [2]. Python (pandas, openpyxl) or DuckDB, which reads .xlsx ranges directly, cover what Power Query cannot [3].
• LLM role: drafts the M or Python code, suggests column names and types, flags suspicious cells. It does not need your full data, only structure and a few anonymised rows.
• Human in the loop: a domain owner confirms the column meaning and checks that sums match the source before the table goes live.

Data preparation

Which data: the spreadsheets themselves, plus the knowledge in people's heads (what a colour or abbreviation means).

Where it lives: file shares, SharePoint/OneDrive, Google Drive, mail attachments, ERP exports (DATEV, SAP, Business Central exports as CSV/Excel).

Typical gaps: merged cells, dates as text, decimal comma vs. point, mixed units, the same customer spelled five ways, hidden sheets, macros, links to other workbooks, personal data in unexpected columns.

Preparation steps: 1. Pick 3 to 5 files that matter most (use the inventory from PREP-003 and PREP-001). 2. Write down the target schema: columns, types, allowed values, key (PREP-012 for definitions). 3. Build the transformation on one file, then test it on three older versions of the same file. 4. Convert colour/comment information into explicit columns. 5. Reconcile totals against the original and log rejected rows. 6. Store the output centrally and set access rights (PREP-023); pseudonymise personal columns if the data goes to external AI (PREP-022).

Tools: Excel Power Query (included in Microsoft 365 and Excel 2016+), Power BI Desktop, LibreOffice, Python with pandas/openpyxl, DuckDB, OpenRefine, SQLite or PostgreSQL.

Rough share of effort: medium; often the largest single step in spreadsheet-based analytics or RAG projects. Duplicate and master data clean-up is a separate task (DATA-003). See GUIDE-003.

Implementation paths

Which path for whom

• SMB - Path C: the work is mostly open-source or already-licensed tooling (Power Query, Python, DuckDB, n8n) that runs on a normal PC or HW1 server. Spreadsheets often contain customer, HR or pricing data, so a local model such as Qwen or Llama via Ollama writing the code keeps data in-house, and the same stack serves PREP-003, PREP-010 and DATA-003 (Baukasten effect).
• Enterprise - Path B: many files, many owners; use a cloud code model through the EU region of an approved platform inside a governed data pipeline (dbt, Fabric, Databricks).
• Developer - Path C: scripted pipelines with pandas/Polars/DuckDB and local code models, versioned in Git.

Path A - Buy (off-the-shelf)

Copilot in Excel or Gemini in Google Sheets can clean a sheet interactively (check licence, data location and AVV). Data-prep products: Power BI dataflows, Alteryx, Tableau Prep, Trifacta-style tools. Good for one-off cleaning, weak for repeatable pipelines and governance; per-user cost and seat licences add up. Not recommended as the main route here.

Path B - Build on cloud APIs

Send header rows and a few sample rows (never full personal data) to an EU-hosted model: Mistral (France, EU), Azure OpenAI in EU regions, Claude via EU-region cloud offerings, or STACKIT / IONOS AI Model Hub (German cloud). Have it return Power Query M or Python. Orchestrate with Power Automate or n8n. The effort is mainly review.

Path C - Self-host (open source)

• Transformation: Power Query (desktop), Python + pandas/openpyxl, DuckDB, OpenRefine.
• Model for code help: a small coder model in Ollama (e.g. Qwen coder family) on HW1/HW2; a laptop with 16 GB RAM is enough for simple unpivot scripts.
• Orchestration and storage: self-hosted n8n, files in Nextcloud, results in SQLite/PostgreSQL; Paperless-ngx for document-type sources.
• Effort: 1 to 3 days for the first file type, then hours per further similar layout.

Path D - Custom training

Not needed; rules plus a general model suffice.

State of the art (2026)

• LLMs are good at writing unpivot, merge and date-parsing code from a description; they are less reliable when asked to transform the data directly, because they can silently drop or invent rows. Always generate code, then verify.
• DuckDB and Parquet became the default light-weight "small database" for exactly this step [3].
• Limits: meaning in colours and comments needs a human; formulas and macros hide business logic that must be re-implemented; files with free-text blocks need separate handling (PREP-014).

Shared resources & combinations

• Same stack as PREP-003, PREP-010 and AUTO-006. Output feeds PREP-012, DATA-003, BI dashboards and text-to-SQL assistants; source exports come from PREP-005.

Effort, cost & KPIs

• Path A: hours per file, licence cost per seat.
• Path B: 2 to 5 days for a first pipeline, small API cost.
• Path C: 1 to 3 days for the first layout, then hours; no running cost beyond existing hardware.
• KPIs: hours per month spent rebuilding reports, share of key reports generated from tidy tables, reconciliation differences (target 0), rejected-row rate, time to answer a new business question.

People & change

Involve early: the department lead and the people who own each spreadsheet (they know what the colours mean); IT for storage and refresh; the data owners (PREP-004). Spreadsheet authors may fear losing their "own" tool, so let them co-design the schema and keep a familiar Excel view on top of the clean data.

Works council: depends. The transformation itself does not monitor staff, but spreadsheets often contain per-person performance, absence or time data, and file-access and refresh logs identify users; check §87(1) no. 6 BetrVG and inform under §90(1) no. 3 when AI supports the work process (GUIDE-004). Public bodies involve the Personalrat.

Data protection: involve the DPO if employee or customer columns are in scope; a DPIA is rarely needed for pure restructuring, but an AVV (Art. 28 GDPR) is required with any cloud model provider (GUIDE-005, GUIDE-010).

AI literacy: Art. 4 AI Act requires measures that support staff's AI literacy; a one-hour workshop on tidy data and checking LLM-written code is enough (GUIDE-006).

Pilot and humans in control: start with one monthly report and one owner, run old and new in parallel for two cycles (GUIDE-008, GUIDE-009). A human signs off the schema and the reconciliation.

Risks, regulation & compliance

• EU AI Act: minimal risk; AI literacy (Art. 4) applies.
• GDPR: personal data may sit in columns nobody listed; minimise what the model sees, use EU hosting or local models (PREP-025).
• Bookkeeping: for tax-relevant data (GoBD) keep the original file; the tidy table is a derived copy.
• Risks: silent row loss, wrong unit conversions, outdated copies. Mitigate with reconciliation, reject logs, one central source and versioned scripts.

Getting started

1. Choose one monthly report that costs the most manual time; copy three past versions.
2. Define the target columns and types in a half-page data dictionary with the report owner.
3. Build the Power Query steps (or let a local model draft the script) and reconcile totals.
4. Schedule the refresh and publish the tidy table in a shared, access-controlled location.
5. Repeat for the next four reports; then add them to the inventory and glossary (PREP-001, PREP-012).

Sources

1. Wickham, H. (2014). Tidy Data. Journal of Statistical Software 59(10) - https://doi.org/10.18637/jss.v059.i10
2. Microsoft Support - Unpivot columns (Power Query) - https://support.microsoft.com/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098
3. DuckDB documentation - Excel import (read_xlsx) - https://duckdb.org/docs/stable/guides/file_formats/excel_import
4. Regulation (EU) 2024/1689 (AI Act) - https://eur-lex.europa.eu/eli/reg/2024/1689/oj