In my last post I used formulas to solve a messy data disaster. In this post/video I’ll show you how to use Excel’s amazing Get & Transform feature (aka Power Query) to re-arrange the data.

Recap: What’s the Problem?

We normally receive this friendly data layout. We get employee names using a quick vlookup 🙂

The Mess We Receive

Our weekly data update is split into 90 mini ranges 🙁  We can’t do a simple vlookup.

original idea: Get & Transform (aka Power Query) !

Get & Transform normalizes the data so we can use our vlookup! Below is a high level description of the steps. Get my Excel file & watch my video for details.

A: Convert data into a table

Get & Transform loads data that’s formatted as a Table (highlight data, on ribbon select ‘Insert’, ‘Table’).

B: Add ‘Group’ & ‘ManagerFlag’ helpers

I used 2 helper columns. Formula in field ‘Group’ creates a group number. Formula in field ‘ManagerFlag’ adds “Manager”.

C: Load the Table into Get & Transform and do magic!

1. 1NormalizeTable4 normalize data (each row is a person)
2. 2ManagerNames filter #1 to keep only managers 
3. 3FINALOUTPUT filter #1 to keep only employees, join to #2

D: Export 3FINALOUTPUT as table in a new sheet

And finally do our simple vlookup! It might be possible to solve it without step B (helpers) but for now it’s done. Disaster avoided.

no helpers! Get & Transform (aka Power Query) !

Viewer comments from my video (Oyekunle SOPEJU, ‘A L’,  Haider Ali) inspired me to solve this without any pre-load helpers! The key was learning how to add Custom Columns in Power Query (especially the MOD function). Previously I couldn’t get the syntax right and abandoned the idea.

Bill Szysz ! Get & Transform (aka Power Query) !

Power Query guru Bill Szysz sent me his solution. Before solving it he went backwards and made the challenge more difficult!! All components in 1 step (query). I’ve been studying Bill’s solution. You should too! Thanks Bill !

Excel File and YouTube Video

• Download Excel file (original idea) and watch my video!   
• My no helper solution Excel file (no helpers!!) inspired by Oyekunle SOPEJU & ‘A L’ & Haider Ali.  
• Get Bill Szysz Excel Solution here! 

Thanks everyone for sharing your ideas!

Why Get & Transform?

• This solution is lighter than 00179 formula solution. Heavy work is done inside Get & Transform
• We could work with a much larger data-set (formula solution would get slower and heavier)
• We get to practice a cool new tool!

Learn Get & Transform With Me!

I’ve been taking this amazing course taught by Ken Puls and Miguel Escobar. I’m not yet 1/2 finished and I’ve already learned so much! Disclaimer: i’m a student and an affiliate.

About Me

My name is Kevin Lehrbass. I live in Markham Ontario and work in Toronto as a Data Analyst.

Just when you thought Excel couldn’t be any more amazing we get a new toy! GET & TRANSFORM!

Visit my blog and my YouTube channel to learn more about Excel !