AWESOME Excel trick to combine data from multiple sheets

hmm 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 gonna 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 gonna 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 gonna 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 ok and we will get all the data for both july august and september as well 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 header patient log the real data always begins from uh row number six for each month so there's a couple of null values in the up front 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 got nhs id 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 nhs id first name last name email date diagnosis and payment status and now we can use this filter to find the nhs id 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 um and i'm gonna 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 part 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 gonna 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 gonna trigger the refresh process and we get 300 rows those extra rows for october are now pulled in and we can see this here you 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

Channel: Chandoo

Views: 775,566

Keywords: chandoo, chandoo.org, Excel, spreadsheets, power query advanced, power query transform data, power query data cleaning, how to combine sheets in excel, excel combine all sheets to one, consolidate data in excel, automatically combine data from different sheets in excel, excel tabs get all data, combine data from multiple sheets, combine data from sheets into a single sheet, consolidate data from multiple sheets, merge data from multiple sheets into a single sheet

Id: k_ugshJ4wIw

Length: 7min 26sec (446 seconds)

Published: Tue Nov 23 2021