Split Column in Power Query | Power BI Tutorial

What is Split Column?

Split Column is a data transformation feature in Power Query that divides the values in a single column into multiple columns based on a delimiter, number of characters, or specific position.

It is commonly used to separate combined data such as names, addresses, product codes, and dates, making the dataset cleaner and easier to analyze.

In Simple Terms: Split Column = Divide one column into multiple columns.

Before & After Example

Before Split Column

Full Name
John Smith
Emma Watson
David Miller

After Split Column

First Name	Last Name
John	Smith
Emma	Watson
David	Miller

Types of Split Column

• By Delimiter—Split using separators such as comma, space, hyphen, slash, or semicolon.
• By Number of Characters – Split after a fixed number of characters.
• By Positions — Split at one or more specified character positions.
• By Lowercase to Uppercase – Split text when the character case changes.
• By Digit to Non-Digit – Separate letters from numbers.
• Into Rows – Split values into multiple rows instead of columns.

Steps to Split a Column

1. Open Power Query Editor.
2. Select the column to split.
3. Go to the Transform tab.
4. Click Split Column.
5. Select the appropriate split method.
6. Configure the split options.
7. Click OK.
8. Review the transformed data.
9. Click Close & Apply.

Real-World Examples

Scenario	Before	After
Customer Name	John Smith	John | Smith
Email Address	john@gmail.com	john | gmail.com
Product Code	PRD-1001	PRD | 1001
Address	Chennai, Tamil Nadu, India	Chennai | Tamil Nadu | India

Difference Between Split Column and Extract

Feature	Split Column	Extract
Purpose	Divides one column into multiple columns	Extracts part of the text
Output	Multiple columns	Single column
Best For	Separating combined values	Retrieving specific characters or text
Example	John Smith → John | Smith	John Smith → John

Advantages of Split Column

• Improves data quality.
• Makes datasets easier to analyze.
• Separates combined values into meaningful fields.
• Reduces manual data cleaning.
• Prepares data for reporting and visualization.

Limitations

• Requires a consistent delimiter or pattern.
• Incorrect split settings may produce inaccurate results.
• Additional transformations may be required after splitting.
• Works best with well-structured data.

Best Practices

• Choose the correct split method based on your data.
• Preview the output before applying the transformation.
• Rename the new columns with meaningful names.
• Validate the split results for accuracy.
• Remove unnecessary columns after splitting.