Unpivot Data

Welcome to the FP&A guys, Excel's tips and tricks video. In this video I'm going to show you how to convert data that comes in this format. Maybe it came from your cell system, your BI tool, your accounting system. And it's in a summarized format where your dates are in columns. And we want to convert the dates and the amounts into rows so you have individual records. East here you can see is record one with the date and the amount you got north as the region with, you know, a date and amount. So I want to show you how to quickly convert this to this. There are a couple different ways you could do it. You could do it manually. You could use some Excel formulas. You could use index with a row incrementer. You could use some of the new shaping formulas such as to call or maybe vstack or other things like that and come up with the dynamic way. But it's much easier using Power Query or sometimes known as Git and Transform. The reason you want to do this is this is the format you want to work for in. You want to work with your data in Excel. The reason that is is this is the format you want to put it in, a pivot table. It's the format you want if you ever use Power Pivot. It opens up more analysis and makes it easier to work with your data. This might be a final format you want to show someone to show to your boss. You might want it on a dashboard or again, you may want it to look like this in a pivot table. So what I'm going to do is I'm going to show you how to quickly convert it and then I'm going to show you why you want it that way when using a pivot table. So what we're going to do is we're going to go here to data and we have this ribbon here and it's the first thing is get and transform. That's basically Power Query. So what we're going to do is we're going to say, hey, I want to take this from a table or a range. In this case, we've already named it a table. So we're going to click here. And now it's going to bring us into Power Query. Power Query is its own application housed in Excel. If I try to go back to the worksheet, I can't. So like, if I minimize this and try to work in the worksheet, you'll see I can't. If you want to learn about Power Query, I recommend you go to my website, go to my Excel section and look at my blog on Power Query. It will give you some videos, it will give you some websites, it will give you some books to learn Power Query. This is not a course to learn Power Query. This is a quick, simple tips and tricks. So what I want to be able to do is I want to be able to take these columns here, this date, this date, this date and this date and convert all the dates to their own column. So these headers. Then I want the amounts to be in their own column. So when I'm done converting this, I will have taken something that has five columns and converted it to three columns. A region, a date and amount. You'll notice on the right hand side I have these applied steps. That's where it records what we've done. When we brought it in, it recorded it to show hey, we brought in the source data and it selected what it thought each header should be, what data type. So now what I want to do is I want to unpivot is what it's called these four columns. So I'm just going to right click here and there's this cool function called unpivot other columns. What the unpivot other columns is going to do. It's going to unpivot everything but the region. So I've done it here. I've already, I've unpivoted it. Now I'm going to come in and I'm going to click in each of these to rename them. I'm going to go date here and just name this date. And then here I can double click and rename this amount. Next I want to convert this to a date format. You'll notice right now it has abc, which means it's being recognized as a text format. So I have all these different formats. So now we've converted it to date. You can see it went over to the right hand side. It's now a date. So now what I'm going to do is I'm going to take this, I'm going to load this back to a table. So we're going to come over here to close and load. Click on the arrow and close and load to. And we'll load it to a separate worksheet in this case. So what we'll do is we'll load it to a table, we'll load it to a new worksheet. Notice how now I have the data in the same format as I had here. One other cool trick that I want to show is, let's say I have this additional date here and let's just say it's May in this example, it's May and we want to add it to my table. So I'm going to come over here and I'm going to copy it and I'm going to add it. So next month, I got next month's data. Look what happens when I click refresh it automatically adds it because I said, hey, on pivot, all columns but the region. So every time we add an additional month, it already does that. Now let's see why we wanted to do that. We're going to come back here to our pivot table real quick and I'm just going to say insert pivot table from table range and we'll put it in this worksheet. We'll just stick it over here in column A. And what you're going to see with this is you'll notice I have region, but then I don't have a date column. I have values for the dates and so I have to drag them down here, which makes it really inefficient. Imagine If I had 50 of these. You're dragging every single one down and I have no way to treat these like a date. So it's not user friendly when you put it in a pivot table. Now let's go ahead and put this in a pivot table. So if I have insert pivot table, we'll do from table range, we'll do existing worksheet. We'll just stick it here. I have three columns just like I want. Now I can do region here, I can do date here. And it automatically groups the dates. You'll notice I put them in months and then finally the amount there. So as you can see, this is how we want our data to be and we need it in this format. As you saw in the simple video, with just a couple simple clicks, I went from this to this. That is the beauty of Power Query. If you're not using it, start today. Thank you for watching this video.