Add Column
Power BI · Power Query (Data Transformation)
Theory
The Add Column tab creates new columns while keeping the originals. Custom Column lets you write an M expression such as [Qty] * [Price]. Conditional Column builds if-then-else logic through a form without writing code. Column From Examples lets you type the result you want for a few rows and Power Query infers the transformation. There are also ready-made options like Index Column, Duplicate Column, and Date or Text extractions such as Year or First Characters.
For example, a Conditional Column named Order Size can label each Sales row: if Amount >= 1500 then "Large", else if Amount >= 800 then "Medium", else "Small". Order 103 with ₹1,500 becomes Large and order 104 with ₹600 becomes Small. The result is a fixed column computed at refresh, so it can be used in slicers, legends and relationships.
Why we use it
Reports often need categories or derived fields that do not exist in the source. Creating them in Power Query keeps the logic in one place and makes the column available like any other.
When to use it
Use Add Column for row-level values that do not change with report filters, such as bands, flags or parsed text. If the value must react to slicers or aggregate across rows, write a DAX measure instead.
How it works
- Need a new derived column
- Is the logic a simple if/then rule? Yes: Use Conditional Column. No: Use Custom Column or Column From Examples.
- Define the rule and output values
- Check results across several rows
- New column loads with the table
Syntax
Add Column → Custom Column | Conditional Column | Column From Examples | Index Column
M:
Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as type)
each if [Amount] >= 1500 then "Large" else if [Amount] >= 800 then "Medium" else "Small"Example
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Order Size",
each if [Amount] >= 1500 then "Large"
else if [Amount] >= 800 then "Medium"
else "Small",
type text)Key points
- Custom Column lets you write M expressions
- Conditional Column builds if/then logic visually
- Column From Examples infers a transformation from sample output
- Conditions are checked top to bottom; the first match wins
- Pass a type (e.g. type text) so the column is not 'any'