Combine Data from Multiple Excel Files in a Single Excel Sheet - Part 1

how you urgently bad Goodley once again and in this video we're gonna take a look that how can you merge data from multiple Excel files and make a single master out of it while making a master excel file from multiple excel files is one of the most common data cleaning tasks there are people do in Excel let's see how can we automate that using power query which is one of the BI tools of Excel the modern Excel now I'm just gonna take take you over to my desktop where I have a folder called shared files and I'm just gonna click on shared files and let's just take a look at this folder so in this folder what we have is I have 4 or different files here year 2005 data 6 data 7 data and 8 data as of now these are Excel files these could be also text files or CSV documents now apart from these the excel files I also have a couple of other junk documents that are there in the folder because I'm assuming this is a shared folder and anybody and everybody can dump their stuff in them in the folder so what I have to do is I have to make a master out of the 4 relevant files that I have and I also have to filter out the other kind of junk files that I have that's one second once I make the master document which has the data from all the four years I also have to automate the entire process so that I don't have to do it the next time I have 2000 and maybe nine data so let's just take a look at the excel file what kind of data do we have just take a while to open all right if you take a look at the data the first column is your date the second column is your sales rep the third column is the customer amount profit and the region now and this is 2005 data so all the records are from 2005 about 140 odd record items here similarly if the exact same structure no difference in the structure you would have 2006 data seven data 8 data so I'm just gonna close it and let's just start with power query now my power query is in data tab because I'm using Excel 2016 if you're using the prior versions of Excel your power query most likely is going to be in the power query tab if you have installed it right so in the data tab I'm gonna go from get data from file from a folder because I have to merge files from a folder so I'll say from a folder and this is just gonna take a while for the power query to open up it gives me hey where's your folder path so I'm gonna say my folders kept on the desktop and the folders kept in the shared files and I'll say okay now as soon as I'm picking up this folder path if there are any subfolders in the shared folder that I've just showed you power query is also going to give me all the files in the subfolders as well right so just letting you know so I'm just gonna say okay and immediately as soon as I say that I'm gonna have the list of all the files in front of me but as of now this is just a list of files well I've got nothing to do with the list but I need the actual content inside the file so rather than pressing on combine or load I'm just gonna click on edit so that it opens up the power query editor from which is where from where I will kind of apply filters and get the relevant files now as of now you can see that I have all the files and their information now I want only these four files now there are two ways to go about it one way is that I go to the drop-down and I pick up the relevant files that I need ear 5 6 7 8 but if I apply the filters here manually that means checking or unchecking the items what this is going to do is this is only going to pick up these four files but remember that apart from combining these four files I have also had to set this up for automation so that when the 2009 data comes in I don't have to do that again so I'll play a trick and I'll kind of set up the filters smartly so that it removes the content or the files that I don't need other than actually picking the content that I need so I'm actually left with the content that I need so here is what I will do I'll go to the extension and I'll say that hey first of all I'm looking at only excel file so it'll just give me a bunch of Excel files now in the excel files also there could be relevant files there could be irrelevant files now how do I identify the relevant files take a look that the pattern is that my file name is starting with year - 20 so I'm just gonna make sure that I do that to apply another filter and I'll go to text filters and I'll say contains contains what contains - 20 now you have to be extremely careful about the way you type in power query because power query is case sensitive so if you type Y non-capital and because Y is capital e a power query is just gonna throw up an error all right I bless I press okay and you can see that on the right hand side the steps are being created this is sort of like a macro recorder but way more smarter then and then VBA all right so I have the four relevant files if you need any further intelligent filters that you want to apply to your folders to leave out the files that you don't need you have a lot of columns actually where you can go and apply a filter so if you wanted only a particular subfolder you can also apply the filter in subfolder paths and things like that all right now that I have the files which I absolutely need it what I'm gonna do is I'm only going to keep these files and remove the rest of the columns so you can see that binary means in excel file if I click here it shows me the excel file right here but I don't actually need the name the file I need the content inside of this so I'm just gonna keep this column for the relevant files which are left over now and remove the rest of the column so right click and say remove other columns and you see a double-headed arrow right here I'm just gonna say combine files and in just about a moment it's gonna give me a box and the box says that hey I'm trying to combine files what is your example file that means can I pick up the first file as my example file what it will do is it'll go inside the first file and take a look at the columns oh yeah I understand first column is date oh okay I understand second column is customer so this is what is going to be considered as an example file in rest all of the files are going to be combined as per the first file and it asks you that hey where is your data and every single file so Mary dies on sheet number one and I say on okay and in just about a few seconds is going to go inside every single file and combine the files and give it to you all the files together and you can see that all my files have been combined now when I say close and load this data is going to come right inside my my Excel from power query to my Excel decloak is gonna create a new sheet and if I just take a look at the drop-down I have 2005 data 6 data 7 data and a data and 590 rows have been loaded at in power query all right now let's just test we were trying to create two things the first thing was we were trying to create a master which we have successfully created the sec thing that we were trying to do is that hey let's just see that if I place another 2009 file in the folder does it get automated or not so let's just take a look so I'm just gonna go into shared files I don't have 2009 so let me just create 2009 in fact let me just copy 2008 and paste it one more time and call this as 2009 and also additionally I have to open this and I have to change the dates so I'm gonna add another column so I add another column and add 365 days to it which makes it 2009 copy that and then paste it right here and then delete this column and let me just save this file and close it now this is my 2009 file now assume that I just got it from somewhere somebody emailed it to me I downloaded this information from the web or server and I have placed this in my folder now what do I expect out of my power query I expect power query to repeat the same process again so go into the folder apply those filters that I applied remain with the Intelligent the d files that i need and add them and append them into this database so as of now I have 590 rows with 4 years of data I should have the fifth here as well how do I do that right click and I just say refresh and let's just take a look it's 753 rows loaded let's also reconfirm in the filter do we have nine data yes absolutely we have 2009 data so that's really awesome about power query now that's just one part of the problem now this is so good and sweet until unless you have absolutely the same kind of structure in our query so you just take a look I was talking about two problems with the same data structure now in the next video I'm gonna speak about the next problem that how do you actually combine files in a more real-time world where the data structures is not the same so sometimes you get some this type of data and then you have multiple files with different types of data structure data structure meaning the columns are not the same there is one empty column in the middle the column headers another same there are so few extra columns how do you deal with those anomalies and still combine the files make power query work and not only just make a master database also set this up for automation alright thanks for watching this and if you have any questions please put them down below do stay tuned in and I'm going to be discussing with uneven data structure in the next video thanks for watching take care bye bye

Channel: Goodly

Views: 201,950

Rating: 4.8594899 out of 5

Keywords: Merge data from multiple Excel files, Consolidate data from multiple excel files, append data from multiple excel files, Powerquery

Id: _jegiQkyC3s

Length: 9min 1sec (541 seconds)

Published: Wed Jun 27 2018