Today, we're going to cover a special case that's
quite common when it comes to cleaning and organizing your data in Excel. Now, this question
actually originated from LinkedIn, just with different data. What we need to do is to come up
with a dynamic way to switch the data in columns to rows. So, for example here, I have a column for
project, department, and person. I need to switch the department information to become separate
headers, so Sales, Finance, and Marketing will be their own columns, and then I have the list of
projects on the side here, and the values part will be the content of the person column. The catch is
that multiple people from the same department can be working on the same project. How can we show
all of this correctly? We're going to take a look at different options. I'm going to show you three:
one is going to use formulas, the next one Power Query, and finally DAX. Let me know which one you
prefer or if you have another way of solving this. Let's solve this first with formulas. What I need
here is a unique list for the project column, so I'm just going to use the UNIQUE function.
All it needs is an array, which is the project column. Close bracket, press Enter, and I have
my unique list, or distinct list of values, better way to call that. For the headers here,
I need a unique list for department, so let's go ahead and do that. Select the department column,
close bracket, press Enter, and my list spills this way. I want it the other way, so I'm just
going to put it inside the TRANSPOSE function, close the bracket here, and I have my distinct
list for department. Okay, so, so far so good. Now we need to get the correct person that works for
this project and for this department. The problem is sometimes we have more than one person from
each department working on the same project. Okay, so how do we handle that? If we just think about
a function that can give us multiple results, what would that be? The FILTER function,
right? So, let's go with that. Because here, I can't really use VLOOKUP or XLOOKUP because
these functions just return one result. We need something that returns multiple results, and
FILTER works great here. The first argument requires an array, this is the result that I
want to get back. I want to get a person back, so I'm going to go with this column. Next is the
include argument. Now, this is the tricky part because it needs to look at the combination of
project and department, so that it can find the right person. This means that my include argument
is going to have the combination of project. I'm going to use an ampersand here and department
and compare this with a combination of this and this. Now let's also plan on how we need to fix
this because I'm planning to copy the formula down and across. Well, here for E3, we don't want
the E to shift, we want the three to change, right? We want the row to change, so I'm going
to press F4 three times to just fix the E, and for F2, I want the two to be fixed. I
want the row to be fixed, but the column to be variable. The last argument for FILTER is what
it should return if it doesn't find a match, and I'm just going to put quotation quotation,
so it returns nothing. Close bracket, press Enter, and here I get two results that's spilling
down. Well, this is going to give me a problem because the second result should belong to Nightfire, right? And also, when a formula spills, I can't just copy down because I'm going to get
this #Spill! error. So this means I need a formula now to combine these two values into a single
value, and I can use the TEXTJOIN function here. TEXTJOIN requires a delimiter. For my delimiter,
I'm going to go with the space and quotation. Next argument is whether empty roles should be
ignored or not. Well, let's just go with true, and the last argument is the text that I want to
combine and that's these two values in this case. So let's go all the way to the end. Actually,
let's expand this so you can see the full formula. Close the bracket here, press enter, and now
we have both names in the same cell. Okay, so let's just expand this as well, so we
can read it better. We can copy this down and copy this across, but when we come to copy
this across the table references, they end up moving. That's not something I want, so
instead of pulling this across this way, we're going to copy this. Press Ctrl + C, and then
just paste this here. This is going to make sure that your table references don't move. Okay,
so now we have the correct persons. Let's just make this bold and this bold, so we can see
it better. We have Donald and Gary working on the Grand Slam project. Just Holly for
Nightfire and Lucas and Skye from Marketing, they're right here working on the SeaFire project.
Also, Paul from Sales is working on SeaFire. Now this is fully dynamic, so if for
SeaFire we have a new person in Sales that's going to join, so let's say I join. I'm
going to end up here with Paul. Okay, so that was the first method of solving this using formulas.
Now let's take a look at the Power Query version. As a first step, let's go to the Data tab and
send this to Power Query. So select from sheet. The logical step here would be to pivot this
column right, because this is the column that I want to make headers out of. Sales should
become a header, Finance should become a header, and Marketing header as well. So this means
I should pivot this. So let's go ahead and do that. Go to Transform and select Pivot Column.
Now, I need to select what I want as values. I don't have numbers, I just have text. So my
values column is going to become my person, because that's what I want to be in the
middle here. And under Advanced Options, I need to adjust this to "Don't aggregate"
right, because I don't want to count them, I don't want to get the max or min of this text, I
want to get the text. So I don't need to aggregate anything here, and I'm going to go with OK. This
works for cases where I just have one person from a department working in a project, but not
where I have multiple people. And the reason is, there are too many elements here, it cannot
show multiple cells in a single cell. This means that I need to do that combination, I
need to bring the different elements into a single cell before I pivot the column right. So I'm
going to reverse this out. So here, for example, I need to combine Donald and Gary in the same
cell, so I just have one line for Grand Slam and Sales, because once I have that, I wouldn't
have any problems pivoting the column. So this sounds like grouping. I need to group these two
columns together, so I'm going to highlight these and go with Group By. It automatically put my
selection here. Now, for the new column name, let's just leave it as is, but I don't want to
do any type of aggregation. I just want to return all the rows and then click on OK. So now, I have
a unique combination for project and department. Check this out, when I click on the side, I
get the multiple records here as a table. For Nightfire here, I just have one person, this
is also one person, and then I have two people here from the Marketing department working on SeaFire. OK, so now, I've managed to create one line but I need to combine these two values into a
single cell. I can't just expand this because this is going to put me where I was before.
It's going to create a new row instead. I need to create a list out of this person column.
To do that, I can add a column and add a custom column. I'll just leave the name as is. That
custom column is just going to grab the person column from this one here. So notice, this column
is called "Count" because I didn't change the name. So I'm just going to select that, and now I need
to get the person columns. I have to use the column syntax which is the square brackets. I
can't see person here because this only shows me the columns that are visible. This one is like
a table in a table. So I just have to type it out, close the square bracket, and click on OK. Now,
I have a list. So when I click to the side, I just have the content of that single column. But what's
so great about lists is this, when I click on this double-sided arrow, I get to extract the values.
So, I don't want to expand to new rows because it's going to put me where I was, but instead I
want to extract the values and when you do that, you get to select your delimiter of your choice.
I'm going to go with custom, add a space, and present space, and click on OK. And now, I'm
going to get the multiple values in a single cell. OK, so I just have to remove this and let's go and
pivot this column. Go to Transform, Pivot Column, my values column here is this custom column
now, so let's select that. For Advanced Options, I want to go with don't aggregate, and click
on OK, and I have the correct view. OK, so we're done with this. We can update the name and
let's send this to Excel. Home, "Close & Load To". I'm going to put it right here on the existing
sheet so we can compare. Click on OK and this is our Power Query solution. Let's also test this.
I'm going to add Nightfire, Finance, Leila. This one should get automatically updated. Let's check,
I'm right there, and this one will get updated after I press Refresh. Another way of solving this
is with PowerPivot and DAX. Let's take a look at that solution on another sheet. So, I've copied the
table over. This time I called it "TProject2." The first thing we're going to do is send this to the
Data Model. So, I'm going to go to the Power Pivot tab and add to Data Model. This is going to bring
up Power Pivot and I could go ahead and write my measure here, but I'm not going to do that
because first I want to create the pivot table. And then we can think about the type of measure
that we need. I'll put the pivot table right here. Let's go to the Insert tab, pivot table, and I'm
going to insert it from the Data Model. I want it on the existing sheet. Right here is fine. Click
on OK. This is "tProject2". What I want to see in the rows here is the project and I want to
have departments in the columns. Now, I want to have person in the values, but we know that if
I bring person right now to the value section, it's going to convert it to numbers, right? By
default, it's going to give count of person. Well, that's not what I want. Instead, I want to go
through the table here or basically the values in the Person column and I want to concatenate them.
Because I'm going to be iterating over a table, there is a function I can use here that's called
CONCATENATEX. Let's create our measure. I'm just going to right-mouse click here and add measure.
Call that measure "Person Allocation". The function I need is CONCATENATEX. This function
needs a table to iterate over. Well, I could use the entire project table and let's do that in
the first round. So, it was called "TProject2," it's right here. Then I need the expression, well
my expression in this case is what I want returned which is the Person column. So let's go
with "Person" here. Then I need the delimiter and that's going to be in quotations and with the
"&" and space and close bracket. OK, so let's make this bigger. That's basically my function. Let's
check it, all good. Click on OK. Now let's bring it to the values section and it works great.
I see Alissa here, Richard, here for sales, I have two people and I automatically get these
grand totals. Now, I can remove the grand totals from view, in this case, because it's a small data
set, but I'd rather improve on the tax measure and make the grand totals just not show right,
because they're irrelevant in this case. But this is what I need to take care of is that I have
grand totals in the rows and in the columns, right, on both sides. I need to suppress them on
both sides. OK, so let's go back to our measure. Right-mouse click and edit the measure and let's
optimize this. Before I do the concatenation, I'm going to use an "IF" and I'm going to check
whether I'm at a grand total cell or I'm somewhere inside the pivot table. Well, when I'm inside the
pivot table, I only have one value here for the project and also one value for the department.
But, I can't use the function HASONEVALUE because it's still going to give me grand totals
in cases like this one here, for Imogen, where I have just one value for the project and one
value for the department. The other departments are empty so this one would still return a grand
total. Instead, I have to use another function that HASONEFILTER or I can also use
"ISFILTERED in this case, but I'll just go with HASONEFILTER". Now before I do that, I'm checking
for two things, right? I have two grand totals, so let's start with an AND function then use
HASONEFILTER, let's go with the Project column first, close bracket and the second part
of my logical test is HASONEFILTER, this time, for the Department. Now, I'm going to close this.
Let's organize this a little bit better. I need to close the AND logical test here, so let's close
the bracket, press comma. I'm just going to press Shift + Enter to break this up. Then, for the
results, if true, I want my CONCATENATEX function, and for result if false, I want to have a
blank. So, I'm just going to close this off. Now, another thing I can also improve on is
this part here. I don't necessarily need to iterate over the whole table. I can iterate over
the values, basically the unique values I have in the Person column. So we can also change this to the VALUES function and reference the Person column. Okay, so let's just make sure
everything is fine. Check DAX formula; it doesn't have errors and click on OK.
And now, we get to suppress the grand totals. Now, if we think this true, how this formula
works. Let's say we're right here. We have a single filter for Project and for the
Department. The filter context here is going to filter our underlying table, which is this one,
to only show SeaFire. Okay, so only these lines, and only the "Marketing" department. So that will
be only this line and this line. The CONCATENATEX function is going to go ahead and get the unique
values of the Person column, which is these two, and then it's going to concatenate them
together with an ampersand as the delimiter. Now, let's just quickly test this. I'm actually
going to add a brand new project. Let's just call it "New." It's going to be in the "Finance"
department, and I'm a part of it. So let's right-mouse click and refresh this, and I pop up
right here. If all of a sudden, I allocated to the Nightfire project, let's just right-mouse click
here and refresh this. And I should be in here. Okay, so this concludes our Power Pivot version.
As you can see in Excel, there is more than one way to solve a problem. Now your approach might
be completely different to mine, and that's fine, as long as the answer is what you want. I hope
you enjoyed this video. As usual, if you did, don't forget to hit that thumbs up, and if
you haven't subscribed to our community here, do consider subscribing. And I'm
going to see you in the next video.
