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

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 !