How to Split Column in Power Query in Power BI

In Power BI's Power Query, you can split a column by selecting the column, then using the Split Column option from the ribbon. You can split by delimiter, number of characters, or positions to separate data into multiple columns easily.

Syntax

The main ways to split a column in Power Query are:

• Split by Delimiter: Splits text based on a character or string like comma, space, or custom text.
• Split by Number of Characters: Splits text after a fixed number of characters.
• Split by Positions: Splits text at specific character positions.

These options are available in the Power Query Editor under the Transform tab.

m

Table.SplitColumn(Source, "ColumnName", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ColumnName.1", "ColumnName.2"})

Example

This example shows how to split a column named FullName by space to separate first and last names.

m

let
    Source = Table.FromRecords({[FullName="John Doe"],[FullName="Jane Smith"]}),
    SplitColumn = Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"FirstName", "LastName"})
in
    SplitColumn

Output

FirstName | LastName
----------|---------
John      | Doe
Jane      | Smith

Common Pitfalls

Common mistakes when splitting columns include:

• Choosing the wrong delimiter, which results in unexpected splits or no splits.
• Not handling rows without the delimiter, causing errors or null values.
• Splitting columns with inconsistent data formats.

Always preview your data before and after splitting to ensure correct results.

m

/* Wrong way: Using comma delimiter on space-separated names */
let
    Source = Table.FromRecords({[FullName="John Doe"],[FullName="Jane Smith"]}),
    WrongSplit = Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"FirstName", "LastName"})
in
    WrongSplit

/* Right way: Using space delimiter */
let
    Source = Table.FromRecords({[FullName="John Doe"],[FullName="Jane Smith"]}),
    RightSplit = Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"FirstName", "LastName"})
in
    RightSplit

Quick Reference

Method	Description	Example Delimiter
Split by Delimiter	Splits text using a character or string	Comma (,), Space ( ), Semicolon (;)
Split by Number of Characters	Splits text after fixed character count	3 characters
Split by Positions	Splits text at specific character positions	Positions 2 and 5

Key Takeaways

Use the Split Column feature in Power Query under the Transform tab to separate data easily.

Choose the correct delimiter or splitting method based on your data format.

Preview data before and after splitting to avoid errors or unexpected results.

Handle rows without delimiters carefully to prevent null or error values.