Write Power Query M Code with AI

The Workflow That Works

• 1. Use the ribbon first

  Connect to the source and do the easy steps with clicks. This gives the AI a correct starting point with your real column names and types.

• 2. Copy the whole query

  Home → Advanced Editor → select all → copy.

• 3. Describe the change precisely

  Say what the output should look like, not just the operation. Include an example row before and after.

• 4. Paste back and step through

  Replace the code in Advanced Editor, click Done, then click each step in Applied Steps to see where the data changes as expected.

• 5. Ask for comments

  Request comments in the code (// lines) so the next person can follow it.

A Good Prompt for M Code

Here is my Power Query M code (Excel 365):
[paste query]

Change it so that:
1. It loads every .xlsx file in the folder C:\Reports\Branches, not just one file.
2. From each file it takes only the sheet named "Sales".
3. It adds a column "Branch" with the file name without extension.
4. It keeps the existing cleaning steps for each file.
Column names are exactly as in my code. Add // comments to each step.

Examples You Can Adapt

Combine all files in a folder with a source column

let
    // All Excel files in the folder
    Source = Folder.Files("C:\Reports\Branches"),
    OnlyXlsx = Table.SelectRows(Source, each [Extension] = ".xlsx"
                                and not Text.StartsWith([Name], "~$")),
    // Read the "Sales" sheet from each workbook
    WithData = Table.AddColumn(OnlyXlsx, "Data", each
        let wb = Excel.Workbook([Content], true)
        in  wb{[Item = "Sales", Kind = "Sheet"]}[Data]),
    WithBranch = Table.AddColumn(WithData, "Branch",
                    each Text.BeforeDelimiter([Name], ".")),
    Keep = Table.SelectColumns(WithBranch, {"Branch", "Data"}),
    Expanded = Table.ExpandTableColumn(Keep, "Data",
                    {"Date", "Customer", "Amount"}),
    Typed = Table.TransformColumnTypes(Expanded,
                {{"Date", type date}, {"Amount", type number}})
in
    Typed

Unpivot a month-per-column report into rows

    Unpivoted = Table.UnpivotOtherColumns(Source, {"Product", "Region"},
                                          "Month", "Value"),
    MonthAsDate = Table.TransformColumns(Unpivoted,
                    {{"Month", each Date.FromText("1 " & _), type date}})

Conditional column with several rules

    Tiered = Table.AddColumn(Typed, "Size", each
        if [Amount] = null then "Unknown"
        else if [Amount] >= 10000 then "Large"
        else if [Amount] >= 1000 then "Medium"
        else "Small", type text)

Replace errors instead of failing the refresh

    SafeAmount = Table.AddColumn(Expanded, "AmountClean",
        each try Number.From([Amount]) otherwise null, type number)

Merge with a lookup table (left join)

    Merged = Table.NestedJoin(Typed, {"Customer"}, Customers, {"Customer"},
                              "Cust", JoinKind.LeftOuter),
    WithManager = Table.ExpandTableColumn(Merged, "Cust", {"AccountManager"})

Common M Errors and What They Mean

Making Queries Robust for Other People

• Store folder paths in a named cell or parameter, not hard-coded in M, so colleagues can point to their own path.

• Select columns by name early in the query; extra columns in next month's file will not break anything.

• Set data types once, near the end, rather than letting Power Query auto-detect at every step.

• Add a final row-count check and show it on a Control sheet.

• Name steps meaningfully (FilteredActive, not Filtered Rows 3).