Splitting Data Into Rows

Splitting Data Into Rows

Previous Session Summary

In the previous lesson, we looked at the merge function which is a way that we can bring two datasets together, row by row. This allows us to have a single dataset, which has the information from two datasets in it. However, it often allows us to do more than that, identifying information that is only in one dataset, or perhaps information that isn’t in either?

Lesson Synopsis

In this lesson we’re revisiting the idea of splitting data again (this is where in a column of text we identify a particular piece of text known as a delimiter, and then split the data in that column wherever that piece delimiter occurs).
 When we last looked at this topic, we split the data into columns meaning that we created new columns every time there was a delimiter in our target text column.  In this lesson we’re instead looking at how we can split data into rows instead. This means that for every delimiter in our text column, a new row is created with each individual section of text in each respective row. In the newly created rows, the remaining columns which do not have the delimiters are filled with whatever information was in the original row.

Resource Listing

The Video

Play Video

Lesson Notes

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.

.... and a little more!

Nothing more to add here!