Excel - Combining Data from Multiple Workbooks (files) into One Worksheet - Basic Power Query

in this video I'm gonna show you a quick and easy way to combine multiple Excel files to one master file and I'm gonna show you in which cases it's not gonna work so let's see what I've got here so I have this folder that's in my C Drive excel data folder combined data and basic that's the folder and in here I have three excel files but one file to file three if I go ahead and quickly open all of this so you can see what's in them so basically it's a file with some data in it a couple of things to note here is that see all three files here have one tab called data and it's the same name for all three tabs and columns don't always match so if you look here the column names this one is missing this column brand for example and there could be some other differences we just have to make sure that the column names for the correct columns match they don't have to exist for all of them this method that I'm gonna show you is gonna work in this case so if you set your files up this way you make sure you have just one tab in each file and the tab name is exactly the same this will work if you have more complicated cases with multiple tabs in each file or different tab names in files I'm gonna do another video which is gonna be more advanced and I'm gonna go over a lot of different examples to do that but in this video we're gonna go with this basic way of structuring the files so it actually works I'm gonna go ahead and close these and all we need is just all of these to be in a folder together like this and just a blank Excel workbook so what I'm gonna do I'm gonna go under this data tab under this data tab there's this section get and transform data if you don't have a section like this then you're probably in earlier versions of Excel like 2013 2010 or so on so in those cases you can install an add-on power query to actually get this as well it's not gonna be gettin transformed it's just gonna be an extra power query tab here on top but you still get the same functionality so you can still do this so for me I'm gonna go under get data from file and choose from folder option this is gonna load and this is gonna show up now we have to browse and choose the folder where the files are so I'm gonna click browse so here's my C Drive and here I have Excel data combined data and the folder is called basic I'm gonna hit OK that's the path I'm gonna hit OK again so it's gonna show this and if you look here it basically shows those three files but it also gets all this other stuff and these are some hidden files that we don't want to combine or touch so we need to exclude them from these results what I'm gonna do I'm gonna click transform data it's gonna load this so here we have this thing called attributes so I'm gonna click on this expand icon I'm gonna unselect all of this and I'm gonna select kind and hidden I'm gonna hit OK so that's gonna add this two columns now I want to make sure I only have Excel files so that's why I have this kind column I'm gonna click on this uncheck all of this stuff and just click excel file now if you open this and you only have just excel file and you don't have this extra options then it's not gonna let you actually do this the correct way so your plan B if you don't have multiple options is this you're gonna have to go under this text filters and choose equals and here type excel file heed ok that's gonna get us here so the next part is the attributes hidden so see we have these files that we couldn't see when we look in our folder here because those are hidden files so we don't want those so the ones that are true for hidden we want to remove those so again I'm gonna click here and uncheck the true option and again if you can select multiples then you're gonna do that same see go here and do basically equals and then do false that's what you're gonna do I'm gonna hit OK that works for me so now I have the three files that I actually need to work with file1 file2 file 3 so what I'm gonna do here I'm gonna simply just select this first two columns by pressing ctrl and selecting the second one and then I'm gonna right click and remove other columns so I just want to keep those to see this one is content this one is the name of the file that's good I'm gonna go ahead and click on this expand for content column so that's gonna load this and this is that tab data I'm gonna click on that and hit OK and just like that see I have all of that data combined now if you wanted to load this back to excel at this point you do close and load and close and load and that will load it back to excel which is what I'm gonna do give it a second I guess there it is so now see this is all of that combined together from those three files so once in a while when you do this you also want to have a column that shows you which file this data is coming from instead of just having this combined data so to get that you can right click on this one that we just made and click Edit and here see these are the list of things it did so I'm gonna go here and see this expand table that's one of the steps and then there's the step remove other columns so I'm gonna go here and get rid of this step this is not this first one see this first one is the one that I did remove other columns this is the one that it made the second one so I'm gonna remove that second amount I'm gonna click on that it says are you sure I'm gonna say so now if you look here we have see the name of the file and the rest of the columns as well so I don't need this anymore now I'm gonna click on this right click on bring move I'm gonna say yeah insert the step so now we have this extra column of the file name I'm gonna go back and load this back close and close and load and now we have our data combined so we can see which file that's coming from and all of this information combined from those three different files now the cool thing about this is that if you go to your files and make some adjustments to them so let's say I go to this file one and I'm gonna open it and I'm gonna scroll this down so we can see the background see this first one that gets us this Perry Neil Adidas that one I'm gonna go here and change that up so instead of adidas let's change it to New Balance I'm gonna save this if I close it I go back here I have to refresh this so I'm gonna click this refresh and see that applies it's still connected to it it will also work if we add more records so if I go back open that file one see we have the last record for file one is this Tony Roberson thing so I'm gonna go here and add another line and I'll just change the date for this one keep everything else the same save this and close it go back and reload now we shouldn't have 2019 here yet but if i refresh this see now we have both of those including 2019 so we can easily go back and add records to our existing files and gonna go reload and finally if we just add another file so I'm just gonna copy one of these files this is a new file I'm gonna open it let me change something here so I'm gonna change this sales rep for the first one I'm gonna call him Superman save this thing close it now I have this extra file maybe I'll rename this to call it extra file that's another excel file in our folder I can go back here and reload there it is now we'll load that one too it combines it automatically without having to do anything else just refresh and if we were to add some different type of files to this so let me just go here from here and try to copy some of this stuff which is some PDFs and that type of files to this folder close this I go back and refresh this that's not gonna affect it because remember one of the steps we need to filter - just Excel files so one thing I want to mention if you go here and acts out of this and you need to get it back what you're gonna have to do you're gonna have to click on this table you can go here under data tab another data tab you will have this thing that says queries and connections click on that that will show up again alright so if you need more advanced versions of this I will have another video coming but for this one this should do it thanks for watching please subscribe and I'll see you in the next video

Channel: Learn Google Spreadsheets

Views: 51,871

Rating: 4.8764844 out of 5

Keywords: Excel, combine, Data, from, Multiple, Workbooks, files

Id: mRznmfaxKDo

Length: 10min 40sec (640 seconds)

Published: Thu Aug 01 2019