How to Separate Text in Power BI

Working with text data in Power BI often means you need to split one column into two or more. Whether you have full names that need to be separated into first and last names, product codes combined with descriptions, or addresses all clumped together, splitting text is a fundamental skill for cleaning and preparing your data. This tutorial will walk you through the most practical ways to separate text in Power BI using both the user-friendly Power Query Editor and the powerful DAX language.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Before You Start: Power Query vs. DAX

There are two primary places you can separate text in Power BI, and choosing the right one will make your life easier:

• Power Query Editor: This is the best place to perform data cleaning and transformation tasks. Changes made here happen before your data is loaded into the Power BI model. For splitting columns, this is almost always the preferred method. It's cleaner, more efficient, and easier to manage.
• DAX (Data Analysis Expressions): This is the formula language used within the Power BI data model itself to create calculated columns and measures. While you can split text with DAX, it's generally used when you need a dynamic calculation within your model rather than a one-time structural change to your data.

We'll start with the easiest and most common method: Power Query.

Method 1: Using "Split Column" in the Power Query Editor

The Power Query Editor has built-in features that make splitting text incredibly simple. It’s a visual process that doesn’t require writing any code.

Step 1: Open the Power Query Editor

First, you need to access the Power Query Editor. From the main Power BI Desktop window, go to the Home ribbon and click on the Transform data button. This will launch a new window where all the data transformations happen.

Step 2: Select the Column to Split

In the Power Query Editor, you'll see a preview of your data tables. Find the table containing the column you want to split and click on the column header to select it. The selected column will be highlighted in green.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Choose Your Splitting Method

With the column selected, navigate to the Home tab or the Transform tab in the ribbon. You’ll find a button called Split Column. Clicking this reveals a dropdown menu with several powerful options for separating your text.

Let's go through the most common methods.

Split Column By Delimiter

This is the most frequent use case. A delimiter is a character that separates your text - like a space, comma, hyphen, or slash. For example, in "John Doe," the space is the delimiter. In "SKU-12345," the hyphen is the delimiter.

1. Select your column (e.g., a "FullName" column).
2. Click Split Column > By Delimiter.
3. A dialog box will pop up. Power Query often automatically detects the delimiter (like a space). If not, you can select it from the list or enter a custom one.
4. You then choose where to split. You can split at the Left-most delimiter (only the first instance), the Right-most delimiter (only the last instance), or Each occurrence of the delimiter.
5. For a "First Name Last Name" scenario, splitting at the "Left-most delimiter" is usually sufficient and will create two columns: one for the first name and one for everything after the first space.

Click OK, and Power Query will instantly create your new columns. It will even document the step in the "Applied Steps" pane on the right, which you can edit or delete at any time.

Split Column By Number of Characters

This method is perfect when your data has a fixed structure, like splitting a state code from a zip code in a string like "CA90210".

1. Select your column.
2. Click Split Column > By Number of Characters.
3. Enter the number of characters where the first split should occur (e.g., '2' to split after "CA").
4. Choose how the split should be applied. You can split "Once, as far left as possible" or "Repeatedly". For our "CA90210" example, "Once, as far left as possible" works perfectly.

Split Column By Digit to Non-Digit (and Vice-Versa)

This is a wonderfully clever option for separating numbers from text in inconsistent strings. For example, if you have product codes like "Product101" or "Item-A-54" and you want to isolate the numbers.

1. Select your column.
2. Go to Split Column > From Digit to Non-Digit. Power BI will split the text every time a number is followed by a letter.
3. Alternatively, you can choose From Non-Digit to Digit to split where text is followed by a number.

This one-click feature can save a surprising amount of time without needing any complex logic.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Using DAX Functions to Create Calculated Columns

If you prefer to keep your original column and create new split columns directly in your data model, you can use DAX formulas. This approach adds new columns to your table without altering the source data via Power Query.

For this method, you'll be working in the Data view or Report view of Power BI Desktop, not the Power Query Editor.

Key DAX Functions for Splitting Text

To separate text, you'll often use a combination of functions to find a delimiter and then extract the text from the left, right, or middle of the string.

• LEFT(text, number_of_chars): Returns the specified number of characters from the start of a text string.
• RIGHT(text, number_of_chars): Returns the specified number of characters from the end of a text string.
• MID(text, start_position, number_of_chars): Extracts a string of a given length from the middle of another text string.
• SEARCH(find_text, within_text, [start_num], [NotFoundValue]): Finds the starting position of a text string within another text string. Crucially, it's not case-sensitive.
• LEN(text): Returns the number of characters in a text string.

Example: Splitting a Full Name with DAX

Let's use the full name "Maria Garcia" from a table named 'Employees' and in a column named '[FullName]' to create 'FirstName' and 'LastName' columns.

1. Create the 'FirstName' Column

First, we need to find the position of the space. Then, we use LEFT to extract all the characters before that space.

1. Go to the Data view in Power BI Desktop.
2. Select your 'Employees' table.
3. From the Table tools ribbon, click New column.
4. Enter the following DAX formula in the formula bar:

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

Formula Breakdown:

• SEARCH(" ", Employees[FullName]) finds the position of the first space in the "FullName" column. For "Maria Garcia", this returns 6.
• We subtract 1 (- 1) to avoid including the space itself in the result. So, we get 5.
• LEFT(Employees[FullName], 5) extracts the first 5 characters from the "FullName", giving us "Maria".

2. Create the 'LastName' Column

Extracting the last name is a bit trickier. We need to find the total length of the string and subtract the number of characters leading up through the location of the space. The RIGHT function will pull the appropriate remaining characters from the text.

1. With your table still selected, click New column again.
2. Enter this DAX formula:

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

Formula Breakdown:

• LEN(Employees[FullName]) calculates the total length of the string "Maria Garcia", which is 12.
• SEARCH(" ", Employees[FullName]) finds the position of the space, which is 6.
• 12 - 6 gives us 6. This is the number of characters in the last name (G-a-r-c-i-a).
• RIGHT(Employees[FullName], 6) extracts the last 6 characters from the string, giving us "García".

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Which Method is Best for You?

While both methods work, here's a simple guide to choosing the right one:

• Use Power Query 95% of the time. For cleaning and preparation, the Power Query "Split Column" feature is your best friend. It is more efficient for the Power BI engine, it keeps your DAX model clean, and the automated steps are transparent and easy to troubleshoot. Always try to shape your data here first.
• Use DAX for dynamic special cases. If you cannot or do not want to go back to Power Query and need to create a new, calculated column that responds to other parts of your data model (like filters or other calculations), then DAX is the right tool.

Final Thoughts

Separating text might seem like a small task, but it's a vital part of building reliable and useful reports in Power BI. Whether you use the intuitive "Split Column" tools in Power Query or the more surgical approach of DAX formulas, mastering this skill allows you to transform messy text into clean, structured columns ready for analysis.

Ultimately, data preparation is the first step toward finding insights. Many of these preparatory tasks, like splitting columns or merging data sources, can take up a lot of time. That's why we've made the process of data preparation seamless with Graphed. We connect directly to your marketing and sales data sources and let you generate dashboards and reports using simple, natural language. Instead of wrangling formulas, you can focus on asking questions and getting instant, real-time answers for your business.