How to Create a Custom Column in Power BI

Adding a custom column to your data in Power BI is one of the most practical skills you can learn. While your raw data is a great starting point, the real insights often come from calculating new metrics, categorizing information, or combining fields to make sense of it all. This guide will walk you through exactly how and when to create custom columns using Power BI’s two primary methods: the straightforward Power Query Editor and the powerful DAX language.

Why Create a Custom Column in the First Place?

Your data sources — whether it's Google Analytics, a CRM like Salesforce, or a simple spreadsheet — rarely contain every single metric you need in the perfect format. Custom columns let you bridge that gap by creating new data from your existing data. It's how you move from just reporting numbers to telling a story with them.

Common scenarios where custom columns are essential include:

• Calculating New Metrics: Your sales data has a Revenue and a Cost column, but you want to analyze Profit Margin. A custom column can calculate ([Revenue] - [Cost]) / [Revenue] for every single row.
• Categorizing Data: You want to group customers into tiers like "High Value," "Medium Value," and "Low Value" based on their lifetime spending. A conditional custom column can automatically assign these labels.
• Combining Text: Your contact data has separate FirstName and LastName columns. A custom column can concatenate them into a single FullName column for easier use in charts and tables.
• Creating Flags or Indicators: You need to flag all sales transactions that occurred on a weekend. A custom column can check the weekday of a date and output "Weekend" or "Weekday."

Essentially, custom columns enrich your dataset, making your analysis more flexible and tailored to your specific business questions.

Method 1: Create a Custom Column in Power Query Editor (ETL - The "Add Column" Feature)

The Power Query Editor is Power BI’s engine for extracting, transforming, and loading (ETL) data. Creating a column here is ideal when the calculation is a permanent part of your data preparation process. The new column is generated when your data is refreshed and then stored in your data model, making it efficient for "pre-shaping" your data before you start building visuals.

Follow these steps to create a custom column here:

Step 1: Open Power Query Editor

From the main Power BI Desktop window, go to the Home ribbon and click on Transform data. This will launch the Power Query Editor in a new window, showing all the queries (data tables) in your report.

Step 2: Navigate to "Add Column"

After selecting the query (table) where you want to add the new column, click on the Add Column tab in the ribbon at the top. Here, you'll see a variety of options, but for our purpose, we’ll focus on the Custom Column button.

Step 3: Define Your Custom Column Formula

Clicking Custom Column opens a dialog box. This is where you'll define your new field. The window has three main parts:

• New column name: Give your new column a descriptive name (e.g., "Profit" or "CustomerTier").
• Available columns: A list on the right shows all columns in your current table. You can double-click a column name to add it to your formula.
• Custom column formula: This is where you write the logic for your new column using the Power Query M language.

Example 1: A Simple Financial Calculation

Let's create a "Profit" column from existing "Unit Price" and "Unit Cost" columns. We also have a "Units Sold" column.

• Name the column "Profit."
• In the formula box, type:

( [Unit Price] - [Unit Cost] ) * [Units Sold]

• Power BI will show "No syntax errors have been detected" at the bottom if the formula is valid.
• Click OK.

Your new "Profit" column will appear at the far right of your table. Remember to set its data type (e.g., to Fixed decimal number or Currency) by clicking the icon in the column header.

Example 2: Text Concatenation

Let's combine "FirstName" and "LastName" into a "FullName" column.

• Name the new column "FullName."
• In the formula box, type:

[FirstName] & " " & [LastName]

• The ampersand (&) is used to join text strings. We added " " in the middle to insert a space.
• Click OK.

Example 3: Conditional Logic with If-Then-Else

Now, let's create a "Deal Size" column based on the value in a "Revenue" column. We'll label any revenue over $1,000 as "Large Deal."

• Name the new column "Deal Size."
• In the formula box, type:

if [Revenue] > 1000 then "Large Deal" else "Small Deal"

• This simple statement checks each row. If the revenue for that row is greater than 1000, it writes "Large Deal", otherwise, it writes "Small Deal."
• Click OK. Set the data type to Text.

Step 4: Close and Apply

Once you've added all your custom columns in Power Query, click on Close & Apply in the Home ribbon. This saves your transformations and loads the newly shaped data, including your new columns, into your Power BI data model.

Method 2: Create a Calculated Column with DAX

DAX (Data Analysis Expressions) is the formula language used throughout Power BI for creating calculations. Unlike Power Query columns which are computed during the data refresh, DAX calculated columns are computed row-by-row within the data model. This is useful when your calculation depends on the context of other data or tables in your model, or when you need it to be dynamic based on your report filters.

Follow these steps to create a calculated column with DAX:

Step 1: Open the Data View

In the main Power BI Desktop screen, click on the Data view icon on the left-hand navigation pane (it looks like a small spreadsheet).

Step 2: Select the Table and Create a New Column

In the Fields pane on the right, select the table you want to modify. Then, navigate to the Column tools tab in the ribbon and click New column.

Step 3: Write Your DAX Formula

A formula bar will appear above the data grid, similar to Excel. Here, you'll write your DAX expression.

The syntax for a DAX calculated column is:

Column Name = [Your DAX Expression]

Note that in DAX, you typically reference columns using the format 'TableName'[ColumnName].

DAX Example 1: Replicating the Profit Calculation

Let’s recreate the same "Profit" calculation, but using DAX.

• In the formula bar, type:

Profit_DAX = ('Sales'[Unit Price] - 'Sales'[Unit Cost]) * 'Sales'[Units Sold]

Press Enter. A new column named "Profit_DAX" will appear in your table.

DAX Example 2: More Advanced Conditional Logic with SWITCH

DAX is especially powerful for more complex logic. Let's create a "Priority" column based on country. Instead of a nested IF statement, we can use the cleaner SWITCH function.

Customer Priority = 
SWITCH(
    'Customers'[Country],
    "USA", "High",
    "Canada", "High",
    "Germany", "Medium",
    "Low" 
)

This formula checks the 'Customers'[Country] column. If it finds "USA" or "Canada," it returns "High." If it finds "Germany," it returns "Medium." For everything else, it returns the default value of "Low."

DAX vs. Power Query: Which One Should I Use?

Both methods will give you a new column, but they work quite differently and have distinct advantages. Choosing the right one is key to building an efficient report.

Use a Custom Column (Power Query M language) when:

• You are doing one-time data transformation and cleaning. Things like splitting columns, fixing data types alongside the new column, etc.
• The calculation is simple and evaluates independently for each row, without needing context from other tables.
• You want the calculation to happen during the scheduled data refresh, not in real-time. This can improve report performance.
• Your calculation is based on data from a non-supported data source for DirectQuery (as the logic is applied upstream).
• A key benefit: Columns created in Power Query are better compressed in the data model, which reduces your PBIX file size and can speed up performance.

Use a Calculated Column (DAX) when:

• The calculation depends on other DAX measures or data from other tables using filter context (e.g., using functions like CALCULATE or RELATED).
• Your calculation needs to be more dynamic and respond to what a user is filtering within the report.
• You are in DirectQuery mode and can’t modify the data in the Power Query Editor.
• A key drawback: Calculated columns are stored in memory just like other data, so adding many complex DAX columns can significantly increase your file size and slow down your report.

As a rule of thumb: If you can create the column in Power Query Editor, it's usually the better, more performant option. Reserve DAX for calculations that truly require its row-context evaluation powers.

Final Thoughts

You’ve seen how to create custom columns in Power BI using both the user-friendly Power Query Editor and the powerful DAX language. This skill allows you to transform static data into dynamic insights, whether by calculating profitability, segmenting customers, or preparing fields for better visualizations. Pushing your data beyond its original state is fundamental to building reports that truly inform decision-making.

While mastering tools like Power BI is a valuable skill, it often involves a steep learning curve and wrestling with formula syntax. Sometimes you just need an answer fast. We built Graphed because we believe analyzing your marketing and sales data shouldn’t require you to become a data engineer. By connecting your sources, you can create real-time dashboards and get answers simply by asking questions in plain English — no custom formulas required.