Locked lesson.
About this lesson
If you work with PivotTables a lot, you know that they put information out the way users want to read it, but you can't use it to drive another PivotTable. So what if there was an easy way to unpivot the data? There is, and it's called Power Query!
Exercise files
Download this lesson’s related exercise files.
UnPivot Data - Begin.xlsx28.1 KB UnPivot Data - Complete.xlsx
49.2 KB
Quick reference
Unpivoting Data
An overview of unpivoting data in a table.
When to use
When you need to convert data into an unpivoted format so that you can drive a Pivot Table or Chart.
Instructions
Getting Started
- Create a new query to bring your data to Power Query
Unpivoting Columns
- Filter out any rows that you do not need
- Delete any columns that you do not need
- Highlight the column(s) that you do NOT want to unpivot
- Right-click --> Unpivot Other Columns
Finishing the Query
- Apply transformations as required
- Set data type for each column
- Rename the query
- Close & Load the query
Hints & tips
- New columns added to the source data table will automatically be unpivoted when the query is refreshed
- 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.