M Query Guide

Examples

M Custom Column Query

Creating a Custom Column

M custom column query calculates profit with expressions.

Introduction to M Custom Column Queries

The M language, used in Power Query, allows users to manipulate data with ease. One of its powerful features is the ability to create custom column queries to perform calculations, such as computing profits using expressions. This tutorial will guide you through creating a custom column to calculate profit by subtracting costs from revenue.

Setting Up Your Power Query Environment

Before diving into the M custom column query, ensure you have loaded your dataset into Power Query. You can do this by loading your data from Excel, CSV, or any other supported data source. Once your data is loaded, follow these steps to create a custom column.

Creating a Custom Column

To create a custom column in Power Query, navigate to the Add Column tab and select Custom Column. This opens a dialog where you can define your custom calculation using M expressions.

In the code above, we create a new column named Profit. The expression each [Revenue] - [Cost] calculates the profit by subtracting the Cost from the Revenue for each row in the dataset.

Understanding the M Expression

The M expression used in custom columns is quite flexible. The each keyword allows you to apply a function to each row in the table. In this example, the function is a simple arithmetic operation that subtracts one column from another.

Advanced Custom Column Calculations

Beyond simple arithmetic, you can use various M functions to perform more complex operations. For instance, you can include conditional logic or multiple arithmetic operations in a single custom column.

In this advanced example, we categorize the profit into High or Low based on the condition [Revenue] - [Cost] > 1000. This illustrates the use of conditional statements in custom column expressions.

Conclusion

Using M custom column queries in Power Query enables you to perform a variety of calculations directly within your data transformation process. By following the examples provided, you can customize and extend your data analysis capabilities effectively.

Examples