Power BI enables users to transform and prepare data efficiently using Power Query. Custom Columns and M Language allow you to create new calculated fields and apply advanced transformations before the data reaches the report layer.
- Create new fields using calculations based on existing columns
- Apply conditional logic, text transformations and date formatting
- Prepare clean, structured and analysis-ready datasets for reporting
Custom Columns in Power BI
Custom Columns allow you to create new fields in your dataset by applying formulas or transformations to existing columns. They are created in Power Query and help shape data before it is loaded into the report.
- Perform arithmetic calculations such as totals, area or volume
- Combine or modify text values
- Apply conditional logic and formatting rules
Steps to Add a Custom Column
1. Open Power Query Editor in Power BI.
2. Select the table where you want to add the new column.
You can download dataset from here
3. Go to the Add Column tab and click on Custom Column.
4. Enter a name for the new column and write the required formula using M Language.
6. Click OK to create the column.
Using M Language for Complex Calculations
While basic formulas are easy to implement more advanced transformations rely on M Language a functional programming language used by Power Query.
M Language is the formula and query language used in Power Query to perform data transformation in Power BI. It is a functional programming language designed to clean, reshape and manipulate data before it is loaded into the data model.
- It is based on functions. Every transformation is written as a function that takes input data and produces output data.
- Power Query stores transformations as sequential steps. Each step references the previous one, ensuring transparency and easy debugging.
- M Language is case-sensitive.
- M provides a wide range of built-in functions for text, date, number and table transformations.
Steps to Add a Custom Column Using M Language
1. Open Power BI Desktop and click on Transform Data to open the Power Query Editor.
2. In the Queries pane, select the table where you want to create the custom column.
You can download dataset from here
3. Go to the Add Column tab in the ribbon. Click on Custom Column. A dialog box will appear.
4. Enter a name for the new column in the New column name field.
6. In the formula box, write the required M Language expression.
7. Click OK to apply the formula. The new column will be created with calculated values.
8. Click Close and Apply to load the updated data into the Power BI report.