# 🧹 B26｜Sales Detail — Before vs After Cleaning

> **Companion file**: `清洗查询_en.pq`
> **Data source**: `数据源_销售明细_sample.csv`
> **Target table**: `RawData` (after pressing `Ctrl+T` in Excel)

This document explains each M step: what it does, which core function is used, and how it cleans each "dirty" issue.

---

## 1. Raw data (before cleaning)

The `RawData` table has 6 columns × 14 rows with these typical dirty issues:

| 订单号 | 客户姓名 | 地区 | 销售额 | 折扣 | 备注 |
|---|---|---|---|---|---|
| D001 | **" 张三 "** | 华东 | **"¥1,200"** | **"12% "** | 老客户 |
| D002 | 李四 | **" 华北 "** | ¥8,500 | 25% | 促销 |
| D003 | **" 王五  "** | 华南 | **"¥15,300 "** | **"5% "** | 大单 |
| D004 | 赵六 | 华东 | ¥3,400 | 30% | 复购 |
| ... | ... | ... | ... | ... | ... |

Four typical dirt issues:
1. **Stray whitespace**: `客户姓名 " 张三 "`, `地区 " 华东 "`;
2. **Currency symbol + thousands separator**: `销售额 "¥1,200"`;
3. **Percent as text**: `折扣 "12%"` is text, not number;
4. **Unnecessary column**: drop `备注`.

---

## 2. What each step does

| Step | M snippet | What it does | Key functions |
|---|---|---|---|
| 1 | `Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content]` | Read table named "RawData" from current workbook | `Excel.CurrentWorkbook` |
| 2 | `KeepCols = Table.SelectColumns(Source, {"订单号","客户姓名","地区","销售额","折扣"})` | Whitelist 5 columns; drop `备注` | `Table.SelectColumns` |
| 3 | `TrimText = Table.TransformColumns(KeepCols, {{"地区", each Text.Upper(Text.Trim(_)), type text}, ...})` | Trim + uppercase `地区`; trim `客户姓名` | `Table.TransformColumns` + `Text.Trim` + `Text.Upper` |
| 4 | `ToNumber = Table.TransformColumns(TrimText, {{"销售额", each Number.FromText(Text.Remove(_, {"¥",","," "})), ...}, ...})` | Strip symbols, then convert to number | `Text.Remove` + `Number.FromText` |
| 5 | `CalcCommission = (sales, discount) => if discount > 20 then sales*0.03 else sales*0.05` | User-defined function: commission rule | `=>` custom function syntax |
| 6 | `AddCommission = Table.AddColumn(ToNumber, "提成", each CalcCommission([销售额],[折扣]), type number)` | Add `提成` column by calling `CalcCommission` per row | `Table.AddColumn` + `each` + `[列名]` |
| 7 | `AddLevel = Table.AddColumn(AddCommission, "等级", each if [销售额]>=10000 then "A" else if ...)` | Conditional column: A/B/C by sales | nested `if...then...else` |
| 8 | `Result = Table.ReorderColumns(AddLevel, {...})` | Reorder columns for tidiness | `Table.ReorderColumns` |

---

## 3. Output (after cleaning)

| 订单号 | 客户姓名 | 地区 | 销售额 | 折扣 | 提成 | 等级 |
|---|---|---|---|---|---|---|
| D001 | 张三 | 华东 | 1200 | 12 | 60 | B |
| D002 | 李四 | 华北 | 8500 | 25 | 255 | B |
| D003 | 王五 | 华南 | 15300 | 5 | 765 | A |
| D004 | 赵六 | 华东 | 3400 | 30 | 102 | C |
| ... | ... | ... | ... | ... | ... | ... |

Concrete changes:
- `" 张三 "` → **张三** (trim leading/trailing whitespace);
- `" 华东 "` / `" 华北 "` → **华东 / 华北** (trim + uppercase);
- `"¥1,200"` → **1200** (strip `¥`, `,`, space, then convert);
- `"12% "` → **12** (strip `%`, space, then convert);
- New **提成** column (commission by rule);
- New **等级** column (≥10000 = A, ≥5000 = B, else C);
- Drop **备注** column; columns reordered for business habit.

---

## 4. How to extend

| Goal | Where to change | Example |
|---|---|---|
| Change discount threshold to 25% | Step 5 `if discount > 20` | `if discount > 25` |
| Change commission to 4% / 6% | Step 5 `sales*0.03` / `sales*0.05` | `sales*0.04` / `sales*0.06` |
| Change level thresholds to 8000 / 3000 | Step 7 `if [销售额]>=10000` | `if [销售额]>=8000` |
| Add a `折扣金额` column | Insert before Step 8 | `AddDiscountAmt = Table.AddColumn(AddLevel, "折扣金额", each [销售额]*[折扣]/100, type number)` |
| Pick more columns | Step 2 whitelist | `Table.SelectColumns(Source, {"订单号", "客户姓名", "地区", "销售额", "折扣", "提成", "等级"})` |

---

## 5. Prompt to ask AI for review

```
Here is my Power Query M query:
{paste full content of 清洗查询_en.pq}

Please help me:
1. Explain each step in plain English.
2. Point out 3 likely error spots (e.g., column name typos, Number.FromText limitations).
3. Modify the rule to "discount > 25% → 4% commission, otherwise 6%" — give me the revised version (keep comments bilingual).
```

---

> 📌 **Checklist**:
> - [ ] Excel table name = `RawData` (case-sensitive);
> - [ ] Column names `订单号 / 客户姓名 / 地区 / 销售额 / 折扣` match code EXACTLY;
> - [ ] After clicking "Done", the preview renders without red errors;
> - [ ] After Close & Load: 14 rows × 7 columns;
> - [ ] Spot-check 2 rows of 提成/等级 by manual calculation.