Topic 8: Conditional Columns, Custom Columns & Group By
Now that you know the basic Power Query transformations, it's time to learn how to create new information from existing data.
Three particularly useful features are:
โข Conditional Columns
โข Custom Columns
โข Group By
These are different from simply cleaning existing data because you're now creating or summarizing information.
1. Conditional Columns
A Conditional Column creates a new column based on rules.
Think of it as:
"If this condition is true, return this value; otherwise, return something else."
Example: Sales Category
Suppose you have:
Product Sales
Laptop 80,000
Monitor 25,000
Keyboard 5,000
You could create a new column called Sales Category.
Your business rule might be:
Sales >= 50,000 โ High
Sales >= 20,000 โ Medium
Otherwise โ Low
The result becomes:
Product Sales Sales Category
Laptop 80,000 High
Monitor 25,000 Medium
Keyboard 5,000 Low
This is useful when you want to classify existing data into meaningful business categories.
Other examples
You could classify:
Customer value
Sales >= 100,000 โ Premium
Sales >= 50,000 โ Regular
Otherwise โ Basic
Transaction status
Amount > 100,000 โ High Value
Otherwise โ Standard
Age group
Age < 25 โ Young
Age 25โ40 โ Adult
Age > 40 โ Senior
The important thing is that the rules should come from a meaningful business requirement rather than being created randomly.
2. Custom Columns
A Custom Column allows you to create a new column using an expression written in M, Power Query's language.
For example, suppose you have:
Quantity
Price
You could create:
Sales Amount = Quantity ร Price
In Power Query, the expression could be:
[Quantity] * [Price]
The result might be:
Quantity Price Sales Amount
2 40,000 80,000
1 25,000 25,000
5 1,000 5,000
This is different from a DAX measure.
The calculation happens during the data transformation stage, before the data is loaded into the model.
3. Conditional Column vs Custom Column
This distinction is important.
Conditional Column
Best when your logic is based on straightforward conditions.
Example:
If Sales > 50,000
Then "High"
Else "Low"
Custom Column
Useful when you need more flexible expressions.
For example:
[Quantity] * [Price]
or more complex M expressions.
A simple way to remember it:
Conditional Column = Rule-based classification
Custom Column = Expression-based transformation
4. Group By
Group By is used to summarize rows based on one or more columns.
Suppose your data contains:
Region Product Sales
West Laptop 80,000
West Monitor 25,000
South Laptop 60,000
South Monitor 30,000
West Laptop 40,000
You might want total sales by region.
Group By can produce:
Region Total Sales
West 145,000
South 90,000
Instead of working with every individual transaction, you're creating a summarized dataset.
โโโโโโโโโโ
What can Group By calculate?
Depending on the requirement, you can calculate things such as:
โข Sum
โข Average
โข Minimum
โข Maximum
โข Count rows
โข Count distinct values
For example:
Total sales by region
West โ โน145,000
South โ โน90,000
Average sales by region
West โ โน48,333
South โ โน45,000
Number of transactions by region
West โ 3
South โ 2
5. Group By Multiple Columns