Transformations

M Conditional Columns

Creating Conditional Columns

M conditional columns use Table.AddColumn with if logic.

Introduction to M Conditional Columns

M conditional columns offer a powerful way to enrich your data transformation processes using the M language in Power Query. By leveraging Table.AddColumn with conditional logic, you can dynamically compute values based on existing data. This is particularly useful for data cleansing, filtering, or creating new data insights.

Using Table.AddColumn with If Logic

To create a conditional column in M, you use the Table.AddColumn function along with if statements. This allows you to compute new column values based on logical conditions applied to existing columns.

Here is a simple example where we add a new column named Status to a table. The Status column will contain 'Pass' if the Score is greater than or equal to 50, and 'Fail' otherwise.

Understanding the M Code

Let's break down the code:

Table.FromRows creates an initial table from a list of rows, with columns Name and Score.
Table.AddColumn is used to add a new column to the table. The function takes three arguments: the source table, the name of the new column, and a function that defines the column's value.
The anonymous function each if [Score] >= 50 then "Pass" else "Fail" applies an if condition to each row, checking the Score value and assigning the corresponding Status.

Advanced Conditional Logic

Conditional logic in M can be extended beyond simple if statements. You can chain multiple if...else if...else statements to handle more complex scenarios. For example, categorizing scores into 'Excellent', 'Good', 'Average', and 'Poor' based on defined ranges.

Conclusion

Using conditional columns in M allows for flexible data transformation and can significantly enhance your data analysis capabilities. Whether you're performing simple binary classifications or more complex categorizations, understanding how to use Table.AddColumn with if logic is an essential skill in data manipulation.

In the next post, we'll explore Table Joins to further expand your data transformation toolkit.

Transformations

Previous: Custom Columns
Next: Table Joins