How can we transpose data
that's in multiple columns but also has multiple
headers like this one, which is more like a
reporting type of layout into a tabular data set like this, so that it's easy for us
to create a pivot table, or do further analysis using formulas? Now I made a previous video
on this that shows you how to unpivot your data if
you have a single header. This time, let's take a look at a more complex unpivot situation. We also want to do it
in a dynamic way though, so that if we end up adding
more columns to this, all we need to do is
refresh the end report and everything will pull
automatically through. Let's get to work. (upbeat music) This video is a part of my ultimate Excel Power Query course, which will take you from beginner to writing your own end function. Link is in the description below. So here we have our customer names, we have article description, and we have a nice report
about sales values by month, which are actually recorded
as dates and by scenario. What we want to do is
to transform this report into a proper tabular data set. In the end, we should
just have a single column for customer, for article. So just like we have now, except that there shouldn't
be any gaps in there, then we have a separate
column for scenario, followed by a separate column for month, and finally a single column
for the sales values. But we have a few challenges to overcome. Our first challenge is
that we also have totals in the middle of this report. And we need to exclude
these from our end results. So the pattern is we
have customer information and then we have the
total for that customer and then all the way in the
end we have the grand total. Now in addition to this, our second challenge is to somehow overcome the
problem of multiple headers. If we just had a single header, things would be a lot simpler. We can use Power Query's Unpivot feature and get this done really fast. But somehow we need to deal with this multiple column headers. Our third challenge, is
to get this to be dynamic, so that if we add data for future months, so let's say for April, all we have to do is
refresh our end report and we get everything pulled
through in a proper format. So let's see how we can set
this up with Power Query. The first thing we need
to do is send this data to the Power Query Editor, which means we can work
either with named ranges or with Excel tables. Now I prefer to work with
tables whenever I can. But what I don't want to happen is to change the look of this report. I want this to look the same. Which means that when you
want to work with ranges that looked like merge cells,
you shouldn't use merge cells and I haven't used it in this report. Instead, what you need to do, if I press Control +
1, I'll show it to you in the Alignment options
here is that you need to use Center Across Selection, because this has the same
visual effect as a merge cell but its not emerge cell. So when you create a table
out of this data set, it's not going to disrupt
the look of your report. Because remember, merge
cells aren't allowed in Excel tables. So if you create a table
that includes merge cells, these will be demerged. Here because I'm using
Center Across Selection, I'm not going to have a problem. So let's create a table out of this but just so we can keep our eye on the bottom of the report
as well, let's split our view. So go to the View tab and add
a split on the bottom side. I'm just going to scroll all the way down so we can keep our table
in view the top part and the bottom part. Now let's select everything, press Control + A, everything
is properly selected, and then press Control + T to
create a table out of this. Now, I don't want my table to have headers because I don't want the actual and budget to be used as headers. So I'm going to uncheck
that, and then click on OK. To go back to the original
look of this table, let's start by removing the table style. Then let's remove the header row. So these headers were
automatically created by Excel because I didn't have any headers there. So it just puts the default column one, column two, et cetera. But I'm going to remove that,
no one needs to see that. As a third step, let's
give this table a name. I'll call it TCustomer. Now that everything is set up, let's send this to Power
Query, Data, From Table Range. Give the end report a name,
I'll call it DataProper. Okay, so Power Query went ahead. It promoted the headers and
it added a Changed Type step. I don't need these steps. So let's reverse them. Okay, so now let's think about the steps that we need to do to get
this into a proper format. Well, the first thing is that we have to identify
the anchor columns. Basically, the columns
that we want to keep as is, that's our customer name
and the article description, except of course, this
shouldn't have any gaps in them. So first thing after you
identify the anchor columns, fill up any gaps that they might have. So I'm going to right mouse
click here, Fill and Fill Down. Now at this point, I'm not ready to use the
Unpivot feature on this dataset, because I have multiple
column headers here. If I didn't have actual
and budget information, I could unpivot the months and I'd be done with this task. But because I have
multiple column headers, I need to follow a different set of steps. What I have to do next, is to combine my anchor
columns into a single column. So again, it doesn't matter here, how many anchor columns you
have, it could be more than two, but you just need to
follow the same steps. It also doesn't matter how many levels of column headers you have
the same steps will also apply to those situations. Okay, so after you identify
the anchor columns, and you fill in the gaps, you need to merge them
to become a single column and you're going to see why in a second. From the Transform tab, let's
go into merge the columns. Pick a separator that doesn't
exist inside your data set, so that you're not using
in the descriptions here. So I'm not using a semicolon,
I'm going to go with that. You can just leave the default name here. We're going to be updating the
column header names in the end. So go with OK, now that we
have a single column here, we can transpose this dataset. So that's going to put this on top here. So from the Transform
tab, select Transpose. Now we have our different scenarios in their own columns here, and our dates, which are months in a
separate column as well. So next step is to fill
in any gaps we have here. So let's right mouse
click, Fill and Fill Down. Now because we've created
a single column header, out of our multiple columns, we can promote this row to become headers. So use first row as headers. Now Power Query went ahead
and automatically applied the Changed Type step but
I don't need that right now so I'm just going to remove that. We're going to apply a
Changed Type step in the end. Okay, so now we have a
separate column for scenario. This is a separate column for date. And this is where our data starts. So because we have a
single column header now, we're ready to use Unpivot. So select the anchor columns, right mouse click, Unpivot Other Columns. Now things are starting to look good. Before we update the names here. Let's split our attribute column to the original two columns that we had. So under Transform, Split
Column, By Delimiter, the delimiter is a semicolon, and OK. Now let's remove that
Changed Type step, as well. Next, let's give these columns a name. First one is scenario,
then we have the dates, next is customer, then
we have our article. And finally we have sales value. Now one thing we still need to do is to remove the totals from here. So anywhere we have the word total, we need to filter it out. Let's go add a text
filter for this and filter for everything that does not end with the word total and OK. Everything looks good, let's update the data types
for this scenario is text. For date, let's just
keep the date portion, texts and text is good and
sales value is currency. Okay, everything looks good. Let's send this to Excel Close & Load. It's going to create a
new table on a new sheet with our report looking like
a proper tabular data set. We have actual information here, followed by budget information. We can of course change the order of this, if we want customer and article to be first followed by scenario and date. I forgot to do that, so let's go ahead and do that right now. Lets select both customer and article and pull them to the front and update our report. Okay, so everything is set up here. One thing we need to do is to make sure that it works if we add data for April, so I have some April data here. First column is for actuals and second column is for budget. So let's test that, I'm just going to add a
column here for actuals. And let's go and copy and paste this in. So April is from P4 until P130. I'm going to cut it, so Control + X, go to the table here and Control + V. For budget, let's just
expand our table area here and do the same for budget. So that's Q4 until Q130, Control + X and Control + V, right here. Now I'm also going to remove this split. So go to View and remove that split. And now let's check if April
shows up in our end report. Once we press Refresh, so
currently we have 596 rows. Let's right mouse click and refresh. We have new rows pull in
here, which looks good, so if we jump all the way to the bottom, we have April data automatically included. Now it looks like we have
some cells on our report side that aren't picked up by Power
Query as null value cells. That's why they show up
in our end results here. So let's go and check that out and see how we can fix it. Let's go back to the Merged Column step. Now most of the cells that are
empty are seen as null here. So when we unpivot this, we
don't see these as empty cells. So these are not the ones that are causing us
problems, the ones that are, are these cells here. Later when I go and unpivot, those cells are going to show up here as empty cells, like we see here. So there's still not null value cells. But after I update the type, and I change the Sales
Value column to currency, then these empty cells become null cells. So to clean up my final
report, what I can do is go and remove any null cells
here and go with okay, send this back to Excel. And I get back a report,
that's a lot leaner, okay? So that's it, those are the
steps you need to follow when you come across more
complex unpivot situations. I hope you enjoyed our
Power Query unpivot example. Now in case you'd like to learn more about Power Query and learn how to use it like an
expert right from the start, make sure you check
out my complete course. Link to it is below. As always, don't forget to hit that thumbs up before you leave. Subscribe if you haven't
and I'm going to see you in the next video. (upbeat music)
