Unpivoting Data

Quick reference

• 00:04 In this scenario, somebody has sent me this table of data.
• 00:07 And we can see that we have Sales Category down the left, dates across the top, and
• 00:11 values in the middle.
• 00:12 This data looks like the output for a pivot table,
• 00:15 even though we can see from the Table Design tab, it is actually a proper table.
• 00:21 The problem is, I want to build a pivot table out of this that looks different.
• 00:26 But in order to do that, my data needs to be in a format that is properly tabular.
• 00:30 So Sales Category for one column, dates in another column, and amount in another.
• 00:35 So how do you unwind that?
• 00:36 The answer is, it's hard, unless you've got Power Query,
• 00:40 which has been built into Excel since Excel 2016.
• 00:44 In other words, you have Power Query.
• 00:46 So here's the deal.
• 00:48 This is what we're going to do.
• 00:49 We're going to go and grab this data and pull it into Power Query.
• 00:51 And the easiest way when the data is in a table is just to right-click and
• 00:56 choose to Get Data from Table/Range.
• 00:59 When we do that, it's going to open it up inside the Power Query Interface,
• 01:03 it's another window inside Excel.
• 01:05 There's a couple of things I want to call out to you here really quickly.
• 01:08 Number one, go to the View tab.
• 01:10 If you do not have a check mark in the formula bar check box right here,
• 01:13 please make sure you do.
• 01:15 It is important to see this because this gives us some very important
• 01:18 information about the data that we're transforming.
• 01:21 Now, on the right-hand side, you'll see that we have an Applied Steps window.
• 01:25 If we click on Source,
• 01:26 this shows us the original data that got brought in from Excel.
• 01:29 We then have another step called Change Type,
• 01:32 where Power Query attempted to associate data types to the columns.
• 01:36 The problem here, we can see that in the formula bar,
• 01:39 it's hard coded to column names.
• 01:41 What this means is if this data gets updated to 2024, this step is going to
• 01:45 cause an error, because these columns will no longer exist.
• 01:49 So we just want to make sure, and this is your pro tip for unpivoting.
• 01:53 Before you unpivot, if you see a Change Type step,
• 01:55 you generally want to just click the X here to make it go away so
• 01:58 that you future proof it against that problem.
• 02:01 Now, what don't we want in source data for our pivot tables?
• 02:06 We never want totals or subtitle totals.
• 02:09 Notice in the Sales Category, there is a Total row.
• 02:12 What I'm going to do is I'm going to check the box right here.
• 02:15 I'm going to uncheck Total, and
• 02:16 that's going to filter that out from the data set.
• 02:19 It adds a new step that says Filtered Rows.
• 02:21 Even though the data is still in the original source, it just says, hey,
• 02:25 I don't want to use that one.
• 02:26 Likewise, there's a Total column over here,
• 02:28 which I'm going to remove by just pressing the Delete key.
• 02:31 Once again, it adds a new step, I can go back and look at the original
• 02:35 data source when we filtered out the Total row and when we remove the Total column.
• 02:39 So none of your original data is destroyed by this.
• 02:42 This just run step-by-step when Power Query is reading it.
• 02:45 Now, let's unpivot, this is the really awesome piece of this.
• 02:49 We're going to go and grab our Sales Category, right-click on it, and
• 02:54 choose Unpivot Other Columns.
• 02:56 And just like that, our data is now back into the unpivoted format that we need.
• 03:02 Now, the column names aren't awesome, so let's change that.
• 03:05 We'll just double-click on Attribute here and change that to be Date.
• 03:08 And we'll double-click on Value and change this to be Units.
• 03:12 Fantastic, the next step before we load this to Excel is to set our data types.
• 03:18 Because right now, this one here is undefined, so
• 03:21 we'll just click on a little ABC 123 and change it to text.
• 03:25 Our dates are showing us text, so we'll click on that and change it to date.
• 03:29 And that should now change this to be the date format from
• 03:32 your Windows control panel.
• 03:34 Finally, we'll change this undefined ABC 123 here to be a whole number, and
• 03:38 we're now going to load it to Excel.
• 03:40 To do that, we could click Close & Load,
• 03:42 which will load it to a table on a new worksheet.
• 03:44 Or we can go and say Close & Load To, and we can choose where we'd like to put it.
• 03:51 So I'm going to put this in a table, and
• 03:53 I'm going to put it on an existing worksheet.
• 03:55 I'm going to load it right down here to row 15.
• 03:58 And just like that,
• 03:59 Power Query is going to churn through and load the output into the worksheet.
• 04:02 So that's pretty cool.
• 04:04 Of course, the data is in the table, so now naturally,
• 04:07 I can summarize this with a pivot table.
• 04:09 We'll go put this on an existing worksheet.
• 04:11 We'll drop it right down here beside our original table and say OK, and
• 04:14 then I can configure this as I want to see it.
• 04:16 So maybe I want to see my Sales Category on rows with dates underneath that,
• 04:22 with units on values.
• 04:24 So now I can see my Sales Category by day with its units.
• 04:28 Awesome, but now the problem is, of course, we send this off to someone and
• 04:32 say, hey, this is great.
• 04:33 Just update your table, because I can use this in future.
• 04:36 And then your users do what users do.
• 04:39 They don't insert a new column, they go and
• 04:42 they add a new column after this in the table.
• 04:45 That's just not awesome.
• 04:46 And then they go and they put some values in here, and
• 04:49 they'll leave it formatted as dates.
• 04:50 I'm going to fix that right now, so we'll just grab this, use the format painter and
• 04:54 paint this across.
• 04:55 There we go. Okay, cool, so we've got values.
• 04:57 But they don't fill in the total.
• 04:59 And then they tell us, hey, by the way, we've got a new sales category that we
• 05:03 started selling several days ago and totally forgot to tell you about it.
• 05:06 So there you go, those are the sales for those two, once again,
• 05:10 after the total one.
• 05:11 Is this going to work if we try to refresh everything?
• 05:15 So let's find out.
• 05:17 We're going to go to Data, and we're going to choose Refresh All.
• 05:21 And what you can see here is that our table has now updated and
• 05:25 we see some cider records, as well as some records for our beer.
• 05:29 But what we don't see in those records in the pivot table.
• 05:32 This is actually an unfortunate thing with Power Query,
• 05:35 it refreshes the pivot table first, and then the source data.
• 05:39 So you actually do need to do two refreshes.
• 05:41 But there we go, all the information is in there.
• 05:43 Why did this work?
• 05:45 Well, let's just go quickly edit our query.
• 05:47 So, this here is our Queries pane.
• 05:49 If it's not showing on your data tab, this is where your Power Query GetData commands
• 05:53 are, this is your Queries And Connections pane.
• 05:56 You can double-click on it,
• 05:58 you can hit the Refresh Preview to make sure your data is current.
• 06:02 And then you can walk through every single step.
• 06:04 So what did we do?
• 06:05 We found our source data, we removed the total row, which in this case,
• 06:09 was in row four, but it removes it based on name.
• 06:12 We removed the total column, so we can now see that our new column is here.
• 06:16 And then, of course, we unpivoted everything other than the Sales Category,
• 06:19 and this is why it actually worked.
• 06:21 So this is very, very cool stuff and is going to make it much easier for
• 06:25 you to actually transform your data and
• 06:27 repeat that action to get it set up properly for pivot tables in the future.

Lesson notes are only available for subscribers.