M Query Guide

Transformations

M Custom Columns

Creating Custom Columns

M custom columns use Table.AddColumn with expressions.

Introduction to M Custom Columns

In Power Query, custom columns allow you to create new columns in a table by applying transformations and expressions. The Table.AddColumn function is a powerful tool used to add custom columns to your data. This post will guide you through the process of creating and using custom columns with M expressions.

Using Table.AddColumn Function

The Table.AddColumn function adds a new column to an existing table. You can define the column name and the expression that calculates the column's values. The basic syntax is as follows:

Creating a Simple Custom Column

Let's start with a simple example where we have a table of sales data and we want to add a new column to calculate the total price by multiplying the quantity by the unit price.

Advanced Expressions for Custom Columns

Custom columns can also utilize more advanced expressions to perform complex calculations. For example, you can use conditional logic or reference other columns in the table. Here is an example where we apply a discount based on the quantity purchased:

Common Use Cases for Custom Columns

  • Calculations: Perform mathematical operations on existing columns, such as totals or averages.
  • Data Transformation: Reformat text, split columns, or combine multiple columns into one.
  • Conditional Logic: Apply logic to create new columns based on conditions in the data.

Conclusion

Custom columns in Power Query provide a flexible and powerful way to enhance your data transformations. By using the Table.AddColumn function, you can create new insights and prepare your data for further analysis. Experiment with different expressions and functions to unlock the full potential of your data.

Transformations

Next
Conditional Columns