This lecture will give you a good overview about some of power queries very useful and simple to use
features.
After this lecture you should have a good feeling on what power query can do to transform your own
data and if it makes sense for you to dig deeper in this topic.
The aim of the power query section in the course is to show you the potential of power query.
Let's take a look at an example right here.
Our aim is to transform this data into a tabular dataset.
This is currently like a report that we received.
Imagine we receive this from an SAP add-in or an Oracle add-in and it comes in this format.
There is nothing we can do about the way we receive this.
Now we wouldn't be able to be flexible on our reporting. Because let's say we want to create a report
that shows the divisions. Right now to be able to do that
we have to sum up the values for division. And notice in the headers here, we actually have three of them.
We have the date, we have the scenario whether it's actual, outlook, or budget, and the type of KPI we are
reporting whether it's revenue, profit, or cash.
Our apps are sitting right here and our divisions are sitting right here.
So to be able to do different types of report on this using formulas will be very difficult. Because
it's going to be very difficult to use SUMIFS on this if our productivity apps is missing some cells here.
We also can't create a pivot table out of this dataset because it's not a tabular data set.
Let me show you the end result.
So you have a good idea on where we want to get to.
That's the final table we want to have.
So we have a separate column for date, a separate one for scenario.
Here we have actual outlook and budget information. One for KPI, division, app, and the final value.
So here we just have one column for value whereas here our values are all over the place.
Once we have the data in a tabular format it's very easy to create reports out of that.
We can create a pivot table out of it and we can also use formulas like SUMIFS on that
data set.
Now we've already learned some ways we can clean this data using standard Excel features. So we can fill
up these empty cells here using a technique we already learned in the course. But it's going to be very
difficult to do some of the steps. For example, splitting these into their own columns and transforming
all these values into one column.
Let's do that with power query.
First off let's go to the Data tab.
We want to take the data from here.
So one option is to transform this data into an Excel table.
Just press Control+T and say OK but then our data set will look like this.
Now obviously we can take away the formatting but the table always needs columns here. And let's say this
is not something we want to do to our dataset we actually want to keep the look like this.
We don't want to add those additional columns.
Another option is to use a range.
And if I hover over this, let's read the instruction.
It says we can use the selected Excel table or a named range.
We basically just have to give the range of our data a name. And if were planning to have more information
let's say we want to add in March, April, and so on. We can take in more columns into account.
And it doesn't matter if we forget to take in a lot of extra columns, we can always update the named
range later on as well.
And our query will update automatically.
In this example I'm just going to go until column AC, highlight this and give the range a name.
I'm going to call it MessyData and press enter.
Okay.
Now that we've given it a name, we want power query to use this name.
Notice if I'm just sitting in a cell and I click on From table range, what Excel tries to do is to
transform this data to a table.
But I don't want that.
I want it to take in my named range.
So I have to select my named range first before I click on that button.
So that named range was called MessyData, select, and then click. Now Excel knows that we want to bring
in that name MessyData.
But here first thing I'm going to do is change this name. Because this is going to be the name of the
final result and that's not going to be messy. It's going to be clean data.
Next thing Excel tries to figure out what to do with these columns.
We don't need that change type here.
I'm going to get rid of it because later once we have our actual columns, we're going to define the type
at a later stage. But it's no problem if you leave it in there as well. So let's take a look at some
of the steps. Number one, let's fill up these empty ones.
There's a great feature for this.
You find it under transform and it's called "Fill".
I'm just going to highlight the column I want to fill, click on fill.
Do we want to fill down or up?
We want to fill down. Because we want to copy these all the way down until the next name.
Well, how do we get separate columns for each of these?
Well, what I'm planning to do is to transpose this dataset. So that I actually bring these to their own
columns here.
But before I do that I want to merge these two columns because I just want to have one header.
I'm going to highlight this column,
hold down Control, highlight the other one, and click on "Merge Columns". What separator do I want
in between? Now you can click on any of these.
I'll just go with semicolon and click on OK.
Now I have one merged column that has my division and then it has the app.
What we're going to do now is to transpose this.
Just click on transpose.
It's going to put my merged column up here as its own header and it's going to give me these.
So the date scenario and the KPI in separate columns.
Now obviously this dataset is very long.
It's not my final result.
It's just an intermediate step.
Next step, I can give these headers. But before I do that I need to promote this line to the header. And I can do
that under the Home tab "Use First Row as Headers".
Now I can name these the way I want.
The first one is date, press enter. I can define the type for this. I don't need the time.
I'm going to go and change this to just date. The second one: actual, budget.
This is my scenario press enter, type is fine.
The third one:
That's my KPI
and type is fine.
Now here I have the division and my app. And now what I'm going to do is to bring these into the columns.
There is a great feature under transform that's called Unpivot columns and that's exactly what it
does.
It brings your headers into the data set.
Now I have the option to either highlight all of these and unpivot but because it's so many, I can do it
the other way round. I can click the ones I want to keep and then go to unpivot columns and say "Unpivot
Other Columns". Which means unpivot all of these.
Now I get these in the columns. I have one single column for my value which is great. But these would
be better to have them in separate columns. And just take a look at this.
I can split the column by delimiter.
What is the delimiter?
It's semicolon, click on OK.
Give these names.
This is my division, press enter.
This one is the app, type is fine for these.
This one I'm going to change to currency and that's it.
All the steps are recorded here.
I can go back and take a look at each step if I want. My end result is this.
Now I'm going to go and load it. So let's go back to home, close & load, close & load to. I could select
a pivot table if I want that directly creates a pivot table out of this.
But let's put this one in a table.
We could decide to make a pivot table out of this as well at a later stage.
So I'm going to go with ok
and that's my proper dataset. 720 rows were loaded.
Now let's take a look at what happens when we get March data.
Let's go back to our messy data set and let's assume we get new information.
I'm just going to copy this, paste it right here and just change this to the next month. And pull this
across.
Now all I have to do is go back to my report. Let's actually call this clean data and just check
how this gets updated.
Right now I have 720 rows loaded, one row for the header. Let's right mouse click and refresh.
Take a look at this.
It's loading, 1,080 rows were loaded.
So that's my March data in there automatically.
So we make a pivot table out of this. All we have to do is refresh the pivot table and we have the new
months in there. All the transformation was applied to the data. Now one last thing,
let's say you want to make a slight change to this.
You are not so happy that all the values are in one column.
You want to have a separate column for revenue, profit, and cash.
Let's go and update our query.
I'm just gonna double click to edit the query.
I'm going to select the KPI column and hold down control and select the value column
and now instead of unpivoting I'm actually going to pivot the column. Which is going to put this data
into the headers.
It tells me here use the names in column KPI to create new columns.
That's fine.
And it's going to take the values from the value column, which is great.
Click on OK.
I have a separate column for revenue, profit, and cash.
All steps are recorded.
Let's go and update our query.
Just close and load, it's loading and I have my updated query. So that's how easy it is to use some
of power query's simple features to transform your data sets.