M Query Guide

Transformations

M Adding Columns

Adding Table Columns

M adding columns uses Table.AddColumn for calculations.

Introduction to Adding Columns in M

In Power Query's M language, adding columns is a common transformation task. The Table.AddColumn function allows you to add new columns to a table, often based on calculations or transformations of existing data.

Using the Table.AddColumn Function

The Table.AddColumn function is used to add a new column to an existing table. It takes three primary arguments:

  • table: The table to which the column will be added.
  • newColumnName: The name of the new column.
  • columnGenerator: A function that defines how each value in the new column is calculated.

Basic Example of Adding a Column

Let's look at a simple example where we want to add a column named "Total" to a table of sales data. The "Total" column will be the product of "Quantity" and "Price" columns.

Handling Null Values

When adding columns, you may encounter null values in your data. It's essential to handle these to avoid errors. You can use conditional logic to manage nulls in your column calculations.

Advanced Calculations with Table.AddColumn

For more complex transformations, you can use nested functions and additional logic within the columnGenerator parameter. This allows for advanced data manipulations, such as conditional formatting or aggregations.

Conclusion

Adding columns in M using Table.AddColumn enhances the flexibility of data transformation tasks. By understanding and utilizing this function, you can perform a wide range of calculations and prepare your data more effectively for analysis.

Transformations

Next
Sorting Rows