How to Split a Column in Power BI

Cleaning up messy data is one of the most common tasks you'll face in Power BI, and knowing how to split a single column into multiple, useful columns is a fundamental skill. Whether you're dealing with full names, addresses, or complex product codes, Power BI's Power Query Editor gives you several powerful, yet straightforward, ways to get the job done. This guide will walk you through the most common methods for splitting columns, from simple delimiters to more advanced techniques.

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

First Things First: Getting to the Power Query Editor

All data transformation in Power BI happens within the Power Query Editor. It's a separate window from your main Power BI Desktop dashboarding view. Before you can split any columns, you need to open it.

Here’s how to get there:

1. From the main Power BI Desktop window, go to the Home tab in the ribbon.
2. Look for the Queries section and click on the Transform data button.

That's it. This will open the Power Query Editor, where you'll see a preview of your data tables. Now you're ready to start cleaning your data.

Method 1: The Classic Split by Delimiter

Splitting a column by a delimiter is the most frequent scenario you'll encounter. A delimiter is simply the character that separates the different pieces of information you want to split. Common delimiters include commas, spaces, hyphens, and slashes.

Let's use a very common example: a "Full Name" column that you want to split into "First Name" and "Last Name".

Example Data:

• John Smith
• Maria Garcia
• Chen Wang

Step-by-Step Instructions

1. In the Power Query Editor, find and click on the header of the column you want to split (in our case, the "Full Name" column). This will select the entire column.
2. Navigate to the Home tab in Power Query’s ribbon. Click the Split Column button. A dropdown menu appears.
3. From the dropdown, select By Delimiter.
4. A new window will pop up with several options. Power BI is usually smart enough to automatically detect common delimiters like a space, but let’s review the settings.
5. Click OK.

Your single "Full Name" column will now be replaced by two columns, likely named "Full Name.1" and "Full Name.2". Power Query even records this action in the "Applied Steps" pane on the right, so you can easily undo it if you make a mistake.

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

Pro Tip: Rename Your New Columns

For your data model to make sense, you should always rename your new columns. Simply double-click on the new column headers ("Full Name.1" and "Full Name.2") and type in more descriptive names like "First Name" and "Last Name".

Method 2: Split by Number of Characters

Sometimes your data doesn't have a neat delimiter, but it does follow a consistent structure based on the length of its parts. This is common with product codes, serial numbers, or order IDs where the first few characters always represent the same thing (e.g., a country code, a year, a factory region).

Example Data: Imagine you have an "OrderID" column where the first two characters are the year of the order, and the rest is a unique ID.

• 21-987654
• 22-100234
• 23-225987

This data has a delimiter (-), but let's pretend it doesn't and the values are 21987654, 22100234, and 23225987. We know the first two characters always represent the year.

Step-by-Step Instructions

1. Select the "OrderID" column.
2. Go to the Split Column dropdown menu, but this time select By Number of Characters.
3. A dialog box will appear.
4. Click OK.

Your "OrderID" column is now split into "Year Code" and "Unique ID", which you should rename accordingly.

Method 3: Advanced Splits for Tricky Data

Sometimes your data format is even more complex. Power Query has several other splitting options hidden in the "Split Column" menu that are incredibly useful in these situations.

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

Split by Positions

This method is similar to splitting by a number of characters but offers more precision. It's perfect for when the segments have variable lengths but always start at the same character positions.

Example Data: A logistics code CA-TSHIRT-L-115, where the first two characters are the country, the next section is the product size, and the last part is a warehouse ID.

• CA-TSHIRT-L-115
• US-SWEATER-M-204
• DE-JACKET-XL-80

Here, the product name has a variable length ("TSHIRT", "SWEATER", "JACKET"). Splitting by a fixed number of characters won't work, and splitting by the delimiter - will give you too many columns. We want to extract the country code, CA, and the rest. We use By Positions.

You’d specify the starting positions of each new column. In Power Query, 0 is the start. So you would enter 0,2 representing two splits: one at the very beginning (position 0), and another starting after the second character (position 2). This would yield "CA" and "-TSHIRT-L-115". From there you might perform further cleaning on the second column, if needed.

Split by Digit to Non-Digit

This is a lifesaver when you have numbers and text smashed together without any separators. Think of street addresses or product names with model numbers.

Example Data: A column of product names and versions.

• ProductASeries3
• SuperWidgetV2
• GeneratorModel4000

You want to separate the text from the numbers.

1. Select the column.
2. Go to Split Column > By Non-Digit to Digit.
3. Power Query will immediately split the column right before the first digit it finds in each cell.

There's also the inverse: By Digit to Non-Digit. If you had data like 2024FiscalReport, this option would split it into "2024" and "FiscalReport".

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

Bonus Tip: Split into Rows Instead of Columns

Sometimes your data is structured with multiple values in a single cell, and it makes more sense to create new rows for each value rather than new columns. A common example is blog posts or products with multiple tags in one field.

Example Data: a "Tags" column.

• powerbi, tutorial, dax
• excel, formulas, reporting
• data, analytics

Creating separate columns for each tag would be messy. Instead, you want to "unpivot" this data, so each tag gets its own row, associated with its original entry. This makes filtering and analyzing the tags much easier.

Here’s how:

1. Select the "Tags" column
2. Go to Split Column > By Delimiter.
3. Set the delimiter to Comma.
4. Here's the key step: Click on Advanced options to expand the menu.
5. Under the "Split into" section, select Rows.
6. Click OK.

Your table will now have more rows, with each tag appearing on a separate line, cleanly organized for analysis.

Final Thoughts

Splitting columns is a basic but essential part of data preparation in Power BI. Using the Power Query Editor, you can easily transform messy, combined fields into clean, individual columns using delimiters, character counts, positions, or even transitions between letters and numbers. Mastering these techniques will save you hours of manual work and make your reports far more accurate and robust.

While mastering Power Query is invaluable, these transformation steps are often just the first stage in a time-consuming reporting process. For many, that's where the next manual headache begins. After cleaning the data, you still need to build the visualizations, create the dashboards, and share them. We built Graphed to automate that entire workflow. By connecting your marketing and sales data directly, we allow you to ask questions in plain English - like "create a chart comparing Shopify revenue versus Facebook Ads spend" - and get a real-time, shareable dashboard in seconds. No more multi-step cleaning and building, just clear answers straight from your data.