Table.AddColumn Function in Power Query M (5 Easy Examples)

If you want to add a new column to a table where each value is calculated from the other columns in that row, Table.AddColumn is the function you reach for.

You give it the table, a name for the new column, and a small formula that runs once per row. It returns the table with the new column appended on the right.

Table of Contents

Syntax of Table.AddColumn Function

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

where

table (required, table). The table you want to add a column to.
newColumnName (required, text). The name of the new column.
columnGenerator (required, function). A function, usually written with each, that computes the value for each row.
columnType (optional, nullable type). The declared data type of the new column, for example type number. Omit it and the column comes through as any (untyped).

Returns: a table, the original table with the new column added as the last column.

In plain terms, you hand it a table and a per-row formula, and it gives you back the same table with one extra column.

Example 1: Calculate a line total from two columns

You have an order list with a Qty and a UnitPrice column, and you want a LineTotal for each row.

Here is the starting data:

Product	Qty	UnitPrice
Notebook	4	3
Pen	10	1
Stapler	2	7
Marker	6	2

Multiply the two columns for each row:

let
    Source = Excel.CurrentWorkbook(){[Name="OrderLines"]}[Content],
    #"Added LineTotal" = Table.AddColumn(Source, "LineTotal", each [Qty] * [UnitPrice])
in
    #"Added LineTotal"

The each formula runs once per row, multiplying that row’s Qty by its UnitPrice.

The result adds the LineTotal column:

Product	Qty	UnitPrice	LineTotal
Notebook	4	3	12
Pen	10	1	10
Stapler	2	7	14
Marker	6	2	12

Each value is that row’s quantity times its unit price.

Example 2: Combine text columns into a full name

You have separate FirstName and LastName columns and you want a single FullName column.

Here is the starting data:

FirstName	LastName
Aisha	Khan
Bruno	Silva
Mei	Tan

Join the two columns with a space using the & operator:

let
    Source = Excel.CurrentWorkbook(){[Name="Employees"]}[Content],
    #"Added FullName" = Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName])
in
    #"Added FullName"

The generator concatenates the first name, a space, and the last name for each row.

The result adds the FullName column:

FirstName	LastName	FullName
Aisha	Khan	Aisha Khan
Bruno	Silva	Bruno Silva
Mei	Tan	Mei Tan

The & operator works on text, so both columns are stitched together row by row.

Example 3: Add a column based on a condition

You have an order list with an Amount column, and you want to flag each order as High or Low.

Here is the starting data:

OrderID	Amount
101	250
102	80
103	500
104	150

Use an if expression inside the generator:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    #"Added Tier" = Table.AddColumn(Source, "Tier", each if [Amount] >= 200 then "High" else "Low")
in
    #"Added Tier"

The generator checks each row’s Amount against the 200 threshold.

The result adds the Tier column:

OrderID	Amount	Tier
101	250	High
102	80	Low
103	500	High
104	150	Low

Orders of 200 or more are tagged High, the rest are Low.

Example 4: Set the column type while adding it

You have invoice subtotals and you want a TaxAmount column typed as a number.

Here is the starting data:

Invoice	Subtotal
INV-01	100
INV-02	40
INV-03	250

Pass type number as the fourth argument:

let
    Source = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],
    #"Added TaxAmount" = Table.AddColumn(Source, "TaxAmount", each [Subtotal] * 10 / 100, type number)
in
    #"Added TaxAmount"

The type number argument declares the new column as a number, so you skip a separate type-change step.

The result adds the TaxAmount column:

Invoice	Subtotal	TaxAmount
INV-01	100	10
INV-02	40	4
INV-03	250	25

Each value is 10 percent of that row’s subtotal.

Example 5: Build a code from multiple functions

You have product SKUs and names, and you want a Code that joins an uppercase SKU with the length of the name.

Here is the starting data:

SKU	Name
abc	Wireless Mouse
def	USB Cable
ghi	Laptop Stand

Nest a few text functions inside the generator:

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    #"Added Code" = Table.AddColumn(Source, "Code", each Text.Upper([SKU]) & "-" & Text.From(Text.Length([Name])), type text)
in
    #"Added Code"

The generator uppercases the SKU, then appends a hyphen and the character count of the name.

The result adds the Code column:

SKU	Name	Code
abc	Wireless Mouse	ABC-14
def	USB Cable	DEF-9
ghi	Laptop Stand	GHI-12

Text.Length returns a number, so Text.From converts it to text before it is joined with &.

Things to keep in mind with Table.AddColumn

columnType only declares a type, it does not convert. Passing type number labels the column but does not change the underlying values. If the generator returns text, the column stays text with a number label, which causes errors downstream. Wrap the value in Number.From, Text.From, or Date.From to actually convert it.
Omitting columnType leaves the column as any. An untyped column can break later steps that expect a specific type. Add the type argument, or follow up with Table.TransformColumnTypes.
Reusing an existing column name fails. The step errors because the name is already in the table. Pick a name that is not already used, or rename the old column first.

Performance and query folding

Simple custom columns built from basic arithmetic or text operations can fold to sources like SQL Server, so the calculation happens at the database. More complex M-only logic, custom functions, or nested transformations usually break folding, so Power Query computes the column locally. On Excel, CSV, and folder sources it always runs locally, which is fine for typical table sizes.

Common questions about Table.AddColumn

What is the difference between Table.AddColumn and Table.TransformColumns?

Table.AddColumn creates a brand new column and leaves the existing ones untouched. Table.TransformColumns changes the values in columns that already exist, without adding anything new.

Can I reference the new column in a later step?

Yes. Once Table.AddColumn finishes, the new column is part of the table, so the next step (including another Table.AddColumn) can read it by name like any other column.

List of All Power Query Functions

Related Power Query Functions / Articles: