Table.AddColumn
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Adds a column calculated from each row.
When to use it
Use it for any value calculated from the row: totals, flags, categories, keys. Always pass the type argument so the new column isn't typed as any. For changing an existing column, Table.TransformColumns avoids a helper column.
Syntax
Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as tableParameters
- columnGenerator
- A function of the row, usually written with each.
- columnType
- Optional type for the new column, such as type number.
Returns
The table with the new column at the end.
Example
Table.AddColumn(#table({"Region", "Product", "Qty"}, {{"North", "Kite", 2}, {"South", "Reel", 5}, {"North", "Reel", 1}, {"West", "Kite", 4}}), "Double", each [Qty] * 2, type number)Result
| Region | Product | Qty | Double |
|---|---|---|---|
| North | Kite | 2 | 4 |
| South | Reel | 5 | 10 |
| North | Reel | 1 | 2 |
| West | Kite | 4 | 8 |
Example: a conditional category
if … then … else inside each gives each row its own result.
Table.AddColumn(#table({"Region", "Rep", "Amount"}, {{"North", "Ana", 120}, {"South", "Bo", 80}, {"North", "Cy", 200}, {"South", "Ana", 50}}), "Size", each if [Amount] >= 100 then "Large" else "Small", type text)Result
| Region | Rep | Amount | Size |
|---|---|---|---|
| North | Ana | 120 | Large |
| South | Bo | 80 | Small |
| North | Cy | 200 | Large |
| South | Ana | 50 | Small |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Give a type so downstream tools don't see 'any'.
Common mistakes
- Leaving out the type, so the column loads as text or any.
- Adding a column whose name already exists.
The matching command in Excel
- Excel for Windows
- Add Column > Custom Column
- What you see
- You type the formula for one row and the column is calculated for every row.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a custom column (Microsoft Learn).
Exercises that use Table.AddColumn
- beginner~10 minFree
Clean and summarize a sales export
Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.
- intermediate~8 minFree
Find the three largest orders (kite shop)
Calculate each order's value and keep the three biggest, largest first.
- beginner~7 minFree
Fix a query whose steps point at the wrong step (kite shop)
A step was renamed and the next step still refers to the old name. Repair the chain so the query runs.
- beginner~5 minFree
Return the last step, not an earlier one (kite shop)
The query builds a Line Total column, but the result doesn't show it: the `in` clause returns an earlier step.
- intermediate~7 minFree
Fix a query that removes a column before using it (kite shop)
The query trims columns too early, so the Revenue step can't find UnitPrice. Reorder the steps.
- beginner~7 minFree
Convert Yes/No answers to true, false or null (charity)
The marketing opt-in column mixes Yes, Y, no, N, blanks and nulls. Turn it into a proper true/false column.
Learn more
- Keep the top N rows after sorting lesson
- Conditional columns with if … then … else lesson
- Mapping tables vs nested if: categorise values the maintainable way guide
- Your first Power Query in Excel, from table to refresh guide
Related functions
Official documentation: Table.AddColumn on Microsoft Learn.