<!-- https://learnsql.cpetoday.com/functions/m/table-addcolumn -->
# Table.AddColumn

Language: Power Query M
Category: Table: columns
Syntax: Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table
Summary: Adds a new column whose value is calculated for each row.
Returns: The table with one extra column.
Level: Beginner
Related: Table.TransformColumns, Table.TransformColumnTypes, Table.AddIndexColumn, Table.DuplicateColumn

## 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
- **table**: the table to add to. Required.
- **newColumnName**: the name of the new column, in quotes. Required.
- **columnGenerator**: a function that returns the value for each row, written with `each`. Required.
- **columnType**: the data type of the new column, such as `type number`, `Currency.Type` or `type text`. Optional, but you should always give it.

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

```powerquery
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.

```powerquery
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
- Always give the type as the fourth argument. Without it, the new column has no fixed type and can load as text.
- A calculation with a blank input returns blank. Multiplying by null gives null, so the total of a blank line is blank, not zero.
- Query folding: simple arithmetic and built-in text functions usually fold into the SQL query. Custom functions and calls to other queries usually do not. Check View Native Query.
- Table.AddColumn is for a new column. To change the values of an existing column, use Table.TransformColumns.

## 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.
