Table.AddColumn

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.AddColumn

Understanding the Table.AddColumn Function

The Table.AddColumn function takes three arguments: the table to modify, the name of the new column to create, and the function to use to generate the values for the new column. The function should take a single argument (a record) and return a single value (the value for the new column).

Here is an example of how to use Table.AddColumn to create a new column that combines the values in two existing columns:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2VNJRMgYyMjQ1VrIyNjC3GBgYGBgYGBgYGBgYGJgYGBgYGJgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgYGBgrA8=", BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),

#"Added Custom" = Table.AddColumn(Source, "Combined", each [Column 1] & "-" & [Column 2])

in

#"Added Custom"

This code creates a new column called “Combined” that concatenates the values in the “Column 1” and “Column 2” columns, separated by a hyphen.

The M Code Behind the Table.AddColumn Function

The M code behind the Table.AddColumn function is relatively straightforward. Here is the basic structure of the function:

(Table as table, NewColumnName as text, ColumnExpression as function) as table =>

let

NewColumn = Table.AddColumn(Table, NewColumnName, ColumnExpression, type any)

in

NewColumn

This code defines a function with three arguments (Table, NewColumnName, and ColumnExpression) and returns a new table that includes the new column. The function first creates a new column using the Table.AddColumn function, passing in the name of the new column, the function to generate the values for the new column, and the data type of the new column (which is set to “any” by default).

The key part of the Table.AddColumn function is the ColumnExpression argument. This argument should be a function that takes a single argument (a record) and returns a single value (the value for the new column). Here is an example of a simple ColumnExpression function:

each [Column 1] + [Column 2]

This code adds the values in the “Column 1” and “Column 2” columns and returns the result as the value for the new column.

You can use any valid M expression as the ColumnExpression argument, including other functions, conditional statements, and more. Here is an example of a more complex ColumnExpression function:

(each if [Column 1] < 0 then [Column 2] 2 else [Column 1] 2)

This code checks whether the value in “Column 1” is less than zero. If it is, it multiplies the value in “Column 2” by two and returns that as the value for the new column. If it is not, it multiplies the value in “Column 1” by two and returns that as the value for the new column.

The Table.AddColumn function is a powerful tool for data transformation in Power Query M. By understanding the M code that powers this function, you can create more complex and customized transformations to meet your specific needs. Whether you are working with small or large datasets, the Table.AddColumn function can help you get the most out of your data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)