Unpivoting Data

Quick reference

• 00:04 As amazing as Pivot Tables are, one of the big challenges the data has to
• 00:08 be in a good format, in order to be pivoted.
• 00:11 And if you ask your users to go and track sales,
• 00:14 it's very likely that they'll do something like this, where they put their
• 00:17 categories down the left-hand and side in their Dates across the top.
• 00:21 But this is not in the right format to be fed into a Pivot Table.
• 00:24 So when the user comes back and asks for a different version of the report,
• 00:27 you go, man, this is a lot of work to unwind.
• 00:30 Well I want to show you that with Excel 2016 and higher,
• 00:33 we actually have some pretty awesome capabilities to make use of this data.
• 00:37 You'll notice that I formatted as a table already.
• 00:39 And I've actually included my totals, on both the rows and the columns.
• 00:43 What I'm gonna do now is I'm gonna go to the data tab, and
• 00:46 I'm gonna go to this button here called from table arrange.
• 00:49 And this is actually going to launch a new tool for you, called Power Query.
• 00:57 And here it is.
• 00:59 Now, Power Query, is explicitly built for reshaping and
• 01:03 changing your data up to get it in the right formats.
• 01:05 So once we have it here, we can do something like this.
• 01:08 Sales category.
• 01:09 I don't really need this total row in any pivotal dataset.
• 01:13 So I'm gonna go and click the little filter arrow, and I'm gonna uncheck it.
• 01:17 And say OK.
• 01:17 You will notice on the right hand side, it's actually recording some steps,
• 01:21 it says Source, it says Change Type, and it's got this Filtered Rows.
• 01:25 And I can step back to any one of these steps to see what state everything was in.
• 01:30 Now, I'm gonna scroll across the right-hand side and find the total column,
• 01:34 because I also don't need that.
• 01:36 So then I'm going to delete it by pressing the delete key.
• 01:39 And you'll notice that is actually recorded, this move to column.
• 01:43 And now to get this into the proper format for
• 01:46 Pivot Table, what like to do is right-click on Sales Category.
• 01:50 And choose, Unpivot Other Columns.
• 01:54 And boom, just like that, it actually unwinds the dataset perfectly,
• 01:59 super nice and easy.
• 02:01 Now I do need to make some changes.
• 02:02 My attribute should be renamed to date, and
• 02:06 my value here I'm going to rename to units.
• 02:10 And then the final thing that we should always do in Power Query,
• 02:13 is we should set the data types correctly.
• 02:15 You'll notice that my sales category here is listed as text, and
• 02:20 that's good, we'll set that.
• 02:22 My date is also listed as text, which isn't good.
• 02:25 So I wanna set that to be a date.
• 02:28 And my units, right now they're set to decimal number, but
• 02:31 I think whole number should be fine here.
• 02:33 And at this point,
• 02:35 I have a nicely unwhelmed dataset that I'd like to load to my worksheet.
• 02:39 Now we have two options here, we could click the Big Close & Load button, and
• 02:43 that would create a new worksheet for us.
• 02:45 Or, we could click the bottom half, where it says Close & Load 2.
• 02:49 And at this point, I can come back here, and
• 02:52 I can tell it exactly where I'd like it to go.
• 02:55 So what I'm gonna do, is I'm gonna place this on an existing worksheet, and
• 02:58 I'm just gonna go and put it right down here, below my data.
• 03:01 And I'm going to say OK.
• 03:04 And just like that,
• 03:05 we have a beautiful table that's been unpivoted from the data set we had before.
• 03:09 And the cool thing about this, of course, is that once we have a data set and
• 03:13 it's in a tabular format, what are we going to do?
• 03:15 Well naturally, we're gonna pivot it.
• 03:17 So we could go and say, insert a new Pivot Table.
• 03:20 I could put it on an existing worksheet, I'll just drop it right over here,
• 03:23 for example.
• 03:24 And say OK.
• 03:26 And now, with this in place, I can easily go back and
• 03:31 put sales category on rows, dates on columns and units on my values.
• 03:36 And just like that, we can rebuild the same Pivot Table that we had before.
• 03:39 But that doesn't make a lot of sense, because we already have that.
• 03:43 But here's the cool thing with a Pivot Table.
• 03:45 We can just move this around.
• 03:47 So now we can look at it in a different format,
• 03:49 that we couldn't build from the original piece.
• 03:52 So this is pretty neat.
• 03:53 But why is it so important to us.
• 03:55 Well it's all about what happens next.
• 03:57 We now have the ability for users to actually capture data the way that they
• 04:01 wanna see it, so we send back and see keep updating this table.
• 04:04 Well the challenge is, your user goes and they do stuff like this,
• 04:08 they say after the total I'm gonna put in a new category.
• 04:12 And I'm gonna go and drop this stuff in here and
• 04:13 put some new values in, ignore the totals and know by the way,
• 04:17 I forgot to tell you that we start selling a new category of data the other day.
• 04:23 And now, we got to try and figure out, how well is this gonna work?
• 04:27 Well, here's the answer data refresh all.
• 04:35 Now if we scroll down our Pivot Table,
• 04:36 we can see that we didn't get any more dates in here.
• 04:39 And yet, I can see that cider is here.
• 04:41 And this is something that's actually important to recognize.
• 04:43 Is that the Pivot Table and the Power Query actually are out of sync.
• 04:47 So if you refresh this again,
• 04:48 you'll notice that you get your new values in place.
• 04:51 The reason why this worked, if we go back to the Queries & Connections pane and
• 04:55 we double click on our Sales Data Query, is that you'll notice that it's actually
• 04:59 recorded every single step that we did and is now re-executing it.
• 05:04 Here's our source data.
• 05:06 You'll notice that now cider is here.
• 05:08 We changed type.
• 05:09 We filtered the roll out for total, removed the other total column,
• 05:12 which is leaving us with the new column now.
• 05:14 Unpivoted out our columns, renamed them, and changed the types and
• 05:19 everything works beautifully.
• 05:20 And this is the powerful thing with Power Query both on the ability to unpivot, as
• 05:25 well as the ability to reuse your business intelligence when the table expands.

Lesson notes are only available for subscribers.