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!
- 1NormalizeTable4 normalize data (each row is a person)
- 2ManagerNames filter #1 to keep only managers
- 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.
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 !
Pingback: Data Disaster: non normalized data | My Spreadsheet Lab
Pingback: Why and How to Unpivot Data | My Spreadsheet Lab
Pingback: Learning Power Query (Get & Transform) | My Spreadsheet Lab