Insomnia in Merritt Island. How would you solve this problem in Excel? I have a solution, but it can't possibly be the best solution. We are playing for Excel Guru patches. Download the data from https://www.mrexcel.com/download-center/2020/02/Podcast2316Data.xlsx and let me know how you would solve it.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2316.
How would you do this?
He will come back to the MrExcel Netcast. I'm Bill Jelen.
It's a Sunday. It's 4 in the morning.
I'm starting today, just like I start almost every day where I go through emails and I look for some interesting Excel problem that I can solve.
It's a great way to start the day, but I find that here the darkness of Merritt Island at four in the morning.
Sometimes I managed to just get trapped down a rabbit hole with some horrible solution.
And so today, Sunday, I want to bounce this problem off of you and I want to hear how you would solve this.
I'm sure there has to be something far easier than what I came up with.
Alright, so I get an email from someone who had been one of my seminars and they have the data that looks like this and they say this date is coming.
It's a summary from many sheets, right?
And what they have here there show me a sample of data with just four employees and they want to twist that data into this table down here. So see these categories.
Those appear along the left hand side and that employee name.
From G3 is going to get repeated here in column B and Q1 Q2 Q3 Q4 It's funny the employee name that 18 is actually the some of these four.
I don't have formulas there, but it's working out everywhere that if I add up those four cells so it's kind of weird that we're getting the employee total 1st and then Q1 Q2 Q3 Q4. And this is weird.
It's like I want to Unpivot, but I don't want to unpivot every column.
I want to unpivot chunks of columns.
Alright, remember it's 4 in the morning and look before I start. Think about this. If this was your data.
In fact, download the data output link down there in the YouTube description. How would you solve this?
I don't want to cloud your thought process with the horrible set of steps I came up with.
What would you do to twist that data at the top into the data at the bottom. Now here's what I'm going to do.
I'm going to take this data.
I'm not going to make it into a table because you know who knows, that might cause problems.
I'm just going to name the range so I choose the data and we'll call it ugly data.
Great name for the data, right?
And then Data, From Table or Range.
And here we are in Power Query.
Right now automatically they promoted the headers and change type. That's great.
We're going to let that go.
I want to keep the category description along the left hand side and then unpivot everything else.
And in fact I think I'm only going to want to keep Q1 Q2 Q3 Q4 if somehow...
I can manage to get that employee name out like I don't need that total column, so I start here, transform, unpivot the other columns.
Alright, now look at this. This is interesting.
So the columns that were going across started out Q1 Q2 Q3 Q4 and then Q1 underscore 1, Q2 underscore 2, Q3 underscore 3.
And it just keeps adding more column numbers, apparently because. Um? Uh.
That must have happened here, yeah?
OK. Got it.
Well, my next thought process is I want to break this column into two columns.
The name in one column and the quarters in another column.
But that underscore is really going to is going to cause problems, so I choose that Column.
Split Column, by Delimiter.
Oh - they even got it.
At the leftmost delimiter -- so the first underscore we find -- click OK.
And have an attribute one attribute, two, that's the underscore one. I don't need that. Remove that.
And then rename this.
To be text value.
All right now it looks pretty simple here.
If its length of two, then it's a quarter.
If it's not a length of two, then it's an employee name.
Alright, so I want to basically in Excel this be easy equal =IF( the LEN( of this is equal to 2 then move it over to another column, otherwise don't.
But I have to tell you I'm really bad at these functions in Power Query so. I'm going to create a custom column.
I'll call it Length.
And while it's the L E N function in Excel, here is the Text Dot Length.
Of that text value.
Alright, no syntax errors have been detected.
That's good, let's click OK.
There we are.
OK, now I need to say if this is 2 then I know it's a quarter if it's more than two.
Then it's a name and I want to move that name over into a new column and so the rule is going to be if the length is greater than two, then bring the name over here to a new column.
Otherwise put the word null in there.
And where I'm headed, I know that I'm going to use Fill, Fill Down to copy those names down into the null cells.
So under Add column we are going to create a new conditional column.
It's going to be called Name.
And when I say if the length Is greater than 2.
Then I want to bring The text value over and get text value after a little drop down Select column.
And say text value.
Otherwise, let's see how I'm going to put the word null in there.
I'm just hoping I can put null. Click OK perfect - it actually gave me null values.
That's beautiful now from there.
I have the name separated out Transform, Fill, Fill Down right? Yeah, that's good.
That's some success right there?
Because now I have the employee name on every single row.
Alright, so we'll move that name over here to the left of text value.
And remember these employee totals here where it says employee one, employee two employee three.
Those are totals that I don't need. That 18 is 14 + 4 + 0 + 0 - I don't need that anymore.
So I'll filter this and I literally just want to keep Q1 Q2 Q3 Q4. All the other employee names.
I'm sure there's going to be a lot more of them - will go away good.
Right and then I want to get rid of Department totals in this column and this is tricky.
Well, here in text value was able to choose the four things I wanted to keep.
Here I want to be careful to choose the thing I want to get rid of, so I'm going to use the Text Filters, Does Not Equal.
And here you don't need quotes Department total.
Click.
OK.
Alright, so there's our description. There's our names.
There's are quarters, and the value. We don't need length.
That was a temporary column. I'll remove that Alright.
And while I use unpivot a lot in power query I've never actually done the opposite, which is the pivot that column I need to take these quarters and make them go across.
So here it is, right here on the transform Tab, Pivot Column. I'm going to choose ...
that's the column I want to pivot. We say Pivot Column.
Use the names in column Text Value to create new columns and the values column is going to be Value.
Advanced options good, so it's automatically choosing to Sum.
I guess we needed, you could do any other standard calculations there. Click OK.
Hey Alright Q1 Q2 Q3 Q4.
It seems a little screwed up that the data has been.
Like employee 1 is not altogether.
That's easy to fix though. On the home tab.
A-Z to sort.
So now we have employee one and then the various items within.
Employee one boy, they're not in order anymore and I can't sort them because they, you know, I can't sort by a custom list.
All right, we're just going to let that let that go.
Last thing we have to do out here on the far right hand side is add a column that sums Q1 Plus Q2 Plus Q3 Plus Q4. So add a column.
Add a custom column we'll call it Total.
And an equal.
Q1 plus Q2 Plus Q3 Plus Q4.
Click OK.
All right and then Home, Close and load.
Alright, so there's employee one various categories for employee one, Q1, Q2, Q3, Q4, totaling up to 56. Alright, so I think that's it, but.
Wow, there has to be some easier, simpler way right?
So if you have one - down in the YouTube comments, tell me how to do this. I don't know. Is there a prize here?
Bragging rights, an Excel Guru Patch. Who knows, this is brand new feature.
Just let's see if we can have some fun with this one. Buy the book.
Well hey, let's talk about Ken Puls book M is for Data Monkey.
Everything I learned about Power Query. I learned from here.
Which is great if my solution is the best solution, but I guess it's horrible if there's some...
just...
blindingly easy thing that I can't see it. Sunday.
Usually I'm giving you tips.
Today, I'm asking you for tips Please subscribe and ring that Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast. from MrExcel.
How would you do this?
He will come back to the MrExcel Netcast. I'm Bill Jelen.
It's a Sunday. It's 4 in the morning.
I'm starting today, just like I start almost every day where I go through emails and I look for some interesting Excel problem that I can solve.
It's a great way to start the day, but I find that here the darkness of Merritt Island at four in the morning.
Sometimes I managed to just get trapped down a rabbit hole with some horrible solution.
And so today, Sunday, I want to bounce this problem off of you and I want to hear how you would solve this.
I'm sure there has to be something far easier than what I came up with.
Alright, so I get an email from someone who had been one of my seminars and they have the data that looks like this and they say this date is coming.
It's a summary from many sheets, right?
And what they have here there show me a sample of data with just four employees and they want to twist that data into this table down here. So see these categories.
Those appear along the left hand side and that employee name.
From G3 is going to get repeated here in column B and Q1 Q2 Q3 Q4 It's funny the employee name that 18 is actually the some of these four.
I don't have formulas there, but it's working out everywhere that if I add up those four cells so it's kind of weird that we're getting the employee total 1st and then Q1 Q2 Q3 Q4. And this is weird.
It's like I want to Unpivot, but I don't want to unpivot every column.
I want to unpivot chunks of columns.
Alright, remember it's 4 in the morning and look before I start. Think about this. If this was your data.
In fact, download the data output link down there in the YouTube description. How would you solve this?
I don't want to cloud your thought process with the horrible set of steps I came up with.
What would you do to twist that data at the top into the data at the bottom. Now here's what I'm going to do.
I'm going to take this data.
I'm not going to make it into a table because you know who knows, that might cause problems.
I'm just going to name the range so I choose the data and we'll call it ugly data.
Great name for the data, right?
And then Data, From Table or Range.
And here we are in Power Query.
Right now automatically they promoted the headers and change type. That's great.
We're going to let that go.
I want to keep the category description along the left hand side and then unpivot everything else.
And in fact I think I'm only going to want to keep Q1 Q2 Q3 Q4 if somehow...
I can manage to get that employee name out like I don't need that total column, so I start here, transform, unpivot the other columns.
Alright, now look at this. This is interesting.
So the columns that were going across started out Q1 Q2 Q3 Q4 and then Q1 underscore 1, Q2 underscore 2, Q3 underscore 3.
And it just keeps adding more column numbers, apparently because. Um? Uh.
That must have happened here, yeah?
OK. Got it.
Well, my next thought process is I want to break this column into two columns.
The name in one column and the quarters in another column.
But that underscore is really going to is going to cause problems, so I choose that Column.
Split Column, by Delimiter.
Oh - they even got it.
At the leftmost delimiter -- so the first underscore we find -- click OK.
And have an attribute one attribute, two, that's the underscore one. I don't need that. Remove that.
And then rename this.
To be text value.
All right now it looks pretty simple here.
If its length of two, then it's a quarter.
If it's not a length of two, then it's an employee name.
Alright, so I want to basically in Excel this be easy equal =IF( the LEN( of this is equal to 2 then move it over to another column, otherwise don't.
But I have to tell you I'm really bad at these functions in Power Query so. I'm going to create a custom column.
I'll call it Length.
And while it's the L E N function in Excel, here is the Text Dot Length.
Of that text value.
Alright, no syntax errors have been detected.
That's good, let's click OK.
There we are.
OK, now I need to say if this is 2 then I know it's a quarter if it's more than two.
Then it's a name and I want to move that name over into a new column and so the rule is going to be if the length is greater than two, then bring the name over here to a new column.
Otherwise put the word null in there.
And where I'm headed, I know that I'm going to use Fill, Fill Down to copy those names down into the null cells.
So under Add column we are going to create a new conditional column.
It's going to be called Name.
And when I say if the length Is greater than 2.
Then I want to bring The text value over and get text value after a little drop down Select column.
And say text value.
Otherwise, let's see how I'm going to put the word null in there.
I'm just hoping I can put null. Click OK perfect - it actually gave me null values.
That's beautiful now from there.
I have the name separated out Transform, Fill, Fill Down right? Yeah, that's good.
That's some success right there?
Because now I have the employee name on every single row.
Alright, so we'll move that name over here to the left of text value.
And remember these employee totals here where it says employee one, employee two employee three.
Those are totals that I don't need. That 18 is 14 + 4 + 0 + 0 - I don't need that anymore.
So I'll filter this and I literally just want to keep Q1 Q2 Q3 Q4. All the other employee names.
I'm sure there's going to be a lot more of them - will go away good.
Right and then I want to get rid of Department totals in this column and this is tricky.
Well, here in text value was able to choose the four things I wanted to keep.
Here I want to be careful to choose the thing I want to get rid of, so I'm going to use the Text Filters, Does Not Equal.
And here you don't need quotes Department total.
Click.
OK.
Alright, so there's our description. There's our names.
There's are quarters, and the value. We don't need length.
That was a temporary column. I'll remove that Alright.
And while I use unpivot a lot in power query I've never actually done the opposite, which is the pivot that column I need to take these quarters and make them go across.
So here it is, right here on the transform Tab, Pivot Column. I'm going to choose ...
that's the column I want to pivot. We say Pivot Column.
Use the names in column Text Value to create new columns and the values column is going to be Value.
Advanced options good, so it's automatically choosing to Sum.
I guess we needed, you could do any other standard calculations there. Click OK.
Hey Alright Q1 Q2 Q3 Q4.
It seems a little screwed up that the data has been.
Like employee 1 is not altogether.
That's easy to fix though. On the home tab.
A-Z to sort.
So now we have employee one and then the various items within.
Employee one boy, they're not in order anymore and I can't sort them because they, you know, I can't sort by a custom list.
All right, we're just going to let that let that go.
Last thing we have to do out here on the far right hand side is add a column that sums Q1 Plus Q2 Plus Q3 Plus Q4. So add a column.
Add a custom column we'll call it Total.
And an equal.
Q1 plus Q2 Plus Q3 Plus Q4.
Click OK.
All right and then Home, Close and load.
Alright, so there's employee one various categories for employee one, Q1, Q2, Q3, Q4, totaling up to 56. Alright, so I think that's it, but.
Wow, there has to be some easier, simpler way right?
So if you have one - down in the YouTube comments, tell me how to do this. I don't know. Is there a prize here?
Bragging rights, an Excel Guru Patch. Who knows, this is brand new feature.
Just let's see if we can have some fun with this one. Buy the book.
Well hey, let's talk about Ken Puls book M is for Data Monkey.
Everything I learned about Power Query. I learned from here.
Which is great if my solution is the best solution, but I guess it's horrible if there's some...
just...
blindingly easy thing that I can't see it. Sunday.
Usually I'm giving you tips.
Today, I'm asking you for tips Please subscribe and ring that Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast. from MrExcel.
