How to Create Custom Columns Using Power Query in Excel

Custom columns are one of the most powerful features in Power Query, enabling you to create new data fields based on calculations, conditions, and transformations of existing columns. Whether you need to combine fields, categorize data, calculate metrics, or manipulate text, custom columns provide the flexibility to shape your data exactly as needed.

This comprehensive guide covers everything from simple formulas to advanced M language techniques, with practical examples for every common scenario.

Why Use Custom Columns in Power Query?

Custom columns are essential for professional data analysis:

• Data enrichment: Add calculated fields without modifying source data
• Business logic: Implement categorization and conditional rules
• Data transformation: Manipulate text, dates, and numbers
• Efficiency: Automate calculations that would be tedious in Excel formulas
• Consistency: Apply the same logic consistently across millions of rows
• Maintainability: Update logic in one place and refresh all dependent data

1. Creating Your First Custom Column

Method: Using the Custom Column Dialog

The easiest way to add a custom column:

1. In Excel, open your Power Query data or import new data
2. Go to the Data tab and select Edit Query (or right-click the query and select Edit)
3. In the Power Query Editor, click the Add Column tab in the ribbon
4. Select Custom Column from the dropdown
5. The Add Custom Column dialog box opens
6. Enter a name for your new column in the New column name field
7. In the Custom column formula box, enter your formula
8. Click OK to create the column

Simple Formula Example:

=each [UnitPrice] * [Quantity]

This multiplies two columns to create a TotalSales column.

2. Basic Custom Column Formulas

Mathematical Operations

Formula	Description	Result
=each [Price] * [Quantity]	Multiply two columns	Total sales amount
=each [Revenue] - [Cost]	Subtract columns	Profit amount
=each ([Revenue] - [Cost]) / [Revenue] * 100	Calculate percentage	Profit margin %
=each 1 + 1	Static value	2 in every row
=each "Constant Text"	Add text to all rows	Same text in all rows

3. Text Manipulation Formulas

Common Text Operations

Extract Initials from Names:

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

Takes the first letter of a name and converts it to uppercase.

Combine Multiple Columns:

=each [FirstName] & " " & [LastName]

Concatenates first and last names with a space.

Extract Text Before a Delimiter:

=each Text.BeforeDelimiter([Email], "@")

Extracts the username portion of an email address.

Convert Text to Proper Case:

=each Text.Proper([ProductName])

Capitalizes the first letter of each word.

4. Conditional Columns with IF Logic

Simple IF Statement:

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

Categorizes sales into two categories based on a threshold.

Using the Conditional Column Feature (No Coding Required):

1. In Power Query Editor, click Add Column tab
2. Select Conditional Column from the dropdown
3. The Add Conditional Column dialog opens
4. Enter your new column name (e.g., “Grade”)
5. Select the column to evaluate (e.g., “Marks”)
6. Choose the operator (e.g., “is greater than”)
7. Enter the value to compare (e.g., 40)
8. Enter the output value if true (e.g., “Pass”)
9. Click Add Clause to add additional conditions
10. Click OK

Example: Multi-Level Categorization

=if [Sales] >= 10000 then "Premium"
else if [Sales] >= 5000 then "Standard"
else if [Sales] >= 1000 then "Basic"
else "Entry"

This categorizes sales into four tiers based on amount ranges.

5. Date and Time Calculations

Create a Fiscal Year Column:

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

Assigns fiscal years for organizations with July start dates.

Calculate Days Since Purchase:

=each Duration.Days(DateTime.Now() - [PurchaseDate])

Calculates the number of days between purchase date and today.

Create Quarter Column:

=each "Q" & Text.From(Number.RoundUp(Date.Month([OrderDate])/3))

Converts dates to quarter format (Q1, Q2, Q3, Q4).

Combine Date Columns:

=each #date([Year], [Month], [Day])

Creates a complete date from separate Year, Month, and Day columns.

6. Advanced Custom Column Examples

Example 1: Product Category Assignment by Text Prefix

=each 
  if Text.StartsWith([StoreID], "F") then "Free Standing"
  else if Text.StartsWith([StoreID], "O") then "Outlet"
  else if Text.StartsWith([StoreID], "P") then "Pop Up"
  else if Text.StartsWith([StoreID], "S") then "Shop in Shop"
  else "Other"

Example 2: Age Group Classification

=each
  if [Age] < 18 then "Minor"
  else if [Age] < 35 then "Young Adult"
  else if [Age] < 55 then "Middle Aged"
  else "Senior"

Example 3: Revenue Commission Calculation

=each
  if [Revenue] >= 100000 then [Revenue] * 0.15
  else if [Revenue] >= 50000 then [Revenue] * 0.10
  else if [Revenue] >= 10000 then [Revenue] * 0.05
  else [Revenue] * 0.02

Example 4: Running Total/Cumulative Sum

=each List.Sum(List.FirstN(Table[Amount], [Index]))

Note: This requires adding an Index column first.

7. Using the M Language for Complex Transformations

Understanding M Language Syntax

M is the underlying language Power Query uses. Key components:

• each: Operates on each row
• [ColumnName]: References a column
• &: Text concatenation operator
• if… then… else: Conditional logic
• Text.*, Date.*, Number.*: Built-in functions

Example: Complex Text Transformation

=each 
  let
    firstName = Text.Start([FullName], Text.PositionOf([FullName], " ") - 1),
    lastName = Text.AfterDelimiter([FullName], " "),
    initials = Text.Upper(Text.Start(firstName, 1)) & Text.Upper(Text.Start(lastName, 1))
  in
    initials

8. Common Custom Column Use Cases

Use Case 1: Create Product SKU

=each 
  Text.Upper(Text.Start([Category], 3)) 
  & "-" 
  & Text.From([ProductID])

Result: “ELE-12345” from Category “Electronics” and ProductID 12345

Use Case 2: Customer Loyalty Tier

=each
  if [TotalPurchases] >= 50000 then "Platinum"
  else if [TotalPurchases] >= 25000 then "Gold"
  else if [TotalPurchases] >= 10000 then "Silver"
  else "Bronze"

Use Case 3: Risk Classification

=each
  if [DaysOverdue] > 90 then "Critical"
  else if [DaysOverdue] > 60 then "High"
  else if [DaysOverdue] > 30 then "Medium"
  else if [DaysOverdue] > 0 then "Low"
  else "On Time"

Use Case 4: Full Address Combination

=each 
  [StreetAddress] 
  & ", " 
  & [City] 
  & ", " 
  & [State] 
  & " " 
  & [PostalCode]

9. Troubleshooting Custom Column Errors

Problem: “The formula is not valid”

Solutions:

• Ensure column names are enclosed in [brackets]
• Check for proper quotation marks around text values
• Verify all operators are spelled correctly (then, else, if)
• Use & for text concatenation, not + operator

Problem: “DataType.Error”

Solutions:

• Convert data types before using in calculations: Number.From([TextColumn])
• Ensure all operands are the same data type
• Add explicit type conversion functions

Problem: Column Returns “Error” or Null Values

Solutions:

• Add error handling: =each try [Calculation] otherwise null
• Check for empty cells in referenced columns
• Verify formula logic handles edge cases

10. Best Practices for Custom Columns

Performance Optimization

• Keep formulas simple: Complex nested formulas slow refresh performance
• Avoid circular dependencies: Don’t reference columns that depend on this column
• Use native functions: Built-in M functions are faster than custom logic
• Place calculated columns last: Order matters for query performance

Maintainability

• Use meaningful names: Call columns “DiscountAmount”, not “Col47”
• Document complex formulas: Add comments explaining logic
• Test with sample data: Verify results before deploying to production
• Version control: Track changes to complex formulas

Data Quality

• Handle nulls explicitly: Use coalesce or conditional logic
• Validate results: Spot-check formulas against known values
• Document assumptions: Note data types and expected ranges
• Test edge cases: Ensure formulas work with extreme values

11. Converting Excel Formulas to Power Query

Excel Formula → Power Query Translation

Excel Formula	Power Query Equivalent
=IF(A1>100,"High","Low")	=each if [Column]>100 then "High" else "Low"
=CONCATENATE(A1,"-",B1)	=each [ColumnA] & "-" & [ColumnB]
=UPPER(A1)	=each Text.Upper([Column])
=LEFT(A1,3)	=each Text.Start([Column], 3)
=YEAR(A1)	=each Date.Year([Column])
=ROUND(A1,2)	=each Number.Round([Column], 2)

Start with simple formulas using the built-in Conditional Column feature. As you gain confidence, explore M language for more complex transformations. Always test formulas with sample data, document your logic, and prioritize performance when working with large datasets.

With these skills, you can transform raw data into analysis-ready datasets that power better business decisions.