Split Columns Sometimes the data that we're using for analysis doesn't fit neatly into the column structure we need.

We'll often find if it's downloaded from another system that it comes joined together in one single column, when really we need it in separate columns and this means that we're unable to perform our data analysis. Split columns is a fantastic tool that allows us very quickly and easily to separate the data into separate columns.

There are many ways in which we can split columns, two of the most common ways are by delimiter or by fixed width.

So delimiter is where we can identify a particular character such as comma, tab, space, or hyphen, and it exists in every row in the column and we'll tell it to split the data at that point fixed width is a little different. We'll identify X number of characters and it will split the data at that same point for every row.

So let's look at a couple of examples in the first example here we have some data we have the number of shares traded and the currency joined together.

So here we would split the data using a delimiter. The delimiter is the hyphen, fixed width is no use to us here because if we look at the first row the split will occur after the sixth character, whereas in the next row the split would occur after the fourth character.

In this example, we'd also perform a delimited split this time. It would be on the space character. Now we would have to be careful in this example because the space character occurs several occasions in each of the text strings. So we wouldn't want to separate Meryl from Lynch for example, so we'll tell it to do the split using the space. But only at the right most delimiter again fixed width is no use to us because each of those company names is a different amount of characters.

The third and last example, we would actually perform a fixed width split, delimited is no use because we don't have a character to use and the ISIN number is exactly 12 characters long on each row so we can tell it to perform the split after the 12th character.

Let's do a workout and see how to use split columns in Power BI.

So I'm going to use Get Data to bring in an Excel workbook,I'm going to use module 3 lesson 4 workout.

And I'll select the split columns worksheet and transform data. That will bring me into query editor and if I take a look at some of these columns, let's say the first one here number of shares traded and currency.

I need to separate this, otherwise, I can't use those values in my analysis. So we have a hyphen separating each of the rows. So I'm going to use split column, selecting my column here first, and then I'll go to split column and I know it's a delimiter I can tell it to use the hyphen.

All right. So already work that out for me. I can see here there are lots of other characters that it can use and I can also type in the character that I want. So on this occasion, I'm just going to click on OK.

And we've split the columns now I probably need to rename them so I can just easily right click over the heading and rename, so just click here and give it a much better name, I'll just put that as shares treated.

And then do the same for this one so I can right click or I can even double click in there and I'll just put in code.

Let's have a look at another example if we scroll across here, we'll see we have the company name and the value at the end.

So I want to separate that using the space character. I'll select the column go to split column, and again, it's a delimiter.

Again, it's worked out for me that it's a space, but what I am going to do here is change it to write most delimiter. So it doesn't split the data on each occurrence. I'll change it here. Click on OK and there we go. So we still have for example Merrill Lynch all in one column and we have the data separated there as well.

Now if I just scroll across here to one more column, I'll take this one here, so this one I can't use a delimiter there isn't one. If I select the column go to split column this time. It's a fixed width split, so in Power BI that's really called are known as number of characters. If I just select that and specify the number of characters, so there were 12 characters in the ISIN number and I'm only ask going to ask you to do it once so once as far left as possible.

So we'll take the first 12 characters and keep those in a column of its own.