How to Split Column in Power BI Using DAX

Splitting a single column into multiple columns is one of the most common data cleanup tasks you'll face. Your data might contain a 'Full Name' field that you need separated into 'First Name' and 'Last Name', or a product SKU that bundles information like size and color. This guide will walk you through how to split a column in Power BI specifically using DAX, giving you the power to create dynamic, calculated columns directly in your data model.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, Why Would You Need to Split a Column?

Working with clean, well-structured data is fundamental to good analysis. Often, raw data comes in a less-than-ideal format. Splitting columns helps you turn that raw data into usable information for your reports and dashboards. Here are a few common scenarios where this is essential:

• Names: Separating a "Full Name" column into "First Name" and "Last Name" for personalization or easier sorting.
• Addresses: Breaking down a full address into Street, City, State, and Zip Code for geographic analysis and mapping.
• Product Information: Extracting parts of a Product ID or SKU, like "TS-Blu-Lrg," into separate columns for "Type," "Color," and "Size."
• URLs: Parsing website URLs to isolate the domain, subdirectory, or specific page paths for traffic analysis.
• Dates & Times: Splitting a single timestamp column (e.g., "2024-07-16 09:30:00") into separate "Date" and "Time" columns.

In all these cases, splitting the original column makes your data more flexible, allowing you to filter, sort, and analyze your reports with greater control.

DAX vs. Power Query: A Quick Comparison

Before diving into the DAX formulas, it's important to know that Power BI gives you two primary ways to split columns: Power Query and DAX.

Power Query is Power BI's data transformation engine. It has a user-friendly, built-in feature called "Split Column by Delimiter" that works beautifully for straightforward, one-time data cleanup while you're importing or shaping your data. It's the go-to choice for permanent transformations before your data is even loaded into the model.

DAX (Data Analysis Expressions), on the other hand, is the formula language used for creating calculations within your data model after the data is loaded. Using DAX to split columns creates what are called calculated columns. This method is incredibly powerful because it's dynamic, flexible, and allows for more complex logic than the standard Power Query split feature. This tutorial will focus exclusively on the DAX method.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Key DAX Functions for Splitting Columns

To split text in a column using DAX, you'll need to get comfortable with a handful of text manipulation functions. Let's get to know our toolset.

• `LEFT(text, num_chars)`: This function returns the specified number of characters from the start (left side) of a text string.
• `RIGHT(text, num_chars)`: Just like LEFT, but it returns characters from the end (right side) of a text string.
• `LEN(text)`: A simple one, this function returns the total number of characters in a text string (its length).
• `SEARCH(find_text, within_text, [start_num], [not_found_value])`: This is your workhorse for finding your separator, or "delimiter." It searches for a character (like a space " " or a dash "-") within a text string and returns its starting position as a number. Crucially, SEARCH is not case-sensitive.
• `FIND(find_text, within_text, [start_num], [not_found_value])`: Very similar to SEARCH, but it's case-sensitive. For most column-splitting tasks, SEARCH is safer and more reliable.

By combining these functions, you can slice and dice text strings with precision.

Step-by-Step Guide: Splitting a "Full Name" Column

Let’s walk through the most common example: separating a 'Full Name' column into 'First Name' and 'Last Name'. Imagine you have a table called `Employees` with a column named `[FullName]` containing values like "Arya Stark" and "Jaime Lannister."

Our goal is to create two new calculated columns: `[FirstName]` and `[LastName]`.

Step 1: Create the 'FirstName' Column

The first name is all the text before the first space. So, we'll need to find the position of that space and then grab everything to the left of it.

1. Navigate to the Data View in Power BI and select your `Employees` table.
2. From the "Table tools" ribbon, click "New column".
3. In the formula bar, enter the following DAX formula:

FirstName = LEFT(Employees[FullName], SEARCH(" ", Employees[FullName]) - 1)

Let's break down how this works from the inside out for "Arya Stark":

• SEARCH(" ", Employees[FullName]) finds the position of the first space. For "Arya Stark," it returns the number 5 (since the space is the 5th character).
• We don't want the space itself, so we subtract 1 from that result (5 - 1 = 4).
• LEFT(Employees[FullName], 4) then tells Power BI to take the first 4 characters from the left of the `[FullName]` column.
• The result is "Arya", which is exactly what we want.

Press Enter, and you'll see your new `[FirstName]` column appear.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Create the 'LastName' Column

The last name is a little trickier. We need to find the space and then grab everything to the right of it. This requires us to know the total length of the name.

1. Click "New column" again.
2. In the formula bar, enter this DAX formula:

LastName = RIGHT(Employees[FullName], LEN(Employees[FullName]) - SEARCH(" ", Employees[FullName]))

Again, let's break this down for "Arya Stark":

• LEN(Employees[FullName]) calculates the total length of "Arya Stark," which is 10 characters.
• SEARCH(" ", Employees[FullName]), as before, finds the position of the space, which is 5.
• The formula then subtracts the position number from the total length (10 - 5 = 5). This number tells us how many characters are in the last name.
• RIGHT(Employees[FullName], 5) tells Power BI to grab the last 5 characters from the right of the string.
• The result is "Stark".

You now have two clean, separate columns for first and last names, ready for your report!

Advanced Scenarios: Handling Errors and Multiple Delimiters

Real-world data is rarely perfect. What if some names don't have a space? Or what if you have multiple delimiters?

Handling Missing Delimiters with IFERROR

If a name in your `[FullName]` column is just "Cersei" (with no last name), the formulas above will break because SEARCH won't find a space and will return an error. You can bake in error-handling logic with the `IFERROR` function to handle these cases gracefully.

For the `FirstName` formula:

FirstName = IFERROR(LEFT(Employees[FullName], SEARCH(" ", Employees[FullName]) - 1), Employees[FullName])

This formula tries to execute the split. If it succeeds, it returns the first name. If it fails (because there's no space), it simply returns the original `[FullName]`, which is the correct behavior.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The PATHITEM Trick: Splitting a Column with Multiple Sections

Imagine a `[ProductCategory]` column with data like "Apparel-Tops-Shirts" or "HomeGoods-Kitchen-Utensils". Using multiple LEFT/RIGHT/SEARCH formulas would be a nightmare. There's a much more elegant DAX function for this: `PATHITEM`.

The `PATHITEM` function is designed to parse text that's structured like a file path, separated by pipes "|". But we can cleverly trick it into working with any delimiter by first using `SUBSTITUTE`.

Let's create three new columns: `[MainCategory]`, `[SubCategory]`, and `[ItemType]`.

1. First, create a 'helper' column to replace the dashes with pipes. Call it `[CategoryPath]`.

CategoryPath = SUBSTITUTE(Products[ProductCategory], "-", "|")

This will turn "Apparel-Tops-Shirts" into "Apparel|Tops|Shirts".

1. Now, use `PATHITEM` on this helper column to pull out each piece.

Create your `[MainCategory]` column:

MainCategory = PATHITEM(Products[CategoryPath], 1)

Create your `[SubCategory]` column:

SubCategory = PATHITEM(Products[CategoryPath], 2)

And finally, your `[ItemType]` column:

ItemType = PATHITEM(Products[CategoryPath], 3)

The second argument in `PATHITEM` (the number) indicates which item in the "path" you want to extract. This method is incredibly clean and scalable for columns with a consistent structure and multiple parts.

Final Thoughts

Splitting columns is a fundamental step in data preparation, and DAX provides a versatile toolkit to handle both simple and complex situations. By mastering functions like `LEFT`, `RIGHT`, `SEARCH`, and the powerful `PATHITEM` function, you build a model that's cleaner, more organized, and ready for insightful analysis.

Learning how to wrangle DAX is a valuable skill, but for many day-to-day business questions, you don't always want to spend your time writing and testing formulas. This is where we designed Graphed to simplify the entire reporting process. Instead of manually creating calculated columns, you can connect your data sources and simply ask in plain English for the chart you need - like, "show me sales by First Name" - and our AI data analyst builds it for you instantly, bypassing the hours spent on tedious data prep.