In this article, we will show you everything you need to know about splitting columns by delimiters in Power Query.
We cover how to split a column or multiple columns by delimiter into rows or columns, how to split by multiple different delimiters types, and special delimiters such as carriage returns and line breaks.
How to split a column by delimiter in Power Query
If you have multiple values in cells that you want to split out into either rows or columns, you can use the ‘split column’ feature in Power Query.
The values in each cell can be split by a delimiter, e.g. comma, semicolon, etc. We will show you how you can do this in Power Query with an example.
Here’s the example data we will be using:
To split each reference into separate rows, in the ‘Home’ section on the top ribbon, we will click on the ‘Split Column’ button and choose ‘By Delimiter’.
We will then need to choose which delimiter to split the values by. In our example data, the references are separated by commas, so we will choose that.
Also, since there is more than one delimiter in each cell, we will choose to split for each occurrence of the delimiter.
After this has been applied, this is the resulting data:
The references have been split by the comma delimiter into separate rows.
If you want to split your column into multiple columns, rather than rows, when you split by delimiter, you can do so.
To do this, click on the advanced options section of the pop-up after you have chosen to split by delimiter, and choose to split into columns.
You can also specify how many columns you want to split in to.
In our example, we want to split our data into three columns since there are three references in each cell.
Here is the result:
Split by multiple delimiters
We will show you how you can split by multiple delimiter types in Power Query if your data uses different delimiters in the column.
Here is some example data where the references are now separated by colons, commas, and semicolons randomly:
To split the references into multiple rows or columns, we will complete the same steps in the previous example to split by delimiter.
It doesn’t matter which delimiter type we choose to split by initially, so we have kept it as a comma.
After you have applied this step, you will need to amend the Power Query M code and include the other delimiter types.
Firstly, we want to change the function from Splitter.SplitTextByDelimiter to Splitter.SplitTextByAnyDelimiter.
Next, we add { } parenthesis to the first parameter of the function and then add the rest of our delimiters within these using quotation marks.
Here is the Power Query M Code for our example after we have completed this:
= Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Reference", Splitter.SplitTextByAnyDelimiter({":",";",","}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Reference")The parts in bold are what we have added to the code.
Here is a screenshot of the code:
Here is the result after we have amended this step:
Split by return or new line
In Power Query, you can split values in a column by carriage return or new line.
Here’s some example data which has references we want to split after each new line:
First, we choose to split the column by delimiter.
Next, we choose to split using special characters.
There are different options you can choose from, as shown below:
In our example, we will choose to split by Line Feed. This will populate #(If) in the delimiter box at the top.
Here are the other delimiters you can choose from and how they are represented:
- Tab = #(tab)
- Carriage Return = #(cr)
- Line Feed = #(lf)
- Carriage Return and Line Feed = #(cr)#(lf)
- Non-breaking Space = #(00A0)
Here is the resulting data after we have applied this:
Split multiple columns into rows at once
In Power Query, you may have multiple columns that need to be split by a delimiter, all at once.
Here’s some example data where we want to split each column by the same delimiter:
If we use the split by delimiter feature in Power Query, for each column, this will not give us the desired result. We need to split each column in the same step.
Firstly, we will use the Text.Split function to create new three custom columns, one for each original column.
Here’s the formula we would use, swapping out the column name each time. We also need to specify the delimiter in the second part of the function, which in this case is a comma, “,”.
= Text.Split([Column],",")Once we have added the three custom columns, each one will have lists, as shown below:
We now want to add a fourth custom column, this time using the Table.FromColumns function to return tables from the lists.
Here is the formula we have used:
= Table.FromColumns({[ReferenceSplit],[RateSplit],[UnitSplit]})Here is the custom column that has been created in this step:
We can now remove all of the other columns so we are just left with the last custom column we have just created.
Lastly, we will expand this column to bring through our original columns.
After we have clicked and expanded the column, we also amended the Power Query M Code for this step to change the column names.
This saves renaming the column header separately and adding more additional steps.
Here is the what we changed the code to:
= Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3"}, {"Reference", "Rate", "Unit"})Here is the result:
The data has now been split from multiple columns exactly as we needed.