Scribd
Upload
2 views8 pages

How To Split Data Into Rows Using Excel Power Query

This document provides a step-by-step guide on how to split data into rows using Excel Power Query, specifically for handling items separated by commas. It explains how to load data, use the Split Column feature with a delimiter, and clean up whitespace for better data management. The guide concludes with instructions on how to load the cleaned data back into Excel.

Uploaded by

quotelover2117
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views8 pages

How To Split Data Into Rows Using Excel Power Query

This document provides a step-by-step guide on how to split data into rows using Excel Power Query, specifically for handling items separated by commas. It explains how to load data, use the Split Column feature with a delimiter, and clean up whitespace for better data management. The guide concludes with instructions on how to load the cleaned data back into Excel.

Uploaded by

quotelover2117
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

How to split data into

rows using Excel Power


Query
posted on SEPTEMBER 10, 2022 (Edit)

Have you ever had a group of items separated by commas


in Excel that you wanted to break into separate cells? Take
the following example:
Here we have a list of signups for an upcoming office party by
department and name. We’d like to easily sort and filter this data by
name and department. In classic Excel, maybe you’d try using Text to
Columns, which would result in something like this:

Not exactly what we want, right? It would be much easier to manage


this data if it were in just two columns like this:
Power Query will make splitting our data just as easy as Text to
Columns… except this does Text to Rows as well.

Splitting Signups by column


The first step is to load the rsvps table into Power Query. Check out
this post for how to do that.

Under the Home tab of the Power Query editor, select Split Column > By
Delimiter:
The “delimiter” is just a fancy word for what character is separating each
item in your data. In our case, it’s a comma and Power Query will
probably detect that for you. If it does not, select “Comma” under the
delimiter menu.

Everything else is ready for you to hit OK… but before you do that, click
Advanced Options. The option to do Text to Rows is hidden here! Click
on Rows, then select OK.

This is looking promising, right?


Cleaning the leftover white space
It would appear that we’d be all set, but there’s actually on more thing
to do here. To see that that is, go to the View tab of the Power Query
editor ribbon and under Data Preview, check on “Show whitespace.”

It appears our data isn’t as clean as Power Query was letting on, huh?
Think back to the delimiter that we used here. It was a comma, but there
is actually a comma and a space separating each name. We could fix this
by adjusting our delimiter to include both characters, but let’s try
something else.

Stripping the whitespace


We’ll instead strip the whitespace from the Signups column by clicking
on its column header, then selecting Transform > Trim:
Great! The leading spaces are gone. And if you’re interested in the
difference between Trim and Clean, check out this post.

We’re done! To see the results in Excel, go back to the Home tab of the
Power Query editor, then Close & Load. You should see the exact results
we were hoping for:
To compare your workbook to the solution file, click below:

Download the solution workbook here

I hope this post gave you yet another use case for the wonderful Power
Query software. Check out my other posts on Power Query here.

You might also like