How to Add Column in Power BI with Formula

Adding a new column in Power BI to calculate a value or categorize data is a fundamental skill for any data analyst. You often need to create new information from the data you already have, like calculating profit from sales and cost, or grouping customers into categories. This article will walk you through the two primary ways to add a formula-based column in Power BI: using DAX for Calculated Columns and M language for Custom Columns in the Power Query Editor.

Calculated Columns vs. Custom Columns: Which Should You Use?

Before you add a column, it’s important to know which method is right for your situation. Power BI offers two distinct environments for creating columns, and choosing the correct one can impact your report's performance and flexibility.

What is a Calculated Column (DAX)?

A Calculated Column is created using DAX (Data Analysis Expressions) after your data has been loaded into the Power BI data model. It exists at the same level as the other columns in your table and is calculated row by row.

When to use a Calculated Column: Use DAX when you need a column that performs calculations based on data from other columns within the same table or based on relationships between tables that are already established in your model. It's best for creating values that you want to see directly in a table, use as a slicer, or analyze in a chart.

• Calculation Time: Calculated columns are computed when you first create them and during a data refresh. They use your computer's RAM and CPU, which means having too many complex ones can slow down your report.
• Example Scenarios:

What is a Custom Column (Power Query)?

A Custom Column is created in the Power Query Editor using M language. This happens before your data is loaded into the Power BI data model. It's part of your data preparation and transformation process. The result is a static column that's included with your data when it's loaded.

When to use a Custom Column: Use Power Query when you need to shape your data during the import process. This is the ideal place to create columns based on complex, step-by-step logic, retrieve data from other queries, or perform transformations that don't depend on report-level interactions like slicers.

• Calculation Time: Custom columns are computed only when the data is refreshed in the Power Query Editor. Once loaded, they act just like any other imported column, generally leading to better report performance for complex transformations.
• Example Scenarios:

A simple rule of thumb: If you need to manipulate or prepare your data as it comes in, use Power Query. If you need a column that dynamically calculates values based on the data already in your model, use DAX.

Method 1: How to Add a Calculated Column with DAX

Let's walk through creating a Calculated Column step-by-step in the main Power BI interface. We'll use a sample sales table with columns for Product, Revenue, and Cost.

Step 1: Navigate to the Data View

In Power BI Desktop, you can work from either the Report View or the Data View. The Data View gives you an Excel-like grid of your data, making it easier to see the results of your formula immediately. Click the table icon on the left sidebar to enter the Data View.

Step 2: Select Your Table

In the Fields pane on the right, click on the table you want to add the new column to. Let's say our table is called SalesData.

Step 3: Click 'New Column'

With your table selected, go to the Table tools or Column tools tab in the ribbon at the top of the screen. Click on the New column button. A formula bar will appear above the data grid, similar to the one in Excel.

Step 4: Write Your DAX Formula

This is where you define your new column. The basic syntax is ColumnName = [Formula]. Start typing, and Power BI's IntelliSense will suggest functions and column names to help you.

DAX Calculated Column Examples

Example 1: Basic Math - Creating a Profit Column

This is the most common use case. We want to calculate the profit for each sale.

In the formula bar, type:

Profit = SalesData[Revenue] - SalesData[Cost]

Press Enter. Power BI will instantly calculate the profit for every single row in your SalesData table and display a new column named Profit. Notice how you specify the table name followed by the column name in square brackets.

Example 2: Text Manipulation - Creating a Full Name

Imagine you have a Customers table with FirstName and LastName columns. Creating a full name column makes your reports and charts cleaner.

Create a new column and type:

FullName = Customers[FirstName] & " " & Customers[LastName]

The ampersand (&) is used to concatenate (join) text. We've added a space enclosed in double quotes " " to separate the first and last names. Now you have a clean FullName column you can use in customer lists or report filters.

Example 3: Conditional Logic with IF

Let's categorize our sales into "Large" and "Small" based on revenue. We can use the DAX IF function for this.

In the SalesData table, create a new column with this formula:

Deal Size = IF(SalesData[Revenue] > 500, "Large Deal", "Small Deal")

This formula checks each row. If the Revenue is greater than 500, it assigns the value "Large Deal", otherwise, it assigns "Small Deal". This kind of categorical column is incredibly useful for creating summaries and visualizations that segment your data.

Method 2: How to Add a Custom Column in Power Query

Now, let's explore creating a Custom Column, which is perfect for pre-processing your data before it even hits your report.

Step 1: Open the Power Query Editor

From the Power BI Desktop Home tab, click the Transform data button. This will launch the Power Query Editor, a separate window where you handle all data preparation tasks.

Step 2: Select the Query

On the left side of the Power Query Editor, in the Queries pane, select the query (the Power Query term for a table) where you want to add the column. Let's stick with our SalesData example.

Step 3: Go to the 'Add Column' Tab

In the ribbon at the top of the Power Query Editor, click on the Add Column tab. You'll see various options for adding new columns here. We'll focus on the most flexible one: Custom Column.

Step 4: Configure Your New Column

Clicking Custom Column opens a new dialog box. Here, you'll:

1. Enter a New column Name.
2. Type your formula into the Custom column formula box. You can double-click columns from the Available columns list on the right to insert them into your formula, which helps avoid typos.
3. Power Query will check your expression at the bottom, notifying you of any syntax errors before you even click 'OK'.

Power Query Custom Column Examples

Example 1: Extracting the Year from a Date

Suppose you have an OrderDate column and want to create a separate Order Year column for analysis. This is a classic Power Query task.

In the Custom Column window, enter:

• New column name: OrderYear
• Custom column formula:

= Date.Year([OrderDate])

Click OK. You've now added a column with just the year from each order date, which is much better for performance than calculating it on-the-fly with DAX for large data sets.

Example 2: Conditional Logic with if then else

Let's classify sales based on ProductCategory. This is the M language equivalent of an IF statement. The syntax is a bit different: if [condition] then [outcome] else [other outcome].

For example, to create a higher-level grouping:

• New column name: Department
• Custom column formula:

if [ProductCategory] = "Electronics" or [ProductCategory] = "Appliances" then "Tech" else "General Goods"

This formula adds a Department collection that groups products, all before the data gets loaded.

Example 3: Combining Fields Conditionally

Sometimes you want to create a unique identifier, but some information may be missing. For example, you want a location identifier combining City and State, but sometimes City might be null.

• New column name: LocationID
• Custom column formula:

= [State] & " - " & (if [City] <> null then [City] else "N/A")

This formula creates a clean ID string, handling cases where City is missing by substituting "N/A," preventing errors and keeping your data tidy.

After creating your columns in Power Query, just click Close & Apply in the Home tab to load your changes back into your Power BI report.

Tips and Best Practices

• Remember Your Use Case: Use DAX for dynamic analysis and report-level logic, Power Query for static, upfront data preparation.
• Choose Descriptive Names: Don't leave names like "Custom" or "CalculatedColumn". Name them logically (UserCategory, ProfitMargin) so your teammates (and your future self) understand their purpose.
• Consider Performance: For extremely large datasets, pre-calculating columns in Power Query is almost always faster than doing complex, row-by-row DAX formulas across millions of rows on report refresh.
• Learn Both Languages: You don't have to be an expert at first, but familiarity with the basics of both DAX and M will make you incredibly efficient and flexible in Power BI.

Final Thoughts

Adding columns with formulas in both Power BI's DAX engine and Power Query Editor are core skills for transforming raw data into clear, decision-driving insights. Knowing when to use a Calculated Column for dynamic analysis versus a Custom Column for data preparation will improve not only your reporting abilities but also your model's performance.

While these techniques are incredibly powerful, they also highlight the learning curve associated with powerful BI tools. You have to learn DAX syntax, understand how M formulas work, and spend time debugging your expressions. At Graphed , we aimed to eliminate that complexity by letting you build reports using simple, natural language. Instead of memorizing IF statements or how to extract a year from a date, you can just ask, "Show me my sales by region grouped as Tech or General Goods" and our AI generates these categories and visuals on the fly. We put the focus back on your questions, not on the code needed to answer them.