Excel Unplugged

Why You Should Know Power Query – Part 2: Split

Power Query can be more than an ETL tool – I often use it as an upgrade to Excel. It has some neat functionalities that make transforming data so much more simpler. In this article we’ll look at Split.

Splitting is a common task in Excel. In simple examples you would use Text to Columns command. But what about if you wanted to split/extract address number in a not-so-simple table? Or if you wanted to split to rows? You would need to use split in Power Query.

How-to

Let’s look at the following table.

Graphical user interface, table

Description automatically generated

We would like to get a column of address numbers. There are several ways to do this in Excel.

For example, we can use Flash fill. Just type in first few values and Excel will autocomplete the rest.

Graphical user interface, text, table

Description automatically generated with medium confidence

It does this pretty well. The problem is, these are not formulas, they don’t change if the data changes.

Better way would be to use a formula. After some trial and error, we get to the following formula.

Table

Description automatically generated with medium confidence

This is better, but it’s not simple nor convenient.

Now let’s use Power Query. Load the table to Query.

Table

Description automatically generated

Select Split Column > By Delimiter.

Table

Description automatically generated

Set delimiter to Space and Split at to Right-most delimiter.

Graphical user interface, application

Description automatically generated

Confirm with OK.

Graphical user interface, application, table

Description automatically generated

The numbers always appear at the end, so we get a new column that contains only the address numbers. Brilliant!

Let’s now try and get values from Fav Office 365 Apps column to appear in multiple rows. So, for example, we know John’s favorite Office 365 apps are Excel and Power BI. We want two rows for John, one with Excel and one Power BI in the Fav Office 365 Apps column.

Table

Description automatically generated

Select Split Column > By Delimiter.

Graphical user interface, application

Description automatically generated

Set delimiter to coma or »,« symbol and expand the Advanced options menu.

Set Split into Rows.

Confirm with OK.

Table

Description automatically generated

We now indeed have two rows for John!

So easy, just ponder for a moment how much time you saved with using Split in Power Query. Your dog will that you for all those extra walks!

In Part 3, we’ll look at another great Power Query functionality: lookup. Stay tuned!

Watch the tutorial

You can also watch the tutorial on our YouTube channel!

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out R Academy!