Power Query provides a powerful feature called Custom Columns, allowing users to create new data fields using formulas. While the graphical interface is useful, leveraging M Language gives more control and flexibility.

In this guide, we’ll walk through the process of adding custom columns using M Language, its syntax, and real-world examples to enhance your data transformation.

1. What is M Language in Power Query?

M Language is the scripting language behind Power Query, enabling users to perform advanced transformations that are difficult to achieve using only the UI.

Key Features of M Language:

✔ Case-sensitive and function-driven language.
✔ Uses let-in expressions for defining step-by-step transformations.
✔ Supports conditional logic, mathematical operations, and text manipulation.

2. How to Add a Custom Column in Power Query?

Adding a custom column in Power Query can be done using the GUI, but for advanced transformations, M Language is the best choice.

Steps to Add a Custom Column Using M Language:

  1. Open Power Query Editor in Power BI or Excel.
  2. Click on Add Column > Custom Column.
  3. In the Custom Column formula box, enter an M Language expression.
  4. Click OK and check the new column.
  5. Apply transformations and Close & Load.

3. Common Use Cases for Custom Columns with M Language

Here are some practical examples of how to use M Language in custom columns.

Example 1: Conditional Logic – Categorizing Data

If you need to classify sales data into categories:

= if [Sales] > 5000 then "High" else "Low"

This formula checks if sales exceed 5000 and assigns a category accordingly.

Example 2: Text Manipulation – Extracting Initials

Extracting initials from a customer’s name:

= Text.Upper(Text.Start([CustomerName], 1))

This formula converts the first letter of the customer’s name to uppercase.

Example 3: Date Calculations – Adding a Fiscal Year Column

Creating a fiscal year column based on a specific cutoff date:

= if Date.Month([OrderDate]) >= 7 then Date.Year([OrderDate]) + 1 else Date.Year([OrderDate])

This formula assigns a fiscal year starting in July.

Example 4: Mathematical Operations – Calculating Profit Margin

If you want to calculate the profit margin percentage:

= ([Revenue] - [Cost]) / [Revenue] * 100

This formula computes the profit margin as a percentage.

4. Best Practices for Using M Language in Custom Columns

✔ Keep formulas simple – Break complex logic into multiple steps.
✔ Use meaningful column names – Helps in easy identification.
✔ Test expressions step by step – Debugging is easier with incremental validation.
✔ Avoid excessive nested if-statements – Use switch logic for better performance.

Conclusion

Adding Custom Columns using M Language in Power Query provides powerful capabilities for data transformation and automation. By understanding M syntax, you can create advanced calculations, improve data quality, and enhance Power BI reporting.

Start experimenting with M Language today to take your Power Query skills to the next level!