Today let's take a look at transposing our horizontal data to vertical. This time, there's a twist. Actually there're two twists. We don't want to transpose
or unpivot every column, just some columns and some
of the cells are empty but we still want to see them
in our transposed version. Now I have a few videos on this, using the transpose formula, copying and pasting as
transposed and also R1 C1 trick. If you missed those, make
sure you check them out. Link is below. Today though, we're going
to do something different and super easy. We're going to use, Get &
Transform from the data tab. Get & Transform is
available since Excel 2016. In the older versions of Excel, it's available as a free
add in, called Power Query. Since 2016, these features
became native to Excel. So let's use them to transform our data. (upbeat music) Here's our sample data. We have information on
App, the account type, and the value by month. This is the information
we want to transpose. And then we want to combine
it with this information here. So what's going to end up happening, is that for each of these numbers, we're going to have a separate row. Which also means we're going
to be adding a separate column for the months here. Let's use Power Query or Get
& Transform to get this done. Now if you go to the Data Tab here, to the Get & Transform section, notice that you can create a new query from a table or a range. Now if you want to use a
range instead of a table, what you need to do is
highlight your range and give it a name. And then click on this button. If you are okay with turning
your dataset into a table, then all you need to
do is press control T, click on okay and use this
to create your query on. So I'm going to go with
the table version here. Let's just take away the table style, give this a name and press enter. Now let's go back to the data tab and click on From Table Range. Automatically, Power Query opens here, and the first thing I'm going to do, is change this name here
which is going to be the name of my end result. So my end table. So that's going to be Table Final. Now let's unpivot these columns. Since I have less of these
columns that I want to keep, I'm just going to highlight them. Right mouse click and select
Unpivot Other Columns. Which basically means unpivot these. I get a new column for the months here. So let's just change this name to Month. Press Enter. Now notice one thing here. For WenCaL sales, I have information that goes from January to December. But WenCaL volumes starts in April. There's no information
for Jan, Feb, March. Why? Because it was missing
from my original dataset. It's empty. So the moment I transpose
these or I unpivoted these, they were removed because they were empty. Let's say I actually want to keep them and I want to show zero
values for those fields. So what I'm going to do is go back a step, before I started to unpivot,
and add a step there. That step is to replace value. So I'm just going to highlight
the Jan to December data. So click on Jan, click on December hold down the shift key,
then go to Replace Values. It asks me if I'm sure if I want to insert a step in between these? That's fine. What value are we looking for? We're looking for null. Don't put this in quotation marks, just type it the way you see it here. And we're going to replace it with 0. And click on okay. 'Kay, so now let's go to the last step and we see our zero values, right here. We're done. We're going to go and load,
close & load, close & load to, this creates a new table
on a new worksheet. Click on okay. Our data is transposed and transformed to give us tabular dataset. Now let's just test one thing. Let's add some more information to this, I'm going to add a new Health App. Some sales information, now let's go back, right mouse click and
refresh and the information automatically shows up here. So that's how you can use Get
& Transform or Power Query to unpivot parts of your dataset. I hope you enjoyed this video. If yes, give it a thumbs up and before you leave, don't forget to subscribe to this channel if you want to get updates
when I post new videos here. (upbeat music)
