Table.AddColumn
Adds a new column whose value is calculated for each row.
Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as tableReturns: 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
- 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.Typeortype 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.
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
- 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.