# Qwen — Excel, raw output

- Date: 2026-09-17, sent 09:24 (+07), "Запуск интерпретатора кода…" for about eleven minutes, answered by ~09:36. Free tier, Qwen3.7-Plus, "Авто" mode; interface Russian, answer in English. File attached by dropping it on the composer; brief pasted.
- Capture: page text via JS and screenshots.
- First read: it says "✅ Cleaned file generated: cleaned_orders_q2_2026.xlsx" but nothing is attached to the message - no link, no card, no download; the file stayed in its sandbox. Numbers: April right, May €1,050.30 and June €1,030.50 wrong, Home €1,471.00 and Pantry €1,261.60 wrong, "Ceramic Bowl (44 units)" wrong - exactly what you get by listing the four duplicates and then summing with them still in (Pantry +€151.90, Home +€72.00, Ceramic Bowl +4). Five mismatches right with old and new values. "Defaulted missing Qty to 1" instead of deriving it from Total ÷ Price - happens to be right for all three rows. Seven of eight faults named (blank regions not mentioned). Formula: make a Table named Orders, add a helper Month column with TEXT(), then SUMIFS on it - works and grows, three steps.

## Answer

1. Data Issues & Fixes

- Inconsistent Dates: Mixed formats (e.g., "Apr 23, 2026", "12/04/2026"). → Standardized to YYYY-MM-DD.
- Currency in Numbers: Unit Price contained "€" and commas (e.g., "€21,00"). → Stripped symbols and converted to numeric.
- Missing Data: Blank Categories and Quantities. → Auto-filled categories based on product names; defaulted missing Qty to 1.
- Messy Text: Extra spaces and inconsistent casing (e.g., " Linen Tea Towel", "LINEN TEA TOWEL"). → Trimmed and Title-Cased.
- Math Errors: 5 rows had incorrect Totals. → Recalculated as Qty × Unit Price.

✅ Cleaned file generated: cleaned_orders_q2_2026.xlsx

2. Aggregated Metrics

Revenue per Month: April 2026: €1,538.60 · May 2026: €1,050.30 · June 2026: €1,030.50
Revenue per Category: Home: €1,471.00 · Pantry: €1,261.60 · Drinks: €886.80
Most Units Sold: Ceramic Bowl (44 units)

3. Duplicates & Mismatches

Duplicate Order IDs (identical rows): PT-1027 (Guilherme Pinto, Olive Oil 1L) · PT-1075 (Lara Mendes, Olive Oil 1L) · PT-1085 (Filipe Sousa, Ceramic Bowl) · PT-1120 (Nuno Lopes, Piri-piri Sauce)

Total Mismatches (Original Total → Corrected Total): PT-1028: €1.90 → €4.90 · PT-1037: €16.60 → €17.80 · PT-1101: €18.50 → €13.50 · PT-1010: €8.40 → €9.60 · PT-1039: €7.70 → €8.90

4. Dynamic Excel Formula

To get revenue per month that automatically updates when you add rows:

1. Select your cleaned data and press Ctrl + T to format it as an Excel Table. Name it Orders (in the Table Design tab).
2. Add a new column named Month with this formula: `=TEXT([@Date],"mmm-yyyy")`
3. Use this formula to calculate monthly revenue (e.g., for April):

    =SUMIFS(Orders[Total], Orders[Month], "Apr-2026")

(Change "Apr-2026" to "May-2026", etc. The structured table reference ensures it always includes new rows).