Splitting columns

Overview

Sometimes, when working with data, columns needs to be split into separate columns, for example, when you need to split a full name into separate first name and last name columns.

Power Queries ‘Split Column by Delimiter’ feature, or Table.SplitColumn function both help split data this way, even, if required, into rows. This can be very useful, as it allows you to create a new table that associates each split value, with its related entity.

Below, for example, is a source table that after being transformed, pairs products with colors to find the number of products by color:

Splitting by columns vs rows

When splitting columns in Power Query, you need to be careful about whether you need to split columns into rows.

For example, if you load the data into the Query Editor, so it looks like this:

You could split the last column, by right clicking on it, and choosing the option to Split Column, by Delimiter, as follows:

If you then choose the following options:

The data will look like this:

But this is not quite what you want.

Instead, if you set, within the advanced options, to split by rows, like this:

You should then get the desired result: