Every month someone downloads the same export from the ERP, shop or bank, deletes header rows, fixes date and number formats, joins a price list and pastes the result into a report. Power Query (Excel, Power BI) and Google Sheets imports record these steps once and replay them with one click. AI removes the hardest part for non-programmers: writing and understanding the M code or formula behind the steps. Typical benefit: a recurring chore becomes a refresh button.
Problem & value
- Today: a controller or clerk repeats the same clean-up weekly or monthly. The steps live in one head or a fragile macro; when the export layout changes, the report breaks and nobody else can fix it.
- AI value: describe the target ("one row per invoice line, decimal comma converted, customer group joined from the second file") and get working steps; paste an inherited query and get an explanation; paste an error and get a fix. Knowing the M language is no longer a precondition.
- Scope boundary: one-off tidying is AUTO-006, conversational BI over databases is DATA-001, engineered ETL is CODE-022.
- Benefit figures: we found no independent study with reliable time savings for this task; measure minutes per refresh before and after.
Variants
- Excel / Power BI: Power Query editor with Copilot or a chat assistant writing M code.
- Google Workspace: Sheets IMPORTDATA, IMPORTRANGE and QUERY, Apps Script for scheduled imports, Gemini for help [4].
- DATEV and bank exports: semicolon CSVs, German number format, ISO-8859-1 encoding; a classic first target.
- Private use: bank CSVs and tax-year summaries.
How it works
export file (CSV/XLSX) in a fixed folder
-> Power Query connects to the folder
-> AI proposes steps (remove rows, typed columns with locale, split, unpivot, merge lookup)
-> user checks the preview after every step
-> query saved, loaded to a table or data model
-> next month: replace file, click "Refresh All"
- AI role: a chat model turns the description into M code that the user pastes into the Advanced Editor; Copilot can apply some steps directly [3]. Send only column headers and a few pseudonymised rows.
- Deterministic core: the saved query runs without AI on every refresh. AI is used once to build the step, so results are reproducible and auditable.
- Human-in-the-loop: the user compares row count and total amount before and after.
Data preparation
Which data: the recurring exports (ERP, DATEV, shop, bank, CRM) and lookup tables (customer groups, article categories, cost-centre mapping). Where: ERP reports, shop back ends, online banking, SharePoint/OneDrive/Nextcloud folders, mail attachments.
Typical gaps: changing file names and locations, columns renamed after vendor updates, decimal commas and dates read in the wrong locale, total rows inside the data, lookup lists in private copies, personal data in samples.
Steps: 1. One fixed folder per export with a naming rule and team-only access (about an hour). 2. Where possible, create a saved or scheduled export with fixed columns instead of manual downloads. 3. Take 20-50 sample rows; remove names, IBANs and e-mail addresses before using any AI tool. 4. Keep lookup tables in one shared workbook with a named owner. 5. Write down the expected result (row count, total, spot values) as acceptance test (GUIDE-003).
Tools: Excel, Power BI Desktop, Google Sheets, Nextcloud or SharePoint. Effort share: low, mostly agreeing on the folder and fixing the export.
Implementation paths
Which path for whom
- SMB - Path A: Power Query is already in Excel; AI help comes from the existing Microsoft 365 or Google Workspace subscription. The process is standard and needs no infrastructure.
- Enterprise - Path B: governed dataflows and a shared AI platform favour approved model APIs with central review of shared queries.
- Developer - Path C: local code models plus Python (pandas, Polars, DuckDB) give versioned import scripts.
- Private - Path C: LM Studio or Ollama explain and write M code or formulas for bank CSVs without cloud.
Path A - Buy (recommended for SMBs)
- Microsoft 365: Power Query (Data > Get Data) is included in desktop Excel [1]; Copilot or a Copilot chat writes or explains M code. Microsoft documents the EU Data Boundary (EU-hosted option) [2].
- Google Workspace: Sheets with Gemini and QUERY, Apps Script for schedules; data-region settings exist.
- Chat assistants: Mistral Le Chat (EU provider, France), ChatGPT, Claude, Gemini under business plans with an AVV.
- Choose A when: exports are standard, volumes fit in Excel, and no unprotected personal data leaves the company.
Path B - Build on cloud APIs
- Models: Azure OpenAI in EU regions or the Mistral API (EU).
- Orchestration: Power Automate or n8n picks up the file, a model proposes a mapping once, a person approves it.
- Use when: many departments need governed imports that integrate with the own data platform.
Path C - Self-host (open source)
- Models: small open-weight code models (Qwen-Coder or Codestral-class, 7-30B) via Ollama [6] or LM Studio.
- Stack: the editor stays Excel; the model only produces M code, SQL or Python snippets.
- Hardware: HW1 suffices for snippets on a laptop; HW2 gives better code. Install takes an hour, no daily upkeep.
- Choose C when: exports contain health, HR or trade-secret data, or a developer wants versioned scripts.
Path D - Custom training
Not worth it; prompting with sample rows is enough.
State of the art (2026)
- Code-capable chat models write correct M for typical steps (typed columns with locale, split, unpivot, merge, group) in most cases but sometimes invent function names; pasting Power Query's error message usually fixes it.
- Copilot features in Excel differ by licence, region and build, so test before planning around them [3].
- Failure modes: locale confusion (1.234,56 read as text or date), silent type errors, hard-coded column names breaking after vendor updates, models guessing what they cannot see. Never accept a result without a checksum comparison.
Shared resources & combinations
- Same assistant and prompt library: AUTO-001 formulas, AUTO-017 macros, AUTO-018 scripts, AUTO-021 regex rules.
- Upstream: AUTO-007 files attachments into the folder; AUTO-008 turns forms into rows.
- Downstream: AUTO-015 documents from the clean table; DATA-006 analysis; DATA-003 for real master-data deduplication.
- A local HW1/HW2 model serves all of these.
Effort, cost & KPIs
- Path A: one to three days for the first query incl. test, about an hour for later ones; cost is the existing subscription plus possibly a Copilot or chat licence.
- Path B: one to two weeks for a governed helper; API cost for snippets is small.
- Path C: one to two days to install and test a local model.
- KPIs: minutes per refresh, manual steps removed, errors found in review, people able to maintain each query, share of imports surviving an export change.
People & change
Involve: department lead, end users who do the chore (let them design the steps), IT (folders, tool approval), data owners of the source systems, and the DPO if exports hold personal data.
Works council: depends. Power Query itself does not monitor people, but Copilot and similar assistants log usage, which the BAG treats as objectively suited to monitor behaviour under §87(1) no. 6 BetrVG. §90(1) no. 3 requires timely information about AI-supported work processes. A framework agreement on AI assistants usually covers this (GUIDE-004). Public bodies involve the Personalrat.
Data protection: a DPIA is normally not needed for reshaping business data, but check it for HR, health or regulated customer data. Conclude an AVV (Art. 28 GDPR) with the assistant vendor (GUIDE-005, GUIDE-010).
AI literacy: Art. 4 AI Act requires measures that support staff's AI literacy; a 60-90 minute hands-on session on "describe, test, compare totals" is enough (GUIDE-006).
Pilot and concerns: start with one report and one volunteer, name an AI champion, document each query on a one-pager (GUIDE-008, GUIDE-009). Concerns: job loss (time shifts to analysis), wrong numbers (checksums), what may be pasted into a chat (GUIDE-007).
Humans in control: the user owns the query and signs off the totals.
Risks, regulation & compliance
- EU AI Act: minimal risk, no Annex III category; Art. 4 applies [5]. See GUIDE-014.
- GDPR: exports contain customer, employee or supplier data. Pseudonymise samples, use EU-hosted tenants, AVV per vendor, no private AI accounts.
- Bookkeeping: keep the unchanged original export as audit trail (GoBD) and document the query.
- Risks: silent conversion errors, shadow IT, single point of knowledge, data leaving via prompts. Mitigations: checksums, shared query register, approved tool list, restricted folders (GUIDE-013).
Getting started
- Pick one monthly export that costs over an hour; save the original and an anonymised sample.
- Write the target in two sentences plus three example rows.
- Build the query with an approved assistant and check each step's preview.
- Compare row count and sum with last month's manual result.
- Save the query, add a one-page description and folder rule, then repeat.
Sources
- Microsoft - Power Query documentation for Excel and Power BI - https://learn.microsoft.com/en-us/power-query/
- Microsoft - EU Data Boundary for the Microsoft Cloud - https://learn.microsoft.com/en-us/privacy/eudb/eu-data-boundary-learn
- Microsoft - Copilot in Excel overview - https://support.microsoft.com/en-us/copilot-excel
- Google - Apps Script overview - https://developers.google.com/apps-script
- Regulation (EU) 2024/1689 (AI Act) - https://eur-lex.europa.eu/eli/reg/2024/1689/oj
- Ollama - https://ollama.com