Downloads
Power Query MTable: columnsBeginner

Table.AddColumn

Adds a new column whose value is calculated for each row.

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

Returns: The table with one extra column.

What it does

Table.AddColumn creates a column from a formula that runs on every row. Use it for any new figure you need, such as an extended amount (quantity times price), a variance (actual minus budget) or a status label. It is the M version of adding a calculated column in a spreadsheet, and it is the step you will write most often after the data is loaded.

Syntax and arguments

Example

A sales analyst wants the value of each order line in the sample database. The line total is the quantity times the price.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Lines = Source{[Schema = "classicmodels", Item = "orderdetails"]}[Data],
    WithTotal = Table.AddColumn(Lines, "LineTotal", each [quantityOrdered] * [priceEach], type number)
in
    WithTotal

Each row gets a new LineTotal column with the quantity multiplied by the price. The type is set to number, so the column can be summed as a number.

Example: a second, different use

An aging report needs a bucket for each invoice, based on the number of days past due. The DaysPastDue column is already in the source.

let
    Source = Excel.CurrentWorkbook(){[Name = "Invoices"]}[Content],
    WithBucket = Table.AddColumn(Source, "Bucket", each
        if [DaysPastDue] > 90 then "Over 90"
        else if [DaysPastDue] > 60 then "61 to 90"
        else if [DaysPastDue] > 30 then "31 to 60"
        else if [DaysPastDue] > 0 then "1 to 30"
        else "Current", type text)
in
    WithBucket

Things to watch

In business terms

Calculated columns are where most finance logic lives in a query: extended amounts, variances, aging buckets, margins and flags for review. Writing the formula once in the query means every refresh applies the same rule.

Related functions

All Power Query M functions · View as Markdown · M basics