Unpivoting Data

Quick reference

• 00:05 Now I wanna show you one of the most mindblowingly awesome things in
• 00:08 Power Query.
• 00:10 When you go and you talk to someone, you ask them to do things like track sales.
• 00:14 It's very likely you're gonna get a list that looks like
• 00:17 with the sales categories down the left and your dates across the top, and
• 00:20 all of your values across the middle.
• 00:22 Why?
• 00:23 Because it's logical for people to actually deal with.
• 00:26 They can enter it, they can visually check it, everything makes sense.
• 00:30 The problem is is that when you actually look at how the data needs to be stored
• 00:35 in order to serve up a pivot table, it really needs to be, for
• 00:39 our first record, beer, January 1st, 2014.
• 00:43 103 and on the next row, wine, January 1st, 2014, 175.
• 00:49 So we need something that goes with category date value, category date value,
• 00:54 category date value all the way down.
• 00:56 Well, the reality is that pivot table was built to consume that kind of information,
• 01:01 which was usually stored in databases and put it out in human readable form.
• 01:06 Trying to get a human to actually record stuff in database format
• 01:10 is just a nonstarter.
• 01:13 The problem though is that when you're user comes back and says hey,
• 01:16 I'd like to see a different style report, how do you do it?
• 01:18 Unwinding data like that, it's brutal.
• 01:21 Not so bad for this data set, probably take you a couple of minutes.
• 01:24 But man, what if you had like 80 columns and 400 rows?
• 01:28 You'd be at it for a week.
• 01:30 And wouldn't it be amazing if there was an easy way to do this now?
• 01:33 Well check this out.
• 01:35 Let's go click on our data, we'll notice that it's a nice table.
• 01:39 We're now gonna go up and
• 01:40 we're gonna pull this data into Power Query from table arrange.
• 01:44 This is gonna bring the data in and at this point,
• 01:47 we have the ability to make some changes.
• 01:49 To serve up a pivot table, we never want a total row
• 01:53 because we can rebuilt that with the pivot table, so we'll just uncheck it.
• 01:58 We also don't need the total cone.
• 02:00 We'll right-click and we'll remove that as well.
• 02:03 And now, wouldn't it be amazing if all you had to do to unpivot this data
• 02:07 was to go and grab the sales category and right-click it and
• 02:10 say, unpivot other columns and boom, it just happened like that because man,
• 02:15 I gotta tell you, I think that is pretty darn amazing.
• 02:19 From here, the rest of the job to get this done, we'll grab our attribute column and
• 02:23 we'll rename it to date.
• 02:26 We'll grab our value column, rename it to say units.
• 02:30 And now we'll go and we'll convert this guy here and
• 02:32 say, let's format this as a date.
• 02:36 At this point, that's pretty much all we need to do.
• 02:38 I can now go give this guy a nice little table name here, we'll call it units.
• 02:43 And I can land this into a nice Excel worksheet.
• 02:47 So I've got a beautifully unpivoted set of data.
• 02:50 And just to prove that it's the real deal here, I'm gonna insert a couple
• 02:54 really fast little pivot tables, gonna drop them right on this worksheet.
• 02:58 And I'm gonna prove that I can rebuild by putting sales category on rows,
• 03:03 dates on columns and units on values, I've rebuilt the exact same pivot table.
• 03:08 But the beauty of course of a table of data,
• 03:11 is that we can build multiple pivot tables off the exact same source.
• 03:16 So let me put in another one here.
• 03:18 We'll go and drop it right here.
• 03:21 And on this one, I'm gonna build something that I couldn't easily do otherwise.
• 03:25 We'll put our categories first and then our dates and then our values.
• 03:28 So now, we can see our sales by date by each category in a slightly different way.
• 03:35 So that's pretty neat and you're thinking, wow, that's so amazing.
• 03:38 This is so cool because now I can send it back to my end user and
• 03:42 they can continue tracking the sales that they like to.
• 03:47 And I can still drive my pivot tables, and this is very good
• 03:50 because the more easy we can make it for someone to actually go through and
• 03:54 record their data, the more they're gonna error check it themselves.
• 03:58 We all know the statement garbage in, garbage out.
• 04:01 If we can give them an easily used input form, it lessens the chance of
• 04:05 getting garbage in which lessens the chance of giving garbage out.
• 04:08 So this is very exciting, except that when you hand it back to a user,
• 04:12 what are they gonna do?
• 04:13 They're gonna update it like this, they'll say this is great, Ken thanks very much.
• 04:18 I'm gonna go and I'm gonna put in my new sales over here after the total call.
• 04:22 And I'm gonna bang in some values and will have those entered here.
• 04:27 And by the way, I forgot to tell you that two days ago,
• 04:32 we actually started selling a different category of product called Cider.
• 04:39 That's okay, right?
• 04:40 You're all right with that?
• 04:41 Now, I'm gonna count.
• 04:42 I'm not okay with forgetting that you had sales, that's not really cool.
• 04:46 And certainly, when they go and they bring me out a pivot table that looks like this.
• 04:49 I'm now go on, man, is it going to work?
• 04:53 And that's the big question.
• 04:54 We've built something, it's on pivoted things.
• 04:57 When I go to refresh this, will it work?
• 05:00 So let's find out.
• 05:01 Data, Refresh All.
• 05:04 It looks like the query updated, but the pivot table didn't, so
• 05:07 let's refresh it again.
• 05:09 And look at that, here's my cider.
• 05:14 The date is in the right order, and
• 05:16 it total, cuz they didn't do that form either.
• 05:19 And if I scroll down, you can see that my cider records for
• 05:21 all three days are there, as well.
• 05:24 Why did that work?
• 05:25 Let's take a really quick look at our query.
• 05:27 We'll go on and we'll say edit.
• 05:30 When we take a look, we can see the original source.
• 05:33 Here is my new rows.
• 05:34 If they don't show up, you just do a refresh.
• 05:37 Remember that we filtered out the total row.
• 05:40 We removed the total column and
• 05:42 at that point, we right clicked on sales category and said unpivot other columns
• 05:47 which January 8th is now one of the other columns, so it worked just beautifully.
• 05:51 And after that the rest is history, it runs through the rest of the steps and
• 05:55 away we go.
• 05:56 So isn't that cool?
• 05:57 Because we used a table which automatically expands to grab the new
• 06:00 data, that's the real big secret here.
• 06:03 And because we've done an unpivot other on the sales category table,
• 06:06 everything just works, our users can continue to track data like this even if
• 06:10 they don't put it in the right order and don't fill it in the sub totals.
• 06:13 I don't care, cuz I can take care of that for them right away.
• 06:16 It is an absolute thing of beauty.

Lesson notes are only available for subscribers.