Power Query: Unpivot - Microsoft Excel Tutorial

- [Instructor] Now I'm going to show you Unpivot. This is the feature that sold me on Excel's Power Query. Here is some data that looks like a pivot table, we have column headers. In this case, the column headers are Shift1 through Shift4 and then we have row headers, the days of the week. And it's easy to see that Ignacia has Shift3 on Tuesday, but now what if we want to sort this in alphabetical order by the people's names? But we also want to see the day and the shift they're assigned? Maybe we want to get a count of how many unique people are in here. Not so easy this way, but hopefully this will sell you on unpivot and Power Query. Here we go, cursor inside the data set, right click, get data from Table/Range, okay, here is the data. The shifts column is already highlighted so I'm going to go up, right click, unpivot other columns. Unpivot other columns is handy if there's a situation where your data might go further out to the right, if you do wind up with a Shift5, Shift6, or you could highlight the Shift1 through Shift4 columns and then unpivot those, but it's easier to highlight the first shift and then unpivot other columns. Here we go, click on it. Look at that, the data is all stacked up and now what we can do is sort these names ascending. Highlight this column, and then A to Z, there we go. Derek, Grace twice, Ignacia three times, all the way down to Yasmine. And notice over in the Power Queries pane, the applied steps, those are being recorded. I'm going to change the name here to Names instead of just Value and then change this to Shift and Days. Then I'm going to close and load this, close and load to, put it on an existing worksheet, and I'm going to put it right here. Okay, it says 20 rows were loaded. I'm going to close the pane and then add more data and I invite you to add some Saturday and Sunday data of your own, okay. Put the data here, right click, paste it in, the table absorbed the data. Now I'm going to bring back the Queries and Connections pane. Okay, now I'm going to refresh. The new data has been added, and you can see 28 rows loaded. I'm going to close the pane and see there is some Saturday and Sunday data in the new data set. Now let me show you one other thing about unpivot. Over in the Conference Rooms sheet, here we have a list of conference rooms, the wing that they're in, and the time slots. In the Goblin Shark room, there are 17 attendees signed up for 1:00 PM. Now here is a situation where we want to keep the conference room along with the wing. It's not good enough to just pivot by the conference room. Cursor in the data set, I'm in the data tab on the ribbon from Table/Range. Now notice one thing about this data, at 10:00 AM in the Moonfish room, also Goblin Shark at 11, there are nulls, unpivot is notorious for getting rid of null values after the unpivot. I've submitted a request to Microsoft to have this changed, I don't know if it will change, but you need to know that this is an issue. A lot of times we don't need the nulls, it's fine that they go away, sometimes we do need to keep them, but let's do this. In order to pivot by the two columns, I've got conference room, that's already highlighted, hold down the control key, select wing, and then right click, unpivot other columns. We can rename this Time, and here, Count. Good. Now let's verify something, I want to slide this out of the way, Goblin Shark 11:00 AM is empty, which means Power Query turned it into a null. Let's look up Goblin Shark 11:00 AM. Goblin Shark 11:00 AM is not in the data set and like I said, maybe this doesn't matter. There are a lot of tricky ways to keep your nulls, but right now, for our purposes, I want you to know how to unpivot and warn you about these nulls. But right now, what we can do is go ahead, close and load, and you see 45 rows loaded, and we can do anything that we want with this data because now it is in four basic columns instead of a matrix, and unpivot was the key to it all.