# 🧹 B26｜Write Power Query M Queries with AI — Companion Assets

> **Series**: AI for Office Work: From Beginner to Pro
> **Article**: B26 (Track 7 Office Dev / Level 2 Intermediate)
> **Title (EN)**: Write Power Query M Queries with AI
> **Title (ZH)**: 用 AI 写 Power Query M 查询做数据清洗

---

## 1. Who is this for? What problem does it solve?

For anyone who **downloads a "dirty" sales detail CSV every month** and then manually trims whitespace, strips currency symbols, and calculates commissions. Open every file, hand-edit, save — your wrists hurt. Change one rule and you have to re-do all 30 spreadsheets.

The main tutorial teaches you how to **use AI to generate Power Query M code** — you describe column names + rules in plain English, and AI writes the code; you only need to read it, paste it, and refresh.

This folder ships a **minimal, real, end-to-end pipeline**:

| File | Purpose |
|------|---------|
| `数据源_销售明细_sample.csv` | 14 rows of sample data (with `¥` / `%` / whitespace / unwanted column) |
| `清洗查询_en.pq` | Full M query (English comments): 5+ step function pipeline |
| `清洗查询_zh.pq` | Same M query (Chinese comments) |
| `清洗前后对比_en.md` | Step-by-step "before/after" comparison (English) |
| `清洗前后对比_zh.md` | Same comparison (Chinese) |

After running this folder, you have a **paste-into-Advanced-Editor-ready** M template. Next month, just change column names and rules and ship.

---

## 2. How to use (5 steps to run the sample)

### Step 1: Import the sample CSV into Excel and name the table `RawData`

1. Open Excel (Office 365 / 2021 work fine);
2. Data → From Text/CSV → pick `数据源_销售明细_sample.csv` → Import;
3. Select the imported range, press **`Ctrl + T`** (or Insert → Table), check "My table has headers";
4. **Table Design → Table Name** → change to **`RawData`** (case-sensitive; must match `[Name="RawData"]` in `.pq` code).

> If you skip this, the Advanced Editor will throw `Expression.Error: The name '...' isn't a table in this workbook`.

### Step 2: Open "Blank Query → Advanced Editor"

1. Data → Get Data → From Other Sources → **Blank Query**;
2. In the right-hand "Query Settings" pane, click **Advanced Editor** (icon `{ }`);
3. **Clear** the default two lines `let Source = "" in Source`.

### Step 3: Paste the M code

Open `清洗查询_en.pq`, select all (`Ctrl+A`) and copy (`Ctrl+C`), paste into the Advanced Editor, click **Done**.

You should immediately see 14 rows × 7 columns of cleaned results. If you see red text, go back to the checklist in `清洗前后对比_en.md`.

### Step 4: Land the result on a worksheet

Home → **Close & Load** → **Close & Load To...** → pick Table → Existing Worksheet → an empty cell (e.g. `H1`) → OK.

### Step 5: Reuse automatically every month

When next month's data arrives:
1. **Overwrite** the new data into the `RawData` table (keep column names and table name unchanged);
2. Right-click the query → **Refresh** (or Data → Refresh All).

**No re-writing the cleaning code** — that's the Power Query dividend: write the rule once, use it forever.

---

## 3. File inventory

| File | Type | Purpose | Lines |
|------|------|---------|-------|
| `B26_PowerQuery_M_EnglishREADME.md` | Markdown | This file | — |
| `B26_M查询_中文README.md` | Markdown | Chinese README | — |
| `数据源_销售明细_sample.csv` | CSV | 14 rows of sample data | 15 |
| `清洗查询_en.pq` | M code | Full query (English comments) | ~75 |
| `清洗查询_zh.pq` | M code | Full query (Chinese comments) | ~75 |
| `清洗前后对比_en.md` | Markdown | Step-by-step comparison (English) | ~110 |
| `清洗前后对比_zh.md` | Markdown | Step-by-step comparison (Chinese) | ~110 |

---

## 4. Real code (key M snippets explained line-by-line)

### ① Source + SelectColumns

```powerquery
// Read table named "RawData" from the current workbook
Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],

// Whitelist columns; drop "备注"
KeepCols = Table.SelectColumns(Source, {"订单号", "客户姓名", "地区", "销售额", "折扣"}),
```

**Two APIs**:
- `Excel.CurrentWorkbook()` returns the list of all tables in the workbook; `{[Name="RawData"]}` picks one by name, `[Content]` extracts its content.
- `Table.SelectColumns(table, {"col1","col2",...})` picks columns by whitelist.

### ② Text cleaning

```powerquery
TrimText = Table.TransformColumns(KeepCols, {
    {"地区",      each Text.Upper(Text.Trim(_)), type text},
    {"客户姓名",  each Text.Trim(_),            type text}
}),
```

**Three syntax sugars**:
- `each _` is shorthand for `(_) => _`, where `_` is "the current row";
- `Text.Trim` removes leading/trailing whitespace, `Text.Upper` uppercases;
- `type text` locks column type so it isn't inferred as `any`.

### ③ Text → Number

```powerquery
ToNumber = Table.TransformColumns(TrimText, {
    {"销售额", each Number.FromText(Text.Remove(_, {"¥", ",", " "})), type number},
    {"折扣",   each Number.FromText(Text.Remove(_, {"%", " "})),     type number}
}),
```

**Order matters**: you must `Text.Remove` to strip `¥` `,` `%` and space FIRST, then `Number.FromText`. Calling `Number.FromText("¥1,200")` directly throws.

### ④ User-defined function + AddColumn

```powerquery
// User-defined function = a reusable mini-formula you define yourself
CalcCommission = (sales as number, discount as number) as number =>
    if discount > 20 then sales * 0.03 else sales * 0.05,

// Invoke the function for every row
AddCommission = Table.AddColumn(ToNumber, "提成",
    each CalcCommission([销售额], [折扣]), type number),
```

**Two key points**:
- User-defined functions use `=>`, NOT `=`;
- `[销售额]` `[折扣]` accesses the column value of the current row inside `each`; outside `each` (e.g. inside the function body), use parameter names like `sales`, not `[销售额]`.

### ⑤ Conditional column + Reorder

```powerquery
AddLevel = Table.AddColumn(AddCommission, "等级",
    each if [销售额] >= 10000 then "A"
         else if [销售额] >= 5000  then "B"
         else "C", type text),

Result = Table.ReorderColumns(AddLevel,
    {"订单号", "客户姓名", "地区", "销售额", "折扣", "提成", "等级"})
```

**Two key points**:
- M's `if` has NO `end`; chain branches with `then`, close with `else`;
- `Table.ReorderColumns` only reorders columns; it does not touch data.

---

## 5. FAQ / Pitfalls

1. **`Expression.Error: The name '...' isn't a table`**
   Table name typo / no table created / mixed Chinese punctuation. Go back to Step 1; verify `RawData` matches case-sensitively, and column names are EXACT.

2. **`Number.FromText` errors / whole column is `null`**
   Symbols still present. Add the missing character to the `Text.Remove` set; full-width space is `U+3000`, written as `{"　"}`.

3. **`each [列名]` says "column not found"**
   Most often a column-name typo. Another cause: writing `[列名]` OUTSIDE `each` — `[列名]` only works inside `each`; outside, use parameter names.

4. **Edits in the Advanced Editor don't persist**
   You must click **Done** to save; just closing the window discards changes. Next refresh re-runs the old code.

5. **Whole column becomes `Error`**
   Usually a row has a blank `销售额`, or dirty text like "TBD" in source. Clean that row manually in `RawData` (or add `try ... otherwise null`), then return to M.

---

## 6. Next steps

After this folder, you have a "paste-into-Advanced-Editor-ready" M cleaning template. Two natural next stops:

- **B23 "Build a Data Model with AI"**: feed cleaned results into a relational model with DAX measures — the standard follow-up to "clean, then summarize".
- **B25 "Batch-Process Excel with Python + openpyxl"**: when you need to process **dozens of files** rather than clean one, Python + openpyxl's read-modify-write pipeline is more ergonomic.
- **Don't want to touch code at all?** See B2 "AI One-Click Dirty-Data Cleaning" — the "AI lists steps, you click the buttons" lightweight route.

---

> 📌 **Quick checklist** (verify before running):
> - [ ] `RawData` table created (`Ctrl+T`);
> - [ ] Table name `RawData` is case-sensitive match;
> - [ ] Column names `订单号 / 客户姓名 / 地区 / 销售额 / 折扣` match the code EXACTLY;
> - [ ] Default two lines in Advanced Editor cleared, full M code pasted;
> - [ ] After "Done", preview shows 14 rows × 7 columns, no red text;
> - [ ] "Close & Load" successfully landed on a worksheet.