How to Delimit in Power BI

Cleaning up messy data is often the most time-consuming part of any analysis. When information gets crammed into a single cell, like a full name or an entire address, it’s not very useful. This guide will walk you through exactly how to delimit data in Power BI, using the Power Query Editor to easily split one column into several more organized ones.

What Does It Mean to Delimit Data?

Think of a "delimiter" as a character that acts as a separator in your text. The most common delimiters are commas, spaces, and hyphens, but they can be any character that breaks up data into distinct pieces. For example, in the text "John,Smith", the comma is the delimiter separating the first name from the last name.

Delimiting data is simply the process of taking data from a single column and splitting it into multiple columns based on that separator. Power BI’s built-in tools make this task straightforward, turning a frustrating data cleaning challenge into a few simple clicks. This technique is fundamental for preparing your data for accurate analysis and clear visualizations.

Why Is Splitting Columns So Important?

You might wonder why you can't just leave the data as is. Splitting columns is about making your data structured and usable. Here’s why it’s a non-negotiable step:

• Improved Filtering and Sorting: You can't sort a list of client names by last name if "FirstName LastName" is all in one column. By splitting it into two separate columns (e.g., First Name and Last Name), you can easily sort and filter your data as needed.
• Better Analysis: Imagine you have an Address column that contains "City, State, Zip Code". If you want to analyze sales by state, you need a dedicated State column. Splitting the original column allows you to perform geographic analysis, count customers by city, and more.
• Data Standardization: Splitting data helps you enforce consistency. Once you have separate columns, you can clean them up more effectively - like trimming extra whitespace from a First Name column or standardizing state abbreviations.
• Enabling Visualizations: Many Power BI visuals rely on structured data. For example, the map visual needs separate columns for city, state, or country to accurately plot locations. A single, messy address column simply won’t work.

A Step-by-Step Guide to Delimiting in Power BI

The best place to handle data cleaning tasks like splitting columns is in the Power Query Editor. It’s a powerful tool within Power BI that records all your data transformation steps, so they are automatically reapplied every time you refresh your data.

Let’s use a common example: We have a column named Contact that contains a person’s first name and last name, separated by a comma (e.g., "Jane,Doe"). Our goal is to split this into two columns: FirstName and LastName.

Step 1: Open the Power Query Editor

First, you need to get into the Power Query Editor. From the main Power BI Desktop screen, look for the Transform data button on the Home tab. Clicking this will launch the Power Query Editor in a new window, showing you all the data tables in your model.

Step 2: Select the Column You Want to Split

In the Power Query Editor, find the table that contains the messy data. Click on the header of the column you want to split. In our example, we would click on the Contact column header. The column will become highlighted to show it’s selected.

Step 3: Locate the "Split Column" Feature

With the column selected, navigate to the Split Column feature. You can find this button in two places on the ribbon:

• On the Home tab, in the "Transform" section.
• On the Transform tab, in the "Text Column" section.

Click on Split Column and a dropdown menu will appear. From this menu, choose By Delimiter.

Step 4: Configure the Split Settings

A new dialog box will pop up, asking you how to perform the split. This is where you tell Power Query what to do.

1. Choose the Delimiter: Power Query is smart and will often auto-detect the delimiter for you. In our case, it should identify the comma. If it guessed wrong or your delimiter is less common (like a pipe symbol |), you can select it from the dropdown or choose "Custom" to enter any character you need.
2. Choose How to Split: The "Split at" option tells Power Query where to divide the text. You have three main choices:

• Left-most delimiter: Splits the text only at the first occurrence of the delimiter from the left. Useful for when you only want to separate the very first part of a string.
• Right-most delimiter: Splits the text at the last occurrence of the delimiter from the right.
• Each occurrence of the delimiter: This is the most common option. It will create a new column for every piece of data separated by the delimiter. For "Jane,Doe", it creates two columns. For "Red,Green,Blue", it would create three.

For our example, we will select Comma as the delimiter and choose Each occurrence of the delimiter to separate the first and last names.

Step 5: Apply the Changes

Click OK. Power Query will instantly perform the split. Your original Contact column will be replaced by two new columns, likely named Contact.1 and Contact.2.

Step 6: Rename Your New Columns

The default column names aren't very descriptive. To rename them, simply double-click on each new column header and type a new name. We'll rename Contact.1 to FirstName and Contact.2 to LastName.

Now you have clean, separate columns for first and last name, ready for analysis!

Step 7: Close and Apply

Finally, click the Close & Apply button in the top-left corner of the Power Query Editor. This will save your changes and load the newly structured data into your Power BI model.

Advanced Delimiting Options in Power BI

Splitting text into new columns is just the start. Power Query offers more advanced options for different scenarios.

Split Column by Number of Characters

What if your data doesn't have a delimiter? Sometimes data is formatted with a fixed length, like a product code where the first three characters represent the category (e.g., "VEG1001", "FRT2034").

In this case, instead of "By Delimiter," you can select Split Column > By Number of Characters. You can then specify a number of characters to split after and whether to do it once or repeatedly.

Split into Rows Instead of Columns

This is an incredibly useful feature. Imagine a single cell contains a list of items, like product tags: "Organic, Gluten-Free, Vegan". Instead of creating one column for each tag, you might want to create a new row for each tag, associating each one with the original product.

To do this, go to the advanced options in the "Split Column by Delimiter" dialog box. Under the "Split into" section, select Rows. This will transform your data from a wide format to a long format, which is often much better for filtering and analysis in Power BI.

Tips and Troubleshooting

Sometimes, data is messier than expected. Here are a few common issues and how to resolve them:

• Inconsistent Spacing: Your data might have extra spaces, like "Jane , Doe". This can disrupt the splitting process. Before you split, use the Format > Trim feature. Right-click the column, go to Transform, and select Trim to remove any leading or trailing whitespace.
• Mixed Delimiters: What if some entries use a comma while others use a semicolon? A good solution is to standardize the delimiter first. Use the Replace Values feature (on the Home tab) to replace all semicolons with commas before you split the column.
• Unexpected Blank Columns: If you see empty columns after a split, it's often due to a leading or trailing delimiter (e.g., ",Red,Green,"). Check your source data and consider applying the Trim function or cleaning up the source before splitting.

Final Thoughts

Learning how to properly delimit data is a huge step toward mastering data preparation in Power BI. By using the "Split Column" feature in Power Query, you can transform messy, unusable text into structured columns that are perfect for building insightful reports and impactful dashboards.

We believe that getting answers from your data shouldn't always require wading through cleaning steps. We built Graphed to automate the entire data prep and reporting process. Instead of manually splitting columns just to get started, you can connect your data sources, ask questions like "show me sales by state last quarter" in plain English, and have an AI analyst instantly generate the visualizations you need.