AWESOME Excel trick to combine data from multiple sheets
123 Steps
106 followers
5 Video Views·Oct 18, 2022
So the data is all there, but not in the same sheet, like one month per worksheet.
Oh, well, this is the perfect thing for Power Query and a cup of coffee. Let's go.
This is how the Awesome Clinics patient log looks like. Every month, we maintain a new tab
in which we keep all of these columns. We have NHS ID, name, email, the date on which that person
or patient visited our clinics, initial diagnosis and the payment status. So we have a tab for July,
August and September. What we would like to do is combine all of this data in an automated way
into a final worksheet where we can see everybody in one big table. We could do this in a couple of
different ways, but let's just say this is the data file, so we'll keep it separate from our
output file. So I'm going to open a new blank workbook and this is where we will consolidate
all the data. For the moment, I'll just close this file and here we will go to data, get data
from file, from workbook, because this is our source system. We would like to get the data
from that workbook here. So I'll say from workbook and point to our patient log and then click on
import. This opens up your usual Power Query navigator screen from where I can get the data.
Now the data that I want is in not any one of the sheets, but it is in all of them. I want to
combine everything into one go. We could select multiple items and do it like this. What it will
do is it will get us three different data sets. We don't want that. We just want one data set that
consolidates everything. So for the moment, we'll pick any one of them. It doesn't matter which one
you pick. I'm just going to pick the very first one, which is July and then instead of load, we
will say transform data and this will open up the data worksheet for July. Now we don't want July
data alone. We want all months data. So I'm going to delete all of these steps all the way up until
we see the source step. This is where I can now see how my workbook looked at and what are the
tables that I have available. So what we will do here is we will use this sideways button to expand
the data so that we can see all the data in one view. We don't need these other columns. So first
up, I'm going to pick my name and data columns, right click and then say remove other columns so
that we just end up with name and data alone. And now we will use this expand button to expand
everything. It will expand. There is no column titles because of the way our data looks. This is
fine. We will just click okay and we will get all the data for both July, August and September as
in one view. We don't mean we can see quickly that we don't need data column one and two because
these are null. Likewise, in data column three, we got the awesome clinics had our patient log.
The real data always begins from row number six for each month. So there's a couple of null values
in the upfront. So what we could do is there are a couple of things that you could do. But if you
could, for example, delete these nulls, anything that is a null in the first name column, we can
delete and that will remove all of those empty rows. So that's what I will do. I'll just say
here uncheck null and then that will give you a cleaner cut of data. We will then remove data
column one, data column two as well. So we will end up with this. This looks to be fairly clean.
I got NHSID. The headings are here. There will be a repetition at the month of August and then
there will be a repetition again in the month of September. And if there is another worksheet,
it will again repeat. So what we could now do is we can go to this very first row and then say
use first row as headers so that this becomes my heading. And then at this point, I'll just rename
this as my month column. So we have month, NHSID, first name, last name, email, date, diagnosis,
and payment status. And now we can use this filter to find the NHSID and then delete it.
So it will remove any subsequent NHSID values so that only the actual high ID numbers remain.
All right. So at this point, our data is in a very clean shape. We got what we wanted.
And I'm going to name this query as all data. If you want, you can load it now. But if you think
there is another cleanup step needing, then you can add that. For example, what I will do is I'll
remove this diagnosis column. Let's just say for the final analysis, you don't need the diagnosis
information. Likewise, we would like to not see two names, but just a combination first name and
last name together. So I'll select these two, right click and then say merge columns. The
separator would be a space and then this merged column is name. So we'll get one name and then
their email address, the date in which they visited our clinic, whether their payment status
is paid, partly paid, etc. All good. And we can close and load this data now. And this will load
up nicely in the worksheet here, combining all the data from all those individual spreadsheet
tabs. It is a very powerful query because what it is doing is irrespective of how many tabs are
there, it will go through all of those tabs. It will combine everything and give you one consolidated
view of the data. Let's test whether this is working or not. We will make a note that there's
233 rows loaded. I'm going to open my patient log and here let's add a tab for October 2021 as well.
So here we have the October 2021 data added to the spreadsheet. Let's just quickly save this
and let's go back to the original file and refresh it now. So here right now we have 233. I'm just
going to trigger the refresh process and we get 300 rows. Those extra rows for October are now
pulled in and we can see this here. We can go to the October data and again it has done the
transformation of combining the names, removing that extra diagnosis column and everything is
available to us in a clean way. I hope that gave you some ideas on how to use Power Query.
If you have never used Power Query before or curious to learn how Power Query can help you
save time and automate tasks at your work, then please check out the video tutorial that is shown
on the screen. It talks about four powerful full-length examples of how to use Power Query
to automate boring data tasks. I'll see you there. Bye.
