Splitting Data Into Rows

In this lesson we’re looking at another advantage of the splitting colymns function that we looked at in Lesson 10. In that Lesson, we showed how we can split data by delimiter, or by number of characters into columns. This was similar to the text to columns function in standard Excel but with some added extras (such as defining multi character delimiters, being able to split text, delimiter by delimiter rather than having to split the entire text string and so on)

We also said in that lesson that you’re able to split text strings into rows rather than into columns. This is a really useful function and one that I could have used knowing more than once in my career!

The principal of this is that you take a text string; let’s take a simple example of ABC:DEF:GHI. When you split this text into columns you would end up with three new columns – column 1 would show ABC, Column 2 shows DEF and Column 3 would show GHI.

When you split into rows, instead of having Column 1-3, we have rows 1-3, with row 1 having ABC, row 2 having DEF and row 3 having GHI. The careful readers among you may then think, how are the other columns in the newly created rows then populated? Are they blank or are they populated with some values? Well, for new rows, all pre-existing columns are populated with the values of the original row.

Let’s say alongside our original column value of ABC:DEF:GHI, we have a second column which has the value 123 in this row.

When we split text to rows, we’ll have three new rows and the column that we split will have a different value in each row (with row 1 having ABC, row 2 having DEF and row 3 having GHI), but then in each new row in our second column, we’ll have the value 123.

How can I split data into rows

Assuming that we already have our data in the PowerQuery editor then, this is what we do.

1. Highlight the column that has the data to be split. You can do this by selecting the column header
2. Select the Home tab and then the Split column drop down menu
3. From here, we then have the same options that we had in Lesson 10 when we were splitting data into columns, being able to split by delimiter or by number of characters. Select the option that you’d like.
4. Toward the bottom of the menu, there is an ‘Advanced’ option. This gives us the option to split our data into columns or split it into rows. Select the option to split into rows and then press ok.

The relevant data in the selected column will not be split into different rows. PowerQuery has also guessed that we wanted it to assign the information that was on the original line in other columns into the new lines. Even if it didn’t do that there’s actually a fill function as well that we’ll look at in a later lesson, that will do the same job.