Power Query Demo: Pivoting & Unpivoting Data

Maven Analytics
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 20 courses • 444,268 students

Lecture description

This lecture demystifies the concept of "pivoting" or "unpivoting" a data table. I'll show you exactly what these tools do, and demonstrate with a sample table in the Excel Query Editor.

Learn more from the full course

Microsoft Excel: Business Intelligence w/ Power Query & DAX

Learn advanced Excel for data analysis & business intelligence (Power Query, Power Pivot & DAX language. Excel 2013+)

07:01:46 of on-demand video • Updated January 2021

  • Get up & running with Excel's game changing data modeling & business intelligence tools
  • Learn how to use Power Query, Power Pivot & DAX to absolutely revolutionize your workflow in Excel
  • Master unique tips, tools and case studies that you won't find in ANY other course, guaranteed
  • Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Get LIFETIME access to project files, quizzes and homework exercises, and 1-on-1 expert support
  • Build pro-quality business intelligence solutions to blend and analyze data from multiple sources
English [Auto] OK so by this point you should be pretty comfortable with those basic power queery inquiry editing tools those number tools those text tools date specific tools. They're all pretty user friendly and pretty easy to operate as I'm sure you've noticed but there are some more interesting ones and some tools that are a little bit less intuitive to work with. And let's cover a few of those. Now we're going to start with pivoting and pivoting and it sounds a little bit nebulous but basically the way I describe it is that pivoting is really just a fancy way to describe the process of turning distinct row values into their own columns. That's called pivoting or turning columns into rows which is called unpinning. Now you're probably reading this and saying OK I understand all those words but it's still not quite clicking. At least that was my experience. So hopefully this visual will help. Picture this little table. You just got one dimension and one quantitative values field got year in the first column sales and the second column. If we were to pivot that table here's what we'd get. We would take every distinct year in the column that we've selected and we're going to pivot those years into columns. So 1990 becomes Don't call in 91 because some call them. And as you can see the actual sales values from the next column they follow. They stick with it. They're kind of getting flipped on their side that's all. So that's what pivoting this table would look like. Now the other direction on pivoting is as simple as going from horizontal to vertical. So you take all of those distinct columns that are selected and it would turn those column headers into their own rows. So little rule of thumb that has helped me kind of get a good grasp on this concept is imagine the table is kind of on a hinge you know right here in the corner pivoting is like rotating it up from vertical to horizontal and on pivoting is like rotating it down from horizontal to vertical. And one thing to note here there's another tool called transpose that works in a really really similar way but it doesn't recognize the unique values. So if you don't have uniques then transpose often does the exact same thing as pivot or pivot. But if you do have duplicates those won't be rolled together so the entire table basically just gets flipped on its side. Each row becomes a column and each column becomes a row. So for instance if we had 1990 1990 1991 and then pivoted the 1990 values would aggregate together because pivoting respects unique values whereas transposing would create two columns for 1990. So that's a small difference but important to keep in mind. Now I actually put a little demo file together to show you this. So let me hop into Excel and show you what that looks like. OK. So you don't have this file so just kind of sit back and watch this one really quick demo. Pull up a cxxviii that I call my pivot demo and I'm going to just load this right up into the query editor OK so right off the bat you'll see a few issues with this table. For one there's no had a row that's been identified which you know it's understandable. Excel saw the sample file and it said it looks like you've just got columns with a bunch of values in it. You know I didn't know that the first row actually years as a dimension as opposed to values. So you can go ahead and fix that in the home and you use first row as headers which just promotes that first row. And now we've got a column for each year and a row for each measure or metric units sales and total revenue. So that's the first issue with the table. The second is that it's just in a really inconvenient format. And I run into this all the time. A lot of times people share data in a way like this where they're trying to be helpful and break things out for you. You know one thing that I commonly see is something like breaking out each month of data into it's own tab in Excel. And as an analyst or someone who needs to model and explore and analyze that data it introduces a lot of headaches when you try to actually analyze that data in a meaningful way. So ideally what you want is a rectangular table with distinct variables as columns and your observations as rows. So in this case we're dealing with three things here we're dealing with a dimension for a year and we're dealing with two quantitative fields or metrics unit sales and total revenue. So ideally we should end up with three columns year sales and revenue with one row for each observation. That's a properly formatted data set that we can effectively analyze once we put it into these be-I analytics tools. So to wrestle this table in its current format into the format we need we're going to rely on our trusted friends pivot and pivot. So first things first we've got data organized horizontally one column for each year and we want these years to be rotated down into a vertical format so that there's one row per year per metric. Now to do that you got to make sure you're selecting the right columns. You know if I just selected this first column and went in to transform and pivot because I want to go from horizontal to vertical it would give me something totally crazy and useless. So by default Excel doesn't know that you want to pivot a certain set of column C if to manually select the ones in this case my 5 columns each represents one year. And these years are the ones that I want to translate from columns into rows. So select those you can see it created a new column to Houghs those your values. And I've got two instances of each year one for each metric. So that step was a good start for us but we're not quite there yet. And let me just show you one other thing quickly. So I had selected all five of these columns and other approaches to select the one that you don't want to pivot and use the pivot other option that does the exact same thing. So we're almost there but remember we said we wanted three columns one for year one for sales and one for revenue. So we're not quite there yet and to get there we want to take this first column which has our metrics organized in a row format and we want to bump that up to a horizontal format so that we've got a call in for sales and a column for revenue. So we'll just do kind of the opposite of what we did and we'll pivot this time I will tell Excel our values live in the value column. And there you go. So now we've got a unique row for each year. Got a unique column for the sales and a unique column for revenue. And if we just name that column year we are officially good to go. So this is a properly formatted table that we can now use for any sort of analysis we need. So pretty cool pretty helpful tool there. Before we go I want to show you one other approach and I'm going to take us all the way back to the source. So right here and because we don't have duplicate values for either years or metrics here transpose will do the trick as well. Works in a very very similar way. But like I said it just basically flips the whole data set or table onto its side. So what we can do here is without even doing anything without even promoting header's I'm going to grab everything in this whole table and just hit transpose right here in the transform tab. And that gets us pretty close. Now once we've transposed now we can go ahead and use that first row as our headers. We can name this one year and looks like we need to format these so a whole number for sales and currency for revenue. And there you go you've got to the same spot at the end of the day using both pivot pivot and transpose. So hopefully that helps clear things up. I'm going to close out of here and discard. I don't want to actually load this fake dataset and there you have it pivot on Pivot. Pretty useful tools when you need to transform a data set in two different structure.