Table.AddColumn
Adds a column whose value a function works out from each row.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table to add the column to. |
newColumnName | text | The new column's name; no column of the table may have it already. |
columnGenerator | function | A function of one row, often written with each, that gives the row's value for the new column. |
columnType | nullable type optional | The new column's type, such as type number or Int64.Type; any when left out. |
Returns
table. The table with the new column as its last one.
Remarks
- Inside
each,[Price]is the row's Price and_is the whole row as a record. - Give a type when the column goes into the model, so that it loads as a number, a date or a text rather than as
any. - This is the step a custom column writes in Transform data.
Examples
Revenue on each line
let
Lines = #table({"Product", "Price", "Units"}, {{"Chair", 50, 4}, {"Desk", 120, 1}, {"Lamp", 30, 6}})
in
Table.AddColumn(Lines, "Revenue", each [Price] * [Units])| Product | Price | Units | Revenue |
|---|---|---|---|
| Chair | 50 | 4 | 200 |
| Desk | 120 | 1 | 120 |
| Lamp | 30 | 6 | 180 |
With a type
The schema shows the new column's kind.
let
Lines = #table({"Product", "Price", "Units"}, {{"Chair", 50, 4}, {"Desk", 120, 1}, {"Lamp", 30, 6}}),
Added = Table.AddColumn(Lines, "Revenue", each [Price] * [Units], type number)
in
Table.Schema(Added)[[Name], [Kind]]| Name | Kind |
|---|---|
| Product | any |
| Price | any |
| Units | any |
| Revenue | number |
A label made of two columns
let
Lines = #table({"Product", "Price", "Units"}, {{"Chair", 50, 4}, {"Desk", 120, 1}, {"Lamp", 30, 6}})
in
Table.AddColumn(Lines, "Label", each [Product] & " x" & Text.From([Units]), type text)| Product | Price | Units | Label |
|---|---|---|---|
| Chair | 50 | 4 | Chair x4 |
| Desk | 120 | 1 | Desk x1 |
| Lamp | 30 | 6 | Lamp x6 |
Related functions
Table.AddIndexColumn, Table.DuplicateColumn, Table.TransformColumns, Table.SelectRows.