- HD
- 720p
- 540p
- 360p
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
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. You can download source data files for the course from the resources section of your Lessons page.
Unpivoting Data19.5 KB Unpivoting Data - Completed
34.5 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 PivotTable or Chart.
Instructions
Getting Started
- Create a new query to bring your data to Power Query
Unpivoting Columns
- 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: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.