馃攳
Consolidate & Clean Multiple Excel Sheets in One Pivot Table - YouTube
Channel: Leila Gharani
[0]
A common problem in Excel
is to combine information
[3]
from different sheets,
[5]
and create a report based on
this consolidated information.
[9]
So let's take a look at
how we can append data
[11]
from multiple sheets into a single table,
[14]
or even a pivot table,
[17]
and how we can also do it in a dynamic way
[20]
so that if we add more
sheets of data to our file,
[23]
all we have to do is
refresh our end report
[26]
and the new information would
be immediately included.
[30]
Let's take a look.
[31]
(upbeat music)
[37]
This video is a part of my
Excel Power Query course.
[40]
Now if you'd like to learn
Power Query like an expert,
[43]
right from the start, check
out the complete course,
[46]
link to it is in the description below.
[49]
In this example, we have
sales data by month.
[52]
We also want to do it in a dynamic way,
[55]
so when we get data for the next months,
[57]
all we have to do is refresh our report
[61]
and the new data's there.
[62]
On each tab, we have an Excel table setup
[66]
that starts with the word data underscore,
[69]
and then the first three
characters for the month name,
[73]
all the tabs have a similar layout.
[76]
But notice something specific here.
[78]
Sometimes we have gaps for sales document,
[81]
and sometimes we also have
gaps for the customer.
[85]
So if we're selling different
articles to the same customer
[89]
and we have the same sales document,
[92]
we are missing that information,
[93]
so we need to account for
that in our transformations.
[97]
Now, the other information
that we have on each tab
[100]
is the grand total.
[102]
So this is another thing we
need to take into account
[105]
where we're combining all
these tables together.
[108]
We know to be able to
combine these together,
[110]
we have to send these
tables into Power Query.
[113]
And one way to do that is to go to Data,
[117]
From Table/Range, and create
a connection to each table.
[121]
So then I'll be sending
this back to the workbook,
[125]
but as a connection only,
[127]
'cause I don't want to duplicate the data,
[129]
I'm only going to create
a connection to this.
[131]
But then I have to repeat the
process for Feb, March, April.
[135]
And if I have new tabs,
[137]
I actually have to repeat
that process again,
[139]
but I don't want to do that
[140]
because he can get really annoying.
[142]
I want this to be fully automatic.
[145]
There is another way to get this done,
[148]
so let me just first delete this.
[151]
Here's how I can do it in one step.
[154]
Go to Get Data, From Other Sources,
[157]
and let's launch just a Blank Query.
[161]
Inside the formula bar,
[163]
type in equals Excel.CurrentWorkbook.
[168]
We see it as the first
choice in the dropdown,
[171]
select it, open bracket,
close bracket, press Enter,
[176]
and we have all the tables
[178]
that are sitting in our current workbook.
[181]
If I click on the side
here, I can see the content.
[184]
This is the name of the table here.
[187]
Now notice also that I get
the expansion button here,
[190]
which is great because
if I expand these tables,
[193]
I'm going to get all the
data below one another.
[197]
Now you could decide
to keep the name column
[200]
to provide context for your data.
[203]
In this example, though,
we don't need that
[206]
because we have the document
date information down here,
[209]
so we can actually extract the
month from the document date.
[213]
So I'm only going to
keep the Content column.
[216]
Right mouse click and
remove other columns.
[219]
Now, before I forget, let
me give this query a name.
[223]
I'll call it AllData.
[225]
Now let's expand,
[227]
uncheck use original column
name as prefix and OK.
[232]
I get all the data
appended below one another.
[237]
Now I can go ahead and
make my adjustments.
[239]
Notice this time I don't
get the column headers
[242]
inside the dataset once I append,
[245]
that's because I'm
working with Excel tables.
[248]
One thing I don't need is the Grand Total,
[251]
let's go and filter that out.
[253]
Filters does not equal Grand Total and OK.
[260]
Next, let's fill up the gaps here.
[262]
Highlight these two columns,
[264]
right mouse click, Fill and Fill Down.
[267]
Now let's take care about the types,
[269]
Control + A to select everything,
[272]
Transform, Detect Data Type.
[275]
Double check if they're
okay, this looks good.
[279]
For document date,
[280]
let's adjust that to a date
only and replace current.
[285]
Now, I also wanted to grab the
month from the document date.
[289]
So let's go to Add Column, Date, Month
[293]
and get the name of the month.
[296]
And that's right here.
[298]
Our steps are recorded,
let's send this to Excel.
[302]
Let's just go with the default
and send this to a new sheet.
[306]
And this is our consolidated report.
[310]
I have data for January, February,
all the way down to June.
[316]
This is all connected to my source data.
[319]
So if some data change here,
[321]
so let's say in January,
instead of 243, I get a one,
[328]
let's just go and refresh
this to see what we get.
[332]
Right mouse click, Refresh.
[334]
The one shows up here, but
something is not right.
[338]
Take a look at this,
we had a lot less rows.
[342]
Right mouse click, Refresh.
[346]
It's just getting bigger and bigger, why?
[349]
The reason is this report is also a table
[354]
and it's also being
included in our end result.
[357]
So it's basically being
appended to the other tables.
[361]
Let's go and take a closer look,
[363]
go all the way back to the source step.
[366]
That's my final report.
[368]
It's being included in the append process.
[371]
So in the next step when I
go and expand the content,
[375]
that table here is also
being expanded and added.
[380]
I need to exclude my end
result from the rest.
[385]
Now this is up to you how you do it,
[387]
you could use a filter
and say it doesn't equal,
[391]
and type in your end report.
[394]
Or, if you have a pattern,
like I do in this case,
[397]
I'm going to go and apply a filter
[400]
that has the table names
begin with Data underscore.
[406]
Okay so anything that
begins with data underscore
[409]
will be included.
[411]
If there is a table that
doesn't begin with this,
[414]
will not be included.
[417]
Now let's go and update this.
[420]
And now I can refresh again,
[422]
and this table will not be included.
[425]
Now you wouldn't have this problem
[427]
if you created a pivot table out of this,
[430]
but because we created a
table, it's getting included.
[434]
Now talking about pivot tables,
let's actually load this
[438]
instead of a table, into
a pivot table report,
[442]
existing worksheet, A one is okay,
[444]
and we're going to replace.
[447]
Let's take a look at the
sales value by month name.
[452]
Update the design to show in tabular form.
[457]
And let's quickly update the
number formatting as well
[460]
to just use a thousand separator.
[464]
Okay, so things look good.
[466]
Now does this update if I add July data?
[470]
Let's try that really quickly.
[472]
I'm going to right mouse click,
create a copy of June data.
[477]
Let's just call it Jul.
[480]
To be consistent, let's
double check the table name
[484]
and also update it to Jul,
[487]
and replace the document dates
[490]
from six slash to seven
slash, Replace All.
[497]
Okay, so now let's assume
this is our July data.
[500]
Go to report,
[501]
right mouse click, Refresh.
[504]
And my July data is there.
[506]
As you can see, it's really
easy to use Power Query
[509]
to append the data here.
[511]
In case you'd like to learn
more about Power Query
[513]
and learn how to use it like
an expert right from the start,
[517]
make sure you check
out my complete course,
[519]
link to it is below.
[521]
As always, don't forget
to hit that thumbs up
[523]
before you leave,
[524]
and subscribe if you haven't.
[526]
And I'm going to see
you in the next video.
[528]
(upbeat music)
Most Recent Videos:
You can go back to the homepage right here: Homepage