A common problem in Excel
is to combine information from different sheets, and create a report based on
this consolidated information. So let's take a look at
how we can append data from multiple sheets into a single table, or even a pivot table, and how we can also do it in a dynamic way so that if we add more
sheets of data to our file, all we have to do is
refresh our end report and the new information would
be immediately included. Let's take a look. (upbeat music) This video is a part of my
Excel Power Query course. Now if you'd like to learn
Power Query like an expert, right from the start, check
out the complete course, link to it is in the description below. In this example, we have
sales data by month. We also want to do it in a dynamic way, so when we get data for the next months, all we have to do is refresh our report and the new data's there. On each tab, we have an Excel table setup that starts with the word data underscore, and then the first three
characters for the month name, all the tabs have a similar layout. But notice something specific here. Sometimes we have gaps for sales document, and sometimes we also have
gaps for the customer. So if we're selling different
articles to the same customer and we have the same sales document, we are missing that information, so we need to account for
that in our transformations. Now, the other information
that we have on each tab is the grand total. So this is another thing we
need to take into account where we're combining all
these tables together. We know to be able to
combine these together, we have to send these
tables into Power Query. And one way to do that is to go to Data, From Table/Range, and create
a connection to each table. So then I'll be sending
this back to the workbook, but as a connection only, 'cause I don't want to duplicate the data, I'm only going to create
a connection to this. But then I have to repeat the
process for Feb, March, April. And if I have new tabs, I actually have to repeat
that process again, but I don't want to do that because he can get really annoying. I want this to be fully automatic. There is another way to get this done, so let me just first delete this. Here's how I can do it in one step. Go to Get Data, From Other Sources, and let's launch just a Blank Query. Inside the formula bar, type in equals Excel.CurrentWorkbook. We see it as the first
choice in the dropdown, select it, open bracket,
close bracket, press Enter, and we have all the tables that are sitting in our current workbook. If I click on the side
here, I can see the content. This is the name of the table here. Now notice also that I get
the expansion button here, which is great because
if I expand these tables, I'm going to get all the
data below one another. Now you could decide
to keep the name column to provide context for your data. In this example, though,
we don't need that because we have the document
date information down here, so we can actually extract the
month from the document date. So I'm only going to
keep the Content column. Right mouse click and
remove other columns. Now, before I forget, let
me give this query a name. I'll call it AllData. Now let's expand, uncheck use original column
name as prefix and OK. I get all the data
appended below one another. Now I can go ahead and
make my adjustments. Notice this time I don't
get the column headers inside the dataset once I append, that's because I'm
working with Excel tables. One thing I don't need is the Grand Total, let's go and filter that out. Filters does not equal Grand Total and OK. Next, let's fill up the gaps here. Highlight these two columns, right mouse click, Fill and Fill Down. Now let's take care about the types, Control + A to select everything, Transform, Detect Data Type. Double check if they're
okay, this looks good. For document date, let's adjust that to a date
only and replace current. Now, I also wanted to grab the
month from the document date. So let's go to Add Column, Date, Month and get the name of the month. And that's right here. Our steps are recorded,
let's send this to Excel. Let's just go with the default
and send this to a new sheet. And this is our consolidated report. I have data for January, February,
all the way down to June. This is all connected to my source data. So if some data change here, so let's say in January,
instead of 243, I get a one, let's just go and refresh
this to see what we get. Right mouse click, Refresh. The one shows up here, but
something is not right. Take a look at this,
we had a lot less rows. Right mouse click, Refresh. It's just getting bigger and bigger, why? The reason is this report is also a table and it's also being
included in our end result. So it's basically being
appended to the other tables. Let's go and take a closer look, go all the way back to the source step. That's my final report. It's being included in the append process. So in the next step when I
go and expand the content, that table here is also
being expanded and added. I need to exclude my end
result from the rest. Now this is up to you how you do it, you could use a filter
and say it doesn't equal, and type in your end report. Or, if you have a pattern,
like I do in this case, I'm going to go and apply a filter that has the table names
begin with Data underscore. Okay so anything that
begins with data underscore will be included. If there is a table that
doesn't begin with this, will not be included. Now let's go and update this. And now I can refresh again, and this table will not be included. Now you wouldn't have this problem if you created a pivot table out of this, but because we created a
table, it's getting included. Now talking about pivot tables,
let's actually load this instead of a table, into
a pivot table report, existing worksheet, A one is okay, and we're going to replace. Let's take a look at the
sales value by month name. Update the design to show in tabular form. And let's quickly update the
number formatting as well to just use a thousand separator. Okay, so things look good. Now does this update if I add July data? Let's try that really quickly. I'm going to right mouse click,
create a copy of June data. Let's just call it Jul. To be consistent, let's
double check the table name and also update it to Jul, and replace the document dates from six slash to seven
slash, Replace All. Okay, so now let's assume
this is our July data. Go to report, right mouse click, Refresh. And my July data is there. As you can see, it's really
easy to use Power Query to append the data here. In case you'd like to learn
more about Power Query and learn how to use it like
an expert right from the start, make sure you check
out my complete course, link to it is below. As always, don't forget
to hit that thumbs up before you leave, and subscribe if you haven't. And I'm going to see
you in the next video. (upbeat music)
