Power BI – Split Column

BY: David Rohlfs

Introduction

There are a lot of reasons why you would need to split a column. Whether you are just trying to separate a section of text from another, or you need to separate two sets of numbers. Splitting a column quickly becomes a must know for any report builder in Microsoft Power BI and there are a lot of reasons to master it early on.

In this blog we are going to go over the different types of the split column function, the options that you might be presented with when selecting these options, and a quick walkthrough demonstrating this function.

When to use it

When you start talking about splitting columns there isn’t one set reason why you would ever do this. This function becomes useful in so many situations that there isn’t any exact reason why you would want to do it, but there are a few scenarios where it is more common to use this function.

The times that I use this function the most are:

• Separating two values by a delimiter
• Separating first and last names
• Splitting a column by a certain number of digits or characters (I do this in place on an extract function)

There are also many options inside of the split column function. You can:

• Split a column by a delimiter.
• Split a column by a number of positions.
• Split a column by a number of characters.
• Split a column by the switch from upper case to lower case.
• Split a column by the switch from lower case to upper case.
• Split a column by the switch from digit to non-digit.
• Split a column by the switch from non-digit to digit.

Because there are so many options under this function it can be useful in a variety of situations. For the most part, I end up using one of the first three options and especially the delimiter option.

To split a column by a delimiter you are specifying a character in your data that you want to separate at. For example, if you have a string of emails separated by comma’s then you would specify to split column by a comma delimiter. And if you had a list of emails and all you wanted were the email domains, you could split the emails by an @ delimiter.

There are several options in the pop-out when you are working in the split column function that you should learn about. Here is a picture with all the options that you will encounter if you use this function. Note: This is a view of the split column by delimiter.

 

In here you can see that you might have the option to choose how many times and where to split your column at. Then with the advanced options you can choose if splitting your column creates a new row (keeps all pre-existing row information) or if it creates a new column.

If you choose to split a column and create more columns, you will also have the option to choose how many new columns will be created. When you choose this option Power BI will automatically create a suggestion based on the first 1000 rows of data. If your data looks like it will be split three times, Power BI will suggest creating three new columns, and four for four, etc.

If you choose to split a column into new rows you should understand how your other data will work when that happens. Here is an example from the walkthrough that shows what will happen.

 

 

See how Jake Smith now has two rows that are identical besides the email, that is because we chose to split the column into rows. I have found this option very useful when I am trying to keep my columns consolidated into as few as possible because you are keeping all your data inside one column. This should only be used when you have lists inside of your cells that you are trying to mitigate.

If you choose to split a column by a delimiter, you will be deleting your delimiter. If this becomes a problem, you can investigate extracting and see if there is a solution using this function.

When you choose to use a quote character you have the possibility to parcel out your splits. Here is an example of a text that I want to split by column with double quotes as a quote character:

 

 

The last option that you have is to split a column using special characters. If you do this, then Power BI will insert a code to the delimiter option.

When you choose to split a column into multiple columns, the name of the original column will have a “.1” added to the end. And to the columns after that it will be “.2”, “.3”, etc. So, you may need to rename your columns after you choose to split them into new columns.

If you are familiar with the extract function, you are probably noticing that the extract function and the split column function have very similar uses. But the difference between the two is that the split column keeps all your data, while the extract column will only keep the information that you specify.

Walkthrough

In this walkthrough we are only going to go through the Power Query function on splitting columns and not any of the M Code. Because there are several methods to splitting columns, we are just going to stick with what I use the most which is splitting a column by delimiter into rows. If you are interested in the other methods their descriptions will be in the When to Use It section of this blog.

To start you are going to need to go to Power Query. You can do this by clicking Transform Data in the Home ribbon.

 

Next you should select the column that you want to work in then click on the transform ribbon and the Split Column dropdown.

 

This will show you a list of options to split your column by. For this example, we are going to choose the split column by delimiter.

 

Now we can choose our delimiter in the first box, and we are also going to split our column into rows. So, we need to open the advanced options and select rows instead of columns.

 

Once we see that all our options are correct, we can click OK in the bottom right corner of the pop-out. And now all that we need to do is check that our column looks correct.

 

Conclusion

There are many reasons to get good at splitting a column and hopefully this blog helps you understand some of them. If the Split Column function is not what you are looking for then I would suggest learning about the extract function in Power Query. If you liked this blog, then there are a couple of similar blogs by EPM Strategy linked below.

 

Links Related to this Article:

Power BI – Extract Function : :: Welcome To EPM Strategy ::

Power BI Column From Examples : :: Welcome To EPM Strategy ::

Power BI and Group BY

Power BI Conditional Column

 

Comments