Methods to Split Columns in Power Query

Power Query has features to split columns by delimiter, number of characters, position along with several other options that gives developers a high level of control when splitting columns into multiple columns without having to write any special formulas.

Splitting columns in Power Query is quick and easy by delimiter, position, special character, and more.

The ability to quickly and efficiently split columns in Power Query is an essential part of data transformation, and a necessary one since Microsoft removed the classic import wizard from being automatically enabled in recent versions of Excel.

We’ll explain how to split columns, work with delimiters, and how to extract specific subsets of text as an alternative to having to split columns when you only want to extract a specific part of a string.

Let’s jump in!

Methods for Splitting Columns in Power Query

Power Query is a highly flexible data transformation tool from Microsoft that’s available in Excel, Power BI, Data Wrangler and Data Flows Gen2. It’s mostly a point and click interface with the most common functionalities available with buttons and menus.

As we cover the most common scenarios for splitting columns, keep in mind that if you’re working with complex datasets where the formatting doesn’t match a common scenario, there is still typically a way to split columns into the format needed but it may take some creativity.

Splitting Columns by Delimiter in Power Query

To split a column by delimiter in Power Query, select the column you wish to split then go to the “Transform” tab, and choose “Split Column” by “Delimiter”, choose a delimiter and define at which occurrence of the delimiter you want a new column to be created.

Within the Split Column dropdown on the Power Query ribbon there are a number of additional options to keep in mind, such as splitting by number of characters, positions, and common presets such as changes in title case or digit to non-digit chacters.

The Power Query Split Column by Delimiter button on the Power Query Ribbon

Once you select to Split Column by Delimiter, the Split Column by Delimiter dialog box will appear which gives a number of options to fine tune the splitting process.

While the most common delimiter is a comma, you can select spaces, tabs, semi-colons or define custom or special characters.

Split column by delimiter options screen

Additional options include defining how often to split a column based on the Split at section, and the ability to define a quote character.

After you press OK, a single column will be split into 3 separate columns.

Example of a single column split into 3 columns based on a comma

Quote Characters and Splitting CSV Files

When working with CSV files that are comma delimited, pay attention to the Quote Character. CSV files or Comma Separated Values are files where each individual column value is separated by a comma. However, problems arise when text data within a column also contains a comma.

You may notice when you try to split some files into columns that 3/4 of the data lines up appropriately then all of the sudden the columns no longer line up. Often times this is because the data itself contains its own comma, such as an address.

This is where many CSV files are encoded with single or double quotes to tell the computer that an entire set of text include commas such as an address should be treated as a whole value.

Splitting Columns with New Lines and Special Characters

Power Query lets users define custom characters to split text to columns, and provides presets for common custom characters. To split a column by new lines or carriage returns, select a column, then go to “Transform”, “Split Column”, “by Delimiter” select the custom delimiter, and insert special character

Splitting a column based on new lines and special characters

When you select Custom for the delimiting Character, an option appears at the bottom of the Split Column by Delimiter dialog box where you can expand Advanced Options. From here you have the ability to “Split using special characters” where you can select a special character from a dropdown menu.

Split column by delimiter special options dialog box for special characters

Once an option is selected it will show up in the Custom value section at the top of the dialog box. Special characters in Power Query have their own syntax and are typically notated with a # at the beginning of the value.

The available special character presets in Power Query for splitting columns, Tab, Carriage Return, Line Feed, Non-breaking spaces

You can also combine this method with splitting columns into rows if you want to maintain the order of values that are combined into a single value but want one long continuous list.

Splitting Columns by Position Power Query

To split a column by position, select the column, then choose “Split Column” by “Number of Characters” from the “Home” tab. Enter the character positions where you want the split to occur. Most delimiting will start at 0 and each additional split should be separated by a comma.

Splitting columns by position in Power Query

Positions are the start of each new column. The positions typically start at character zero to delimit based on the beginning of the text string of the columns elected.

Defining set text positions in Power Query

Advanced options include the ability to switch from split to columns to split to rows.

When you press OK, the new columns will be generated by Power Query.

Example of a column split into 3 columns based on position

This approach is perfect for datasets where data follows a consistent pattern, such as fixed-length file formats. When possible, you can try and get a guide from the data source of where columns need to be split. For example, many EDI feeds have files that will explain how to delimit the incoming data in the form of a separate PDF file of documentation that can save a lot of time vs guessing and checking your way through.

To make things faster, you can also try enabling “Monospaced” fonts under the View section that makes all characters take up the same amount of width making positions easier to count.

Splitting Columns by Number of Characters in Power Query

The split columns by number of characters option will split a single column into many columns of the same width. For example, split a column every 5 letters would generate new columns after every 5 letters in the initial columns. To use the option, go to “Home”, “Split Column”, “By Positions” and enter the number of characters to split by..

Splitting Column based on Text Contains

To split a column based on Text Contains in Power Query, you will need to perform multiple steps. First, create a conditional column to determine if a column contains a specific value. Then filter the dataset based on the output of the conditional column and apply a Split Column function.

Depending on the dataset an alternative might be to try Extracting text instead of Splitting it if there is a common pattern across the different rows of data in a column to split.

Splitting Columns to Rows in Power Query

To split columns to rows, use the “Split Column” by “Delimiter” option, and then select “Advanced Options.” Choose to split into rows instead of columns. This method is useful for data that needs to be normalized or for transforming a single column of list data into multiple rows.

How to split columns to rows in Power Query

Advanced options are available under all of the different split column types within Power Query after expanding the Advanced Options section.

Power Query Split Columns vs Extract Features

Splitting columns divides one column into multiple columns or rows based on certain criteria, while extracting involves pulling out specific data points into a new column without altering the original structure. In the example below, we only want a column that contains the text between two spaces.

We could delimit the columns by space, but it would generate 3 new columns, and would have to remove 2.

An alternative is the Extract feature, where we will tell Power Query to Extract text between delimiters by going to “Transform”, “Extract”, “Text Between Delimiters”

Using the Power Query extract feature to split a column into a single new column based on the value needed

Then we will type in a space for the start delimiter and end delimiter to tell Power Query where to begin and end the parsing.

Extracting text between two delimiters for a new column

After pressing OK, the result will be the extracted text without creating a new column.

Results of extracting text instead of splitting to columns

The Extract feature is a great compliment to splitting to columns and can save you from having to go through additional Power Query steps. It’s not as powerful as RegEx, a common text parsing language but you can usually get the exact string that you need without too much hassle.

Conclusion

Splitting columns in Power Query can be done a number of different ways. The most common being delimit based on comma, but you can also use special characters such as new lines or carriage returns to split more advanced scenarios. When working with CSV files be careful to use the correct quote character to avoid having text split to columns incorrectly and not align appropriately.

Other options exist to split based on number of characters or position. If you use these methods, check if there’s any documentation for the starting file that you’re working with. Many companies will provide a PDF file that explains where to parse text otherwise you are left to guess and check which can be time consuming and annoying.

Scroll to Top