How to Split Data in Power BI

Splitting a single column into two or more is a common task in data cleaning, and Power BI makes it incredibly straightforward. You might need to turn a 'Full Name' column into separate 'First Name' and 'Last Name' columns or break down an address field into its component parts. This tutorial will walk you through exactly how to split data in Power BI using the Power Query Editor, an essential skill for anyone serious about building useful reports.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why You Need to Split Columns in Power BI

Before jumping into the steps, it helps to understand why this is such a fundamental part of data preparation. Raw data rarely comes in a perfectly structured format ready for analysis. Often, multiple pieces of information are condensed into a single column, which limits your ability to filter, sort, and visualize your data effectively.

Here are a few common scenarios where splitting columns is a lifesaver:

• Names: A 'Full Name' column like "Sarah Connor" is less flexible than separate 'First Name' ("Sarah") and 'Last Name' ("Connor") columns. Splitting them lets you sort by last name or analyze sales performance by first name.
• Addresses: An address string together in one line, like "123 Main St, Springfield, IL 62704", needs to be broken apart. Separating it into 'Street', 'City', 'State', and 'Zip Code' lets you create map visualizations or analyze data by geographic region.
• Product Codes: A product SKU such as "TEE-SM-BL-042" might contain codes for 'Category', 'Size', and 'Color'. Splitting this allows you to analyze sales by size or color without complex formulas.
• Dates and Times: If you have a column with both date and time (e.g., "2023-10-27 09:30:00"), you might want to split them to analyze activity by time of day separately from the date.
• Campaign Tracking Data: A URL parameter like "utm_source=google&utm_medium=cpc" needs to be split by the "&" and "=" delimiters to analyze which traffic sources and types are performing best.

By breaking these compound columns down, you make your data model more robust and your reports more powerful.

Entering the Power Query Editor

All data transformation, including splitting columns, happens in the Power Query Editor. This is the behind-the-scenes engine where you clean and prepare your data before it gets loaded into your Power BI report.

To access it, open your Power BI Desktop file and follow this simple step:

• On the Home tab of the ribbon at the top of the screen, click the Transform Data button. This will launch a new window - the Power Query Editor.
• Here, you'll see a preview of all your data tables. Now you're ready to start splitting columns.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 1: Splitting Columns by Delimiter

The most common method for splitting columns is by using a delimiter. A delimiter is simply a character that separates your data - like a space, a comma, a dash, or a semicolon.

Let's walk through a classic example: splitting a “Contact Name” column containing names like "Maria Anders" and "Ana Trujillo" into first and last names.

Step-by-Step Instructions

1. Select Your Column

In the Power Query Editor, find the table containing the column you want to split. Click on the header of the column to select it. In our case, it's the “Contact Name” column.

2. Find the "Split Column" Tool

With the column selected, navigate to the Home tab in the Power Query Editor ribbon. In the 'Transform' section, you'll see a Split Column button. Click on it, and a dropdown menu will appear. Select By Delimiter.

(Alternatively, you can also find this command under the Transform tab).

3. Configure the Split Settings

A new window will pop up with several options:

• Select or enter delimiter: Power BI is usually smart enough to detect common delimiters like a space and suggest it. You can choose from the dropdown (comma, equals sign, semicolon, etc.) or enter a custom one. For our "Contact Name" example, Space is the correct choice.
• Split at: This setting is very important. It tells Power BI how to handle the delimiter. You have three main choices:

For our basic first/last name scenario, "Left-most delimiter" is the simplest and best option.

Click OK to apply the changes.

4. Rename Your New Columns

Power Query will execute the split and create new columns, usually named with a suffix like Contact Name.1 and Contact Name.2. This isn't very descriptive.

Double-click on each new column header and type a more meaningful name, like "First Name" and "Last Name". Press Enter to save the name.

And that's it! Your single name column has been cleanly split into two usable columns.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: Splitting Columns by Number of Characters

Sometimes your data isn't separated by a character but by a fixed length. For example, an order ID like "CA-2021-152156" might have a two-letter country code, a four-digit year, and a six-digit unique identifier. You can split this by a number of characters.

Step-by-Step Instructions

1. Select the Column

Just like before, find and select the header of the column you want to split. We'll use a "Product Code" column with values formatted like "FR-R-905" (Category, Type, ID).

2. Choose "By Number of Characters"

Select the column, go to the Split Column menu in the ribbon, and this time choose By Number of Characters.

3. Configure Your Split

The configuration window for this method is a bit different:

• Number of characters: Enter the number of characters where the first split should occur. To extract the "FR" category code, we'll enter 2.
• Split:

Let's use 2 and Once, as far left as possible to extract the category.

Click OK. You'll now have a column with "FR" and another column with the remainder, "-R-905". You can then continue this process on the remaining column, splitting by a delimiter ('-') to further break it down.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Fix Mistakes: Using the "Applied Steps" Pane

What if you make a mistake? Perhaps you chose the right-most delimiter instead of the left-most. Undoing your work in Power Query is simple.

On the right side of the Power Query Editor, you'll see a pane called APPLIED STEPS. This is a list of every single transformation you've made to your data. To undo a split, just find the step labeled something like "Split Column by Delimiter" and click the little "X" icon next to it. Your data will instantly revert to how it was before that step, with no harm done.

This feature makes it safe to experiment because you can always go back one step at a time.

A Few Final Tips for Clean Data

To avoid common frustrations when splitting data, keep these two extra tips in mind:

1. Watch for Leading/Trailing Spaces: Messy data often has extra, invisible spaces at the beginning or end of text. This can stop a delimiter split from working correctly. Before you split, it's a good practice to right-click your column, go to Transform, and select Trim. This removes any leading or trailing whitespace.
2. Consider Data Types: After you split a column, Power BI tries to guess the data type. Sometimes it gets it wrong. For example, a numeric code like a Zip Code might be interpreted as a number to be summed. Make sure to click the icon on the left of the column header (e.g., "ABC" for Text, "123" for Whole Number) and set the correct data type.

Final Thoughts

Splitting columns in Power Query is an essential skill for cleaning up your data and preparing it for meaningful analysis. Whether you're separating names by a space delimiter or breaking down complex IDs by character count, it's the key to unlocking the detailed insights hidden in your dataset.

While mastering steps like this in Power BI is rewarding, it's often just one part of a much larger reporting process. We built Graphed because we believe getting answers from your data shouldn't require so many manual steps. Instead of pulling data into a tool and cleaning it column by column, Graphed allows you to simply connect your data sources - like Google Analytics, Shopify, or Salesforce - and use plain English to build the dashboards you need. It automates the data prep so you can jump straight to asking questions and getting real-time insights in seconds, not hours.