Rearranging data is initially about recognizing the pattern. This screenshot is from Ken Puls. I instantly saw the steps to rearrange the data (thanks to Ken’s online course!)

Pattern

High level: (a) split ‘Clients Assigned’ names into columns, (b) unpivot those columns into rows.

Necessary Transformation

This task is now incredibly easy thanks to Excel’s Power Query (Get & Transform) tool.

Get my Excel file and follow along.

Transformation Steps

Load Data

• Select any cell within your data
• On the ribbon click ‘Data’, in ‘Get & Transform Data’ section select ‘From Table/Range’

Transformation Steps

• Right click column header ‘Clients Assigned’, ‘Split column’, ‘By Delimiter’, select ‘Comma’, split at ‘Each occurence…’

• Right click column header ‘SalesPerson’ and ‘Unpivot other columns’
• Right click column header ‘Attribute’ and ‘Remove’
• Double left click column header to rename ‘Value’ to ‘Clients Assigned’

Export Back To Sheet

• Click ‘Close & Load’, ‘Close & Load To…’, and select a location.

Why Rearrange Data?

Building a model on top of database layout data is much easier. However, there are some rare exceptions. If the task were simply to count the clients assigned to each salesperson use this formula:

=LEN(G5)-LEN(SUBSTITUTE(G5,”,”,””))

Or if it’s a table format use this formula:

=LEN([@[Clients Assigned]])-LEN(SUBSTITUTE([@[Clients Assigned]],”,”,””))

Before Power Query

In the ol’ days before power query rearranging data was often painful. It’s possible to use formulas to solve this but it’s so much work. There’s also a trick to combine Text to Columns with the Alt D P pivot trick. Just a few steps but Power Query is still easier.

About Me

My name is Kevin Lehrbass. I’ve worked as a Data Analyst since 2001.

Power Query is a revolutionary tool! The basics are easy to learn but mastering advanced techniques takes practice. The vast majority of Excel users have no idea how valuable Power Query is.

Power Query is free! Starting with Excel 2016 it’s a built-in feature. Learn with me at Power Query academy.