Creating Conditional, Custom, and Index Columns in Power BI

Course: Microsoft Power BI Training

Topic: Creating Conditional, Custom, and Index Columns in Power Query

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Creating Columns in Power BI

Power BI provides several ways to create new columns that improve the quality, usability, and analytical value of a dataset. In Power Query Editor, conditional columns, custom columns, and index columns are especially useful for transforming raw data into a structured format before loading it into a Power BI data model.

These column types help analysts categorize records, calculate values, create labels, assign sequential numbers, and prepare datasets for reporting. They can be created through the Power Query interface without manually modifying the original source file.

1.1 What Is a Column in Power BI?

A column is a field that stores a particular type of information for each row in a table. Examples include Product Name, Quantity, Sales Amount, Customer Category, Order Date, and Region.

A newly created column derives its values from conditions, formulas, existing columns, or sequential numbering rules.

1.2 What Is Power Query?

Power Query is the data preparation and transformation engine available in Power BI. It allows users to connect to data sources, clean data, change data types, remove errors, combine tables, and create new columns.

Power Query records transformations as steps in the Applied Steps pane. When the data is refreshed, these steps are executed again against the source data.

1.3 Learning Objectives

• Understand conditional, custom, and index columns.
• Open Power Query Editor and locate the column creation tools.
• Create conditional columns using business rules.
• Write custom formulas using Power Query M language.
• Generate sequential indexes with different starting values and increments.
• Choose suitable data types for newly created columns.
• Validate results and troubleshoot common errors.
• Apply all three column types in a practical sales reporting project.

2. Understanding the Three Types of Columns

2.1 Conditional Column

A conditional column evaluates a rule and returns a specified result when the rule is satisfied. If the condition is not satisfied, another result can be returned.

For example, a business may classify sales as follows:

• Sales of 50,000 or more: High Sales.
• Sales of 20,000 or more but below 50,000: Medium Sales.
• Sales below 20,000: Low Sales.

2.2 Custom Column

A custom column allows users to write formulas using the Power Query M language. It can reference one or more existing columns, perform arithmetic, manipulate text, work with dates, or apply conditional logic.

For example, if a table contains Quantity and Unit Price, a custom column can calculate the total sales value for every row.

2.3 Index Column

An index column assigns sequential numbers to rows. Depending on the selected settings, numbering can start from zero, one, or a custom value and increase by a specified increment.

An index is useful for identifying row positions during transformations. However, an index is not automatically a permanent business identifier, because its values can change when row ordering or source data changes.

3. Opening Power Query Editor

Before creating new columns, open Power Query Editor in Power BI Desktop.

3.1 Step-by-Step Procedure

1. Open Power BI Desktop.
2. Load a dataset using Home > Get Data.
3. Select the appropriate data source, such as Excel, CSV, SQL Server, or another supported source.
4. Choose the required table or worksheet.
5. Select Transform Data to open Power Query Editor.
6. Select the query you want to transform from the Queries pane.
7. Review the table preview and check the existing column names and data types.
8. Select the Add Column tab to access the column creation options.

3.2 Important Options in the Add Column Tab

Important: Use Add Column when you want to retain the original columns. Transform commands generally modify the selected column or its values instead.

4. Creating Conditional Columns

4.1 Definition

A conditional column is a new column whose values depend on one or more conditions. It follows an IF-THEN-ELSE approach and is useful for turning numerical or textual data into meaningful categories.

The general logical structure is:

if condition then result_if_true else result_if_false

This is the basic structure of a Power Query M conditional expression. The Conditional Column dialog helps build such rules without requiring users to write the entire formula manually.

4.2 Example Dataset

Suppose the business wants to classify each order as High, Medium, or Low according to the Sales Amount column.

4.3 Step-by-Step: Create a Conditional Column

1. Open Power Query Editor and select the sales query.
2. Select Add Column > Conditional Column.
3. Enter Sales Category as the new column name.
4. Set the first condition to Sales Amount greater than or equal to 50000.
5. Set the output for this condition to High.
6. Add another clause for Sales Amount greater than or equal to 20000.
7. Set the second output to Medium.
8. Set the final Else output to Low.
9. Select OK and review the generated values.
10. Set the new column's data type to Text.

Conditions are evaluated from top to bottom. Therefore, the higher threshold must be checked before the lower threshold. Otherwise, a value of 75,000 would also satisfy the lower threshold and might be assigned to the wrong category.

4.4 Expected Output

4.5 Creating a Conditional Column Using M Code

The same business rule can be written as a custom M expression:

if [Sales Amount] >= 50000 then "High"
else if [Sales Amount] >= 20000 then "Medium"
else "Low"

This formula can be entered in the Custom Column dialog. It checks whether the sales amount reaches the highest threshold first, then checks the medium threshold, and otherwise returns Low.

4.6 Using Text Conditions

Conditional columns can also evaluate text values. For example, a company may classify orders according to the payment status.

if [Payment Status] = "Paid" then "Completed"
else if [Payment Status] = "Pending" then "Awaiting Payment"
else "Review Required"

Before creating text conditions, check for inconsistent spelling, extra spaces, and different capitalization in the source data. Clean the values when necessary so the rules match the intended records.

4.7 Using Multiple Conditions

Business rules sometimes depend on more than one field. In Power Query M, the operators and and or can combine conditions.

Example: classify an order as a Priority Order only when the sales amount is at least 50,000 and the order status is Confirmed.

if [Sales Amount] >= 50000 and [Order Status] = "Confirmed"
then "Priority Order"
else "Standard Order"

The and operator requires both conditions to be true. The or operator requires at least one condition to be true.

4.8 Common Uses of Conditional Columns

• Classifying customers by purchase amount.
• Grouping products by price range.
• Assigning performance ratings to employees.
• Identifying overdue invoices.
• Classifying orders by status.
• Creating eligibility flags for business processes.
• Grouping delivery records into On Time and Delayed categories.

5. Creating Custom Columns

5.1 Definition

A custom column is a new field generated using a formula written in the Power Query M language. It is more flexible than the basic rule-building interface because it can combine arithmetic, text operations, date functions, conditional expressions, and references to multiple fields.

5.2 Example Dataset

The business wants to calculate the Gross Amount and Net Amount for each product order.

5.3 Step-by-Step: Create a Custom Column

1. Open Power Query Editor.
2. Select the required sales query.
3. Choose Add Column > Custom Column.
4. Enter Gross Amount as the new column name.
5. Enter the formula [Quantity] * [Unit Price].
6. Review the formula and select OK.
7. Set the new column's data type to an appropriate numeric or currency-compatible type.
8. Repeat the process to create Net Amount using the gross amount and discount rate.
9. Check the calculated values against a manual calculation for several rows.

5.4 Calculating Gross Amount

Gross Amount is the quantity multiplied by the unit price.

[Quantity] * [Unit Price]

For a laptop order containing two units at 40,000 each, the gross amount is 80,000.

5.5 Calculating Net Amount After Discount

When Discount Rate is stored as a decimal, subtracting it from 1 gives the percentage of the original amount that remains payable.

[Gross Amount] * (1 - [Discount Rate])

For the laptop example, a 10% discount on 80,000 produces a net amount of 72,000.

5.6 Expected Output

5.7 Combining Text Values

Custom columns can combine multiple text fields to create readable labels or identifiers.

[First Name] & " " & [Last Name]

If First Name is Asha and Last Name is Sharma, the result is Asha Sharma.

Another example is combining a product category and product name:

[Category] & " - " & [Product]

For reliable results, ensure the referenced columns contain compatible values and handle null values where necessary.

5.8 Creating Date-Based Custom Columns

Date functions can extract useful information from a valid date column.

Extract the month number:

Date.Month([Order Date])

Extract the year:

Date.Year([Order Date])

Extract the month name:

Date.MonthName([Order Date])

These columns can help organize transactions by year and month during data preparation. If the date field contains text rather than a date value, convert it to the correct data type before applying date functions.

5.9 Creating a Conditional Custom Column

Custom columns can also contain business logic that goes beyond simple arithmetic.

if [Quantity] >= 10 then "Bulk Order"
else "Regular Order"

This example classifies orders containing ten or more units as Bulk Order. Smaller orders receive the Regular Order label.

5.10 Useful Power Query M Expressions

5.11 Handling Null Values in Custom Columns

A null value represents missing or unknown information. A formula that uses missing inputs may return null or produce an error, depending on the operation and data types involved.

For example, a formula can explicitly handle a missing quantity:

if [Quantity] = null then 0
else [Quantity] * [Unit Price]

Use this approach only when treating a missing quantity as zero is correct for the business. If the missing value means that the record is incomplete, preserving null or flagging the record for review may be more appropriate.

6. Creating Index Columns

6.1 Definition

An index column assigns sequential numeric values to the rows in a table. It is created through Add Column > Index Column in Power Query Editor.

An index can be used for row numbering, positional filtering, debugging transformations, or supporting calculations that depend on row position. It should not be treated as a durable unique key unless its stability and uniqueness are explicitly guaranteed.

6.2 Types of Index Columns

6.3 Step-by-Step: Add an Index Column

1. Open Power Query Editor.
2. Select the table that needs sequential numbering.
3. Open the Add Column tab.
4. Select Index Column.
5. Choose From 0, From 1, or Custom.
6. If Custom is selected, enter the desired starting value and increment.
7. Select OK if a dialog is displayed.
8. Rename the resulting column to a meaningful name such as Row Number or Import Sequence.
9. Review the table and verify that the values increase as expected.

6.4 Example: Index Starting from 1

6.5 Custom Index Example

Suppose the index should start at 100 and increase by 10. Choose Add Column > Index Column > Custom, and set the starting value to 100 and the increment to 10.

6.6 Index Column Using Power Query M

The M function Table.AddIndexColumn creates an index column. The following example starts at 1 and increments by 1:

Table.AddIndexColumn(
    #"Previous Step",
    "Row Number",
    1,
    1,
    Int64.Type
)

Replace #"Previous Step" with the actual name of the preceding step in your query. The final argument specifies a whole-number data type for the index.

For a custom sequence starting at 100 and increasing by 10, use:

Table.AddIndexColumn(
    #"Previous Step",
    "Custom Index",
    100,
    10,
    Int64.Type
)

6.7 Important Considerations When Using Index Columns

• Sort the table explicitly before adding an index when row order matters.
• Filtering or sorting rows after creating an index can change the relationship between index values and visible row positions.
• Refreshing data can assign different index values if the source ordering changes.
• An index generated independently in multiple tables does not automatically create a valid relationship between those tables.
• For stable relationships, prefer genuine business keys or properly designed dimension-table keys.
• Use a whole-number data type when the index represents sequential integers.

7. Conditional Column vs Custom Column vs Index Column

8. Understanding Power Query M and Applied Steps

8.1 What Is Power Query M?

Power Query M is the formula language used to define data extraction and transformation steps. The Power Query interface generates M code for many operations, including creating conditional columns, custom columns, and index columns.

Users can inspect the generated expressions by selecting a step and reviewing the formula bar or opening Advanced Editor.

8.2 Example of a Custom Column Step

= Table.AddColumn(
    #"Changed Type",
    "Gross Amount",
    each [Quantity] * [Unit Price],
    type number
)

This expression adds a column named Gross Amount, calculates it for each row, and declares its type as number. The name of the previous step must match the actual query.

8.3 Example of a Conditional Column Step

= Table.AddColumn(
    #"Changed Type",
    "Sales Category",
    each
        if [Sales Amount] >= 50000 then "High"
        else if [Sales Amount] >= 20000 then "Medium"
        else "Low",
    type text
)

The expression creates a text category for each row based on the Sales Amount field.

8.4 Example of an Index Step

= Table.AddIndexColumn(
    #"Previous Step",
    "Row Number",
    1,
    1,
    Int64.Type
)

This step creates a whole-number index beginning at one. The sequence follows the row order presented to this step.

8.5 Why Applied Steps Matter

Every transformation is recorded in the Applied Steps pane. This makes it possible to review, modify, or remove transformations without manually editing the original source file.

• Rename steps clearly when the query contains many transformations.
• Check step order when a formula references a column created earlier.
• Inspect the formula bar when a transformation does not produce the expected output.
• Be careful when deleting a step that later steps depend on.

9. Data Types and Column Quality

9.1 Why Data Types Matter

A column's data type determines how its values are interpreted and which operations can be performed reliably. For example, a sales amount should be numeric, an order date should be a date or date/time, and a sales category should generally be text.

9.2 Changing a Column's Data Type

1. Select the column header in Power Query Editor.
2. Select the data type icon next to the column name or use the Transform tab.
3. Choose the appropriate data type.
4. Review any conversion errors.
5. Check a sample of values to confirm that the conversion is correct.

Conditional and custom columns may initially have an unsuitable or generic data type. Always verify the resulting type rather than assuming Power Query has inferred it correctly.

9.3 Handling Errors and Missing Data

Before creating calculated columns, inspect the source fields for errors, blanks, null values, and inconsistent data. A missing Unit Price can affect a sales calculation, while a missing Sales Amount can prevent a conditional classification from being meaningful.

Decide whether missing values should remain null, be replaced with a valid default, or be flagged for correction. The correct choice depends on the business meaning of the data.

10. Combining Conditional, Custom, and Index Columns

In practical data preparation, these column types are often used together. A custom column can calculate sales, a conditional column can classify the resulting amount, and an index column can number the rows.

10.1 Example Workflow

1. Load the sales data into Power Query Editor.
2. Check the data types of Quantity, Unit Price, and Order Date.
3. Create a custom column named Gross Amount using Quantity multiplied by Unit Price.
4. Create a custom column named Net Amount if the dataset includes a valid discount rate.
5. Create a conditional column named Sales Category based on the appropriate amount and business thresholds.
6. Add an index column if sequential row numbering is required.
7. Review the output and correct data types.
8. Select Close & Apply to load the prepared data into Power BI Desktop.

10.2 Sample Final Table

This example uses a threshold of 50,000 for High, 20,000 for Medium, and lower values for Low. The index represents the order of rows at the point where the index was created.

11. Troubleshooting Common Problems

12. Best Practices for Creating Columns

• Use meaningful names: Choose names such as Net Amount, Customer Segment, and Row Number instead of generic names such as Custom1.
• Validate business rules: Confirm category thresholds and outputs with the relevant business requirements.
• Order conditions carefully: Check overlapping rules and place conditions in the intended evaluation order.
• Check data types: Ensure calculations use appropriate numeric, date, or text types.
• Handle nulls deliberately: Do not automatically replace missing data with zero unless the business logic supports it.
• Test boundary values: Check values immediately below, at, and above each threshold.
• Keep transformations understandable: Use descriptive column names and review the Applied Steps pane.
• Use index columns carefully: Sort data before indexing when order matters, and do not assume the index remains stable across refreshes.
• Validate calculations: Manually calculate a few sample rows to confirm formula results.
• Keep transformations maintainable: Remove unnecessary steps and avoid creating duplicate columns that are not needed.

13. Practical Project: Sales Data Preparation

13.1 Project Objective

Prepare a sales dataset by calculating the total value of each order, classifying sales performance, and assigning row numbers. The final output should be ready to load into a Power BI report.

13.2 Sample Source Data

13.3 Project Tasks

1. Load the source table into Power Query Editor.
2. Check and correct the data types.
3. Create a custom column named Total Sales using [Quantity] * [Unit Price].
4. Create a conditional column named Sales Category using these rules: High for sales of 50,000 or more; Medium for sales of 20,000 or more; otherwise Low.
5. Create a conditional column named Order Classification that returns Valid Order for Completed orders, Awaiting Action for Pending orders, and Review for all other statuses.
6. Add an index column starting from 1 and name it Row Number.
7. Check the calculated values and data types.
8. Select Close & Apply to load the transformed table.

13.4 Expected Result

13.5 Project Questions

• What would happen if the Medium condition were placed before the High condition?
• How would you calculate Total Sales if the dataset also included a decimal Discount Rate column?
• Why should Row Number not automatically be used as a permanent customer identifier?
• How would you handle an order with a missing Unit Price?
• Which columns should have numeric data types, and which should have text data types?

14. Interview Questions and Answers

Q1. What is a conditional column in Power BI?

A conditional column creates values according to specified conditions. It is commonly used to categorize records based on numerical ranges, text values, dates, or other business rules.

Q2. What is a custom column in Power Query?

A custom column is created using an M formula. It can perform calculations, manipulate text, extract date information, and combine values from multiple columns.

Q3. What is an index column?

An index column assigns sequential numbers to rows. It can start at zero, one, or a custom starting value and can use a specified increment.

Q4. What is the difference between a conditional column and a custom column?

A conditional column provides a convenient interface for building rule-based logic. A custom column allows more flexible formulas using the Power Query M language. Custom columns can also contain conditional expressions.

Q5. Can a custom column use values from multiple columns?

Yes. A custom column can reference multiple existing columns in a single formula, provided the operations and data types are compatible.

Q6. What does the M expression each mean?

The each keyword is shorthand for a function applied to the current row or item in many Power Query operations. In a table-addition step, it allows a formula to calculate a value for each row.

Q7. How do you create a column that calculates sales?

Use a custom column with the formula [Quantity] * [Unit Price], after confirming that both source columns contain appropriate numeric values.

Q8. How can you create a sales category using conditions?

Create a conditional column with rules for the required sales thresholds, or use a nested M expression that checks the thresholds in the correct order.

Q9. Why can an index column change after refreshing data?

The index depends on the row order at the point where it is added. If the source order or upstream transformations change, the same record may receive a different index value.

Q10. Does an index column guarantee a unique permanent identifier?

It can produce unique sequential values within a particular table result, but it does not guarantee a stable identifier across refreshes or different tables. Use a reliable business key or a deliberately designed key for permanent identification.

Q11. Why is setting a data type important?

Correct data types help prevent calculation errors, ensure values are interpreted correctly, and support reliable filtering, sorting, and modeling.

Q12. Where can you review the M code generated by Power Query?

You can select a transformation in Applied Steps and inspect the formula bar, or open Advanced Editor to review the query's M expressions.

Q13. How do you handle missing values in a custom calculation?

First determine what the missing value means. Depending on the business rule, preserve null, replace it with a valid default, or flag the record for review.

Q14. Can a conditional column return text and numbers?

Power Query expressions can return different kinds of values, but a column should ideally have a consistent data type. If a rule mixes text and numbers, consider redesigning the output or explicitly converting the values.

Q15. When should you use Power Query instead of a DAX calculated column?

Use Power Query when the calculation belongs to the data preparation and transformation process before loading the model. DAX calculated columns are evaluated within the data model and are useful when the logic depends on model relationships or DAX-specific behavior. The best choice depends on the requirement.

15. Quick Revision Checklist

• Understand the difference between conditional, custom, and index columns.
• Know where to find the Add Column options in Power Query Editor.
• Use conditional clauses in the correct order.
• Write M expressions with correct column references and syntax.
• Understand basic arithmetic, text, and date functions.
• Know how to create an index from zero, from one, or from a custom starting value.
• Set the correct data type for each new column.
• Test formulas against sample rows and boundary values.
• Handle null values and errors according to business requirements.
• Review Applied Steps and confirm the final output before loading the data.

16. Conclusion

Conditional, custom, and index columns are important Power Query tools for preparing data in Power BI. Conditional columns apply business rules, custom columns create flexible calculations through the M language, and index columns add sequential numbering for row-position and transformation tasks.

By combining these features, analysts can clean datasets, calculate meaningful values, classify records, and create structured tables for reporting. Always validate business rules, check data types, handle missing values carefully, and use indexes appropriately before loading the transformed data into Power BI.

17. Additional Learning Resources

• Microsoft Learn: Add a Conditional Column
• Microsoft Learn: Add a Custom Column
• Microsoft Learn: Table.AddIndexColumn Function
• Microsoft Learn: Power Query Documentation
• Explore Microsoft Power BI Training at JustAcademy
• Register for a Course Demo