I was introducing Power Query to a client this morning and found myself saying things that needed to be shared with a broader audience. That client was paying my full rate, you’re benefiting for free. You’re welcome!
- Merging datasets together
- Breaking a dataset into its component parts / Deduplication
- Unpivoting a dataset
For folks coming from a tech background these activities have NEVER been hard — you have tools like SQL that can crank through this stuff. For the Excel crowd these tasks have NEVER been easy. But Excel folks are hackers and they’ve figure out how to get these things done. For example, I was showing a client “unpivoting” this morning and he said “let me show you a trick” which then proceeded into a set of three-button keyboard shortcuts, cascading menus, and non-obvious button clicks that did in fact unpivot the dataset. It was cool in the same way that these household hacks are cool …
Why didn’t I think of that?!www.buzzfeed.com
50% “sweet!”+ 50% “wtf, srsly?!” Most Excel hacks share one common characteristic — they aren’t very automated, meaning you have to do that same arcane set of button clicks and keyboard shortcuts every time you deal with the data. Sure you feel bad ass for knowing that secret code in the same way you’d feel bad ass for biking the Swiss Alps with a fixed gear bike. I’m looking at the whole situation and thinking “Ouch!” So anyways, yes I was impressed with the hack but he was WAY more impressed when we walked through how to do it in Power Query.
I’m not going to spend much time explaining how to do these things — but if the community expresses some interest in a topic I could definitely write up a post 🙂
So I was saying … there are three things that are now easier with Power Query, here they are! Power Query encourages us “to be good to our future selves” meaning you spend some time building something the right way now so next time you do it you just have to click a button. Let’s get into it.
Merging datasets together
You have three tables: a products table, a a products category table, and a products subcategory table. You need to get all of this data into one table so that you can make sense of it. What’s your first move?
For the Excel crowd you’re probably going with VLOOKUP or INDEX/MATCH. Right now there’s somebody reading this who has a crazy hack to get this done. I know! I know! Calm down person with the crazy hack. I’m suggesting how 99% of Excel users would solve this problem. The problem with this standard VLOOKUP approach is that you start with the products table and then you have to write a formula for every column of data that you want to merge into it. And when a new column of data shows up or you realize you left one out, you write another formula.
The SQL people in the room all have their noses in the air and are like “LEFT JOIN, duh!” Look SQL folks, historically we Excel people haven’t had that LEFT JOIN luxury. We’re lowly data janitors just scrubbing these floors with this busted ass VLOOKUP mop. But no longer, PQ to the rescue! In Power Query you can smash two tables together based on a common column and load that merged table into a worksheet — it’s just a few button clicks.
Breaking a dataset into its component parts / Deduplication
Situation Number Two. You have a table that has all of your sales data (data, sales amount, customer, product) and you need to break it out into a table for sales, a table of unique customers, and a table of unique products. Alright Excel people, what’s your first move?
Really the best way to deal with this is to start copying and pasting. Sounds a little nutty but any hack that you use here is going to take a lot more time than doing the work manually. Wait what? You have a crazy hack for this?! 🙂 I’m sure somebody has figured it out, but moving on … You’re going to copy and paste the sales data into two extra tabs, remove the columns you don’t need for the products table / same for the customers table, and use the remove duplicates function to get a table of unique products/customers. Then you can go back to the sales table and remove the products/customers columns that you don’t need. Voila! When you get the data for next month you have to do this all over again and you better pay close attention so you don’t fat finger anything!
Once again our SQL people are just dying to introduce us noobs to the SELECT statement but Excel people don’t be intimidated with all this fancy SQL talk. In Power Query you can take the original table, break it out into its component tables, deduplicate the products/customers , and load all the results into a worksheet in Excel. When the new data comes in next month you just click the refresh button and you’re done. No need to write one line of code — it’s all just button clicking.
Unpivoting a dataset
Finally Situation Number Three. The shape of your data matters, and there are basically two approaches to shaping a dataset— really wide tables or really tall tables. Here’s what I mean …
The wide table is a better format for reading the data; it’s a more “human” format; it’s a reporting format. The tall table is a better format for a computer to deal with the data; it’s a better format to do analysis of the data with a pivot table and with Power Pivot. I’m not going to go too much into why the second way is better, so assuming you want option 2 how do you “unpivot” the wide table to re-shape it into the tall table?
There are definitely some good hacks for this and of course there’s VBA but once again for 90% of Excel people you’re probably going to start copying and pasting. It’s how I was doing it in 2011 — no judgment. It’s not an exaggeration to say that unpivoting is one button click in Power Query. For lots of Excel people if this is all that PQ did it would be a great tool, like something you would pay money for. But of course it does much more.
It still puts a smile on my face when a client shows me a three-button keyboard shortcut that brings up some menu that I’ve never seen. 🙂
To close it out — Power Query solves a number of headaches that us Excel people have just come to live with over the years. We’ve accepted our fate and come to idolize people that know all the crazy hacks. And yes cheers to all the crazy hacks. It still puts a smile on my face when a client shows me a three-button keyboard shortcut that brings up some menu that I’ve never seen. 🙂 But in some ways the era of hacks is coming to an end as Microsoft is delivering real solutions to these problems with Power Query, Power Pivot, & Power BI. Currently the best way to learn Power Query is to just dive in, and if you have any questions you know where to reach us.
Austin is VP of Operations & Growth at PowerPivotPro — check us out http://www.powerpivotpro.com