[Office Development]Write Power Query M Queries with AI

Summary: Use AI to write Power Query's M code for you, turning repetitive data cleaning into a reusable query.

1. Pain Point Introduction

You've almost certainly run into this: the sales detail exported from your system at the end of every month is always a "dirty table" — customer names carry stray spaces front and back, the region flips between "华东" and " 华东 ", the sales amount shows up as text like "¥1,200" with a currency symbol and thousands separators, the discount is written as "12%", and there's a useless "备注" column stuck on the far right.

Your heart sinks: if you fix this by hand, how long will it take across hundreds of rows? Miss a single space and the month-end reconciliation won't balance. You also vaguely know that Excel has something called Power Query (on the ribbon it's the "Get & Transform" group — Excel's built-in "data-cleaning workbench": you click a few buttons, it remembers the steps, and later when the data changes you just hit "Refresh" to re-clean without starting over) that can handle it.

But what really stops you is step two: once the cleaning logic gets even slightly "non-standard" (say, "when the discount is above 20%, commission is calculated at 3%"), you can't get it by clicking buttons alone — you have to edit the code behind it, which is written in a language called M (the programming language behind Power Query that writes the cleaning steps). The moment you see let...in, each, Table.AddColumn, your head spins.

Don't panic. This article teaches you a simple trick: let AI write the M code in one go, and you only need to understand it, paste it in, and refresh. AI writes the code for you; you just do the acceptance check, and every step stays readable so you know what it does.

2. Target Output

After this article, you'll walk away with two things:

• A real, runnable M query (not pseudo-code, not a screenshot — a complete let...in you can paste straight into the Power Query Advanced Editor). It does this for you: pick columns, trim spaces, turn "¥1,200" into the number 1200, calculate commission by rule, and assign an A/B/C grade by sales amount.
• A "plain words → working code" prompt template. Next time a colleague dumps another dirty table with the same structure, you swap in the column names and rules, send it to the AI, and get fresh code in three minutes; right-click "Refresh" and you're done — no redo.

"Writing M queries with AI" doesn't mean having AI click the buttons for you. It means having AI produce the code behind Power Query directly — which happens to be the part you're most afraid to write by hand, yet most need to master.

3. Hands-on Example

Below we'll use a hypothetical sales detail as the example. First build the data into a proper "Table" in Excel (select the range, press Ctrl+T, or click Insert → Table). I'll name it RawData (this table name goes into the code later, so watch the capitalization).

The dirty table RawData looks like this:

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

We want to clean it into: drop "备注", trim text spaces, convert the symbol-bearing numbers into real numbers, add a commission ("提成") column (3% when discount > 20%, otherwise 5%), and add a grade ("等级") column (sales ≥ 10000 → A, ≥ 5000 → B, rest → C).

Step 1: Generate M code with AI (prompt included verbatim)

Open ChatGPT (or any AI that can write code) and paste the prompt below in full. The key: tell it your table name, column names, what each column really looks like, and the cleaning rules; and explicitly require it to "use only Power Query standard library functions, don't invent functions that don't exist."

You are a Power Query M language expert. Use only functions from the Power Query standard library — do not invent functions that don't exist.
I have an Excel table named "RawData"; its columns and sample values are as follows (the column headers are in Chinese):
- 订单号 (Order ID): text, e.g., D001
- 客户姓名 (Customer Name): text; some cells have stray leading/trailing spaces, e.g., " 张三 "
- 地区 (Region): text; some values carry extra spaces and inconsistent casing, e.g., " 华东 "
- 销售额 (Sales): text with a currency symbol and thousands separators, e.g., "¥1,200"
- 折扣 (Discount): text with a percent sign, e.g., "12%"
- 备注 (Notes): text; not needed — remove it

Write a complete query in M (let...in structure) that does the following, in order:
1. Read the table named RawData via Excel.CurrentWorkbook
2. Keep only: 订单号, 客户姓名, 地区, 销售额, 折扣
3. 地区: trim spaces and convert to uppercase; 客户姓名: trim leading/trailing spaces
4. 销售额: strip "¥", commas, and spaces, then convert to a number; 折扣: strip "%" and spaces, then convert to a number
5. Add a "提成" (Commission) column computed by a custom function: discount > 20 → sales × 3%, otherwise sales × 5%
6. Add a "等级" (Grade) column: sales ≥ 10000 → "A", ≥ 5000 → "B", otherwise "C"
7. Order the columns as: 订单号, 客户姓名, 地区, 销售额, 折扣, 提成, 等级
Give me the complete code, ready to paste into the Advanced Editor, with line-by-line comments in English.

AI will usually return a chunk of code. Don't blindly copy it — understand it first, then verify. Below I give you a "reference answer" I've checked and confirmed runs; use it to compare. If what the AI gives matches this approach, you can basically trust it.

Step 2: Following the reference answer, type the M code in

The complete M code is below (just copy it; note the column names are in Chinese and must match your source table character-for-character):

powerquery
let
    // 1) Get data: read from the table named "RawData" in the current workbook
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],

    // 2) Keep only the needed columns, dropping "备注" (Notes)
    KeepCols = Table.SelectColumns(Source, {"订单号", "客户姓名", "地区", "销售额", "折扣"}),

    // 3) Clean text: trim spaces from 地区 and convert to uppercase; trim leading/trailing spaces from 客户姓名
    TrimText = Table.TransformColumns(KeepCols, {
        {"地区", each Text.Upper(Text.Trim(_)), type text},
        {"客户姓名", each Text.Trim(_), type text}
    }),

    // 4) Turn "销售额" and "折扣" from symbol-bearing text into real numbers
    ToNumber = Table.TransformColumns(TrimText, {
        {"销售额", each Number.FromText(Text.Remove(_, {"¥", ",", " "})), type number},
        {"折扣", each Number.FromText(Text.Remove(_, {"%", " "})), type number}
    }),

    // 5) Custom function: compute commission (3% when discount is above 20%, otherwise 5%)
    //    A custom function = a small reusable formula you define yourself, written as (parameters) => expression
    CalcCommission = (sales as number, discount as number) as number =>
        if discount > 20 then sales * 0.03 else sales * 0.05,

    // 6) Add a "提成" (Commission) column, calling the custom function above on every row
    //    Table.AddColumn adds a new column to a table; its third argument is the "what to compute per row" function
    AddCommission = Table.AddColumn(ToNumber, "提成",
        each CalcCommission([销售额], [折扣]), type number),

    // 7) Conditional column: grade each row A / B / C by sales amount
    //    A conditional column labels each row via if logic; in M it's written if...then...else, with no end keyword
    AddLevel = Table.AddColumn(AddCommission, "等级",
        each if [销售额] >= 10000 then "A"
             else if [销售额] >= 5000 then "B"
             else "C"),

    // 8) Reorder the columns and output the final result
    Result = Table.ReorderColumns(AddLevel,
        {"订单号", "客户姓名", "地区", "销售额", "折扣", "提成", "等级"})
in
    Result

Step 3: Paste the code into the Power Query Advanced Editor

Operation path (Excel 365 / 2021, the interface is basically the same):

1. Click Data → Get Data → From Other Sources → Blank Query.
2. Above the "Query Settings" pane on the right, click Advanced Editor (its icon looks like this: { }).
3. Clear out the two default lines inside (let Source = "" in Source) entirely, and paste in the complete code above.
4. Click Done. If the column names and table name are right, it immediately previews the cleaned result.
5. To land it on a worksheet: click Home → Close & Load. Later when the source table RawData changes, right-click this query → Refresh, and it re-cleans automatically.

Screenshot tip: capture an "Advanced Editor" window with a red box marking "where to paste the code" and the "Done" button, so readers get it at a glance.

Step 4: Reading the M code AI gave you (key breakdown)

The reason M code scares people is that nobody ever breaks it down for you. Actually it's just four parts; once you know them all, it's not intimidating:

• let...in is a "step list." Each line you write after let (like KeepCols = ...) is a "variable = result of this step." After in you write the variable to output in the end (here it's Result). Every step you click in the Power Query UI is automatically generated as one such line behind the scenes.
• each is shorthand for "run on every row." each Text.Trim(_) is equivalent to (_) => Text.Trim(_), where _ stands for "the current row." Inside each, to grab a column's value, write [列名] (column name) — for example each [销售额] takes the current row's sales amount. Note: the [列名] syntax only works inside each.
• Table.AddColumn is the standard way to "add a column." The first parameter is the original table, the second is the new column name (in English double quotes), the third is the "what to compute per row" function (usually each). Steps 6 and 7 in this article both use it.
• A custom function uses => not =. The line CalcCommission = (sales, discount) => ... defines a reusable little formula; the left of => is the parameter, the right is the algorithm. It must be written inside let, before in, so later steps can call it.

Once these four parts are familiar, you can read any M code the AI gives you, line by line, following let.

Step 5: Refresh to reuse

After loading the query onto a worksheet, it's "bound" to the source table RawData. When next month's new data arrives, all you do is:

1. Overwrite the new data into the RawData table (keep column names and the table name unchanged).
2. Right-click the query → Refresh (or Data → Refresh All).

Not a single cleaning step needs redoing. This is the biggest advantage of cleaning with an M query over "fixing by hand": write the rules once, reuse them for life.

4. Principle Summary

In one sentence: every step you normally click out with the mouse in Power Query is, behind the scenes, a piece of M language code; the so-called "Advanced Editor" is the window that lets you see and edit that code directly. M is a functional language — data goes in at Source, gets transformed step by step through "function pipelines" like Table.SelectColumns, Table.TransformColumns, Table.AddColumn, and finally in delivers the finished product. The value of AI is this: it turns the threshold of "you must know M to write M" into "you spell out the rules and column names in plain language, and AI produces qualified code" — and all you need is to be able to read it and accept it.

5. Pitfall Guide

1. Column names must use English double quotes and match the source table character-for-character. Even one stray space or one full-width symbol and M will report "column not found." Chinese column names are still written as "订单号", but they must be exactly identical to the header in Excel.
2. Inside each, use [列名] to get the current row's field — don't write a cell coordinate. each [销售额] is correct; outside each (such as inside a custom function body) if you want a row field, you must use the parameter — for example CalcCommission uses the passed-in sales, not [销售额].
3. Before converting text to a number, always clear the symbols first. Number.FromText("¥1,200") errors out directly; you must first Text.Remove the ¥, ,, and spaces, then convert. Nine out of ten beginners hit this pit.
4. A custom function uses => not =. (x as number) => x * 2 is a function; writing = treats it as an ordinary assignment, and it errors the moment you call it. The parameter type (as number) is optional, but writing it is safer and AI is also less likely to get it wrong.
5. After editing code in the Advanced Editor, you must click "Done" to save. Just closing the window without clicking "Done" loses your changes. After that, when the source data updates, you must click "Refresh All" to re-run the whole query — changing only the source table won't change it automatically.

6. Advanced Extensions

• Want to clean the same data using only mouse clicks, never touching code? Go back to B2 "Clean Dirty Data in One Click with AI" — it takes the route of "AI gives a checklist, you click the buttons following it," suited for those who don't want to write M for now.
• After cleaning, want to build a data model (relate multiple tables together to form a reusable "data foundation" layer) for analysis? See B23 "Build a Data Model with AI." Once the model is built, pair it with DAX (the calculation language for writing measures in Power BI / Power Pivot) to do dynamic aggregation.
• Want to make "refresh + export" a one-click action? Learn macro (the mechanism in Excel that records a sequence of operations and replays them automatically); related hands-on practice is in B7 "Mail Merge at Scale with AI" (VBA export to PDF) and C1 "Automated Weekly Report." Going further, driving Power Query query results from VBA touches on COM object (the Component Object Model object, the interface VBA uses to drive Excel and other programs) — that's a deeper level of "office development."
• Dragging the cleaned result straight into a PivotTable (the drag-fields-to-summarize table in Excel) for a monthly dashboard is the most natural next stop on this chain; to make it look good, combine it with the dashboard thinking from B24.

Wrap-up: today you got the complete playbook for "use AI to write M queries for cleaning" — prompt produces code, Advanced Editor accepts it, refresh reuses it. Next, either go to B23 to build a model and compute metrics with DAX, or go to C1 to make the whole workflow one-click with VBA.