Table.AddColumn

Table.AddColumn is a Power Query M function that adds a new column to a table, with values calculated using the specified columnGenerator function. The function returns a modified table with the new column added.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
table		The table to add a column to.
newColumnName		The new column’s name.
columnGenerator		A function performed in a row context that generates the value for your value. Simplified functions always start with the keyword ‘each’.
columnType	optional	A data type to ascribe to the new column. This could be a primitive type like ‘type text, ‘type number’ etc. Alternatively, you can provide Type Claims like Text.Type, Number.Type, etc.

Description

The Table.AddColumn function in Power Query M adds a new column to an existing table. This function requires you to choose the table to be modified (table), name the new column (newColumnName), and define a formula or expression (columnGenerator) for generating values in the new column.

Additionally, it supports an optional columnType parameter. Specifying a data type for columnType does not convert the data in your column but tells the M engine to assume that the column is of this specified type. This means the engine won’t perform additional checks or conversions based on this data type assumption. Whereas this feature is a convenient way to add a data type to a column, it can also cause errors when the data type is incompatible with the generated value.

Examples

Imagine you’re working with a basic table in Power Query M that includes columns for ProductKey, Product, and Sales.

Now, let’s say you want to add a new column to this table to show sales including a Value-Added Tax (VAT) of 10%. Here’s a simple and user-friendly way to do this using Power Query’s Table.AddColumn function.

Adding A Custom Column

To start, go to the “Add Column” tab in Power Query and select “Custom Column.” This action opens a dialog box where you can input your formula. To calculate the sales including VAT, you can use a straightforward formula like:

This formula takes each sale and multiplies it by 1.10, effectively adding a 10% VAT. When you apply this formula, Power Query M automatically uses the Table.AddColumn function as follows:

Table.AddColumn(Source, "Sales Incl VAT", each [Sales] * 1.10)

In this function, “Source” refers to your original table, “Sales Incl VAT” is your new column name, and the formula for calculating the VAT is specified after each. Interestingly, we didn’t type in the word ‘each’ in the Custom Formula Box. Power Query automatically adds it to indicate the start of a function.

Assigning a Data Type to Your New Column

You might want to specify a data type for your new column. This is where the optional fourth argument of Table.AddColumn comes in handy. For instance, if you want your new column to be treated as a number, you would write:

Table.AddColumn(Source, "Sales Incl VAT", each [Sales] * 1.10, type number )

However, it’s important to note that specifying a data type doesn’t convert the data. It simply tells Power Query M to treat the column as that type. You could label a column containing numbers as type text and you won’t run into an error within Power Query.

Table.AddColumn(Source, "Sales Incl VAT", each [Sales] * 1.10, type text )

However, this leads to errors when loading the data into Power BI or Excel, as the actual content and the assigned type would be inconsistent.

Working with the Entire Row

Any expression you provide to Table.AddColumn is evaluated for each row in the table. In some cases, you may want to reference the entire row as a record. This is done by using the underscore symbol _ in your formula:

Table.AddColumn(Source, "Sales Incl VAT", each _ )

This approach considers the entire row for each iteration in the table.

Experimenting with the Function

To try this out yourself, you can use the following Power Query M code snippet. This code creates a sample table and applies the Table.AddColumn function in different ways, giving you a hands-on feel for how the function works:

let
    Source = 
      #table( 
        type table[ ProductKey = Int64.Type, Product = Text.Type, Sales = Int64.Type ],
        { 
          { 1, "Product A", 150 },
          { 2, "Product B", 200 },
          { 3, "Product C", 175 },
          { 4, "Product D", 220 }
        }  
    ),
    SimpleExpression =     Table.AddColumn(Source, "Sales Incl VAT", each [Sales] * 1.10),
    IncludeDataType =      Table.AddColumn(Source, "Sales Incl VAT", each [Sales] * 1.10, type number ),
    IncompatibleDataType = Table.AddColumn(Source, "Sales Incl VAT", each [Sales] * 1.10, type text ),
    ReferenceRow =         Table.AddColumn(Source, "Sales Incl VAT", each _ )
in
    ReferenceRow

Related functions

Other functions related to Table.AddColumn are:

• Table.AddIndexColumn
• Table.AddRankColumn
• Table.DuplicateColumn

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-addcolumn