3 things that used to suck in old excel that you can now easily do with power query

I’d like to dedicate this article to Joe Dambrino who in 2012 was the first person to explain to me and show me the beauty of working with flat files. We didn’t have Power Query then but we do now!

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!

  1. Merging datasets together
  2. Breaking a dataset into its component parts / Deduplication
  3. 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 …

 

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 …

This is a WIDE table, all the dates are in separate columns going off to the right
This is a TALL table, all the dates and sales amounts are in one column (I cut off lots of the rows)

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

4 thoughts on “3 things that used to suck in old excel that you can now easily do with power query

    • That’s a great question – you are totally right, it’s better to use the relationships in the diagram view to connect tables together AS OPPOSED TO the way we used to do it in Excel with VLOOKUP and creating a giant table.

      Here’s a scenario where you would want to merge tables – imagine if you have a product table, a product category, and a product subcategory table. Your data model will be cleaner if you can put all that data into one table, so you could merge them together in PQ. Does that make sense?

      Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s