We have our sample data set here.
It includes division and region information in one column.
We have app and profit information.
Notice also we have totals in the middle of our dataset.
Now our aim is to create a pivot table report that has profit information by division.
So just profit by division.
We don't need region at all.
If we just convert this data set into a table and insert a pivot table, we're going to have problems
right?
Because we have total values here hardcoded in the middle. And we just have the division and region information
sitting inside one column.
So what we're going to do is to use power query or get and transform to clean up this data set before
we insert our pivot table.
Let's get started.
Number one: Convert the data below to a table.
Call it RawDiv.
Easy, right? Click anywhere inside the data set.
Use the shortcut key Control+T.
You can also go to the Insert tab and insert a table from there.
I'm gonna go with
Ok.
First thing I'm going to do is remove the table style to go back to my original formatting and I'm going
to call this RawDiv, press enter.
Number one is done.
Number two: We need to load to power query and clean up the data by: One is to remove these total fields
here
And two we want to split the first column into two columns.
One for division and the other for region.
Let's load first into power query.
So go to the Data tab and under the get and transform data section, click on From table.
This opens up the power query editor. So we had two tasks to do.
One was to remove the total value.
I'll start with this one first.
Now notice that the second column is always empty
whenever we have totals here.
So one way for this data set is to remove the rows where app is empty
But let's look at an option that we didn't look at before. And that's we're going to filter for this
column but only include rows that don't have the word "total" in them.
Click on this down button here.
Go to text filters and let's go with does not contain.
Right here, I'm going to type in total and click on
Ok.
My list is filtered to everything that doesn't include the word total in it.
Now our second task was to split this column.
So I'm going to highlight this and go with split column, by delimiter. Because the delimiter here is a
dash sign.
Excel automatically picked up the dash so it's giving me the custom option and it already pushed the
dash sign in there.
I'm going to go with
Ok.
But there is a problem here.
Can you guess what it is? I still have a space included.
If I click on this Australia and I highlight this, there is one space just before Australia. If I click
on game here and highlight this, notice there is one space after game.
So my delimiter wasn't just the dash sign it was "space Dash space".
Let's make sure we remove these trailing spaces.
I'm going to delete that last step and for this split column by delimiter, I'm going to go and edit
it.
I can remove it completely and redo it.
Or, I can go to edit here and update my delimiter.
All I need to do is add a space before and add a space after and then click on
OK.
Now let's just double check that this works, if I highlight Australia
there is no space before it.
If I highlight game, there is no space after this.
Let's just double check the data types here.
So this is text, this is text. But I want to update the column header so just double click it and then
let's just call this one division. Press enter, double click the second one. Call it
Region, press enter, app is fine.
Data type is fine.
For profit, I'm gonna change this to the currency data type.
All the steps are recorded here.
They can be changed and revised any time. Now we want to get this data in a pivot table.
So I'm going to go with close and load to. And directly load this into a pivot table report.
If you don't have Office 365, you have Excel 2016.
You don't have the pivot table option here.
Just load your data into a table and then create a pivot table off of that.
But I have the option.
I'm going to select it. Bring my pivot table in the existing worksheet and it says here in cell F10.
I'll just select that and click on
Ok.
Now I get my pivot table options opening up here.
I'm just gonna bring this closer to our report.
All I need to do now is to drag division here and to drag profit right here.
And I have my report for division by profit.
Of course, I will update this to make it easier to read.
I'm going to remove these field headers. Call this profit, space, enter. And update the number formatting
for this. I'm also going to automatically sort this from largest to smallest.
So the division with the highest profit is Game.
We actually did both
Number two and number three.
Now before we celebrate, let's just test this by adding a new division to this and making sure our report
updates accordingly.
Right on the bottom I'm going to add the health division. Just for Asia and add in this profit.
I'm also going to adjust some things. Remember in our original report up here utility was last, game
was first.
I'm going to go and change this. For Atmos utility,
I'm going to add in a very large number.
So let's just go with this.
Press enter. Now this data set has been updated.
Our pivot report needs to be refreshed for these to take effect.
Right mouse click, click on refresh. And we have utilities switch to the top.
And we have our health division in here as well. Ok so challenge was completed and tested.