Combine Files With Inconsistent Columns In Power Query

so in this video we're gonna take a look at how we can combine multiple files in a folder when the files have inconsistent table names and inconsistent column heading names so I've got a folder here set up and it's just got two files in it just as a simple example and I'm just gonna open those both up and take a look at what's inside so here's file 1 and here's the data I'm interested in importing and so you've got a column here called common and a column called a and the table name is data table and then we've got some other stuff in here that we're not interested in and then in file 2 I've got the same common full common column heading and I've got another column called B and that table is called my data table and we've also got some other stuff in here so I'm gonna close those two and I'm just gonna copy this folder path containing those files and the usual way to import and combine files in a folder with power queries in the data tab go to get data and from file and from folder that's gonna allow you to combine multiple files from a folder so let's just pop in our path there and press ok and see what happens so power query has listed out the two files that I have in that folder and we can combine those using this combined button so we can combine and edit combined and load or combine and load too but I'm gonna use combine and edit because I want to see what power Cory does with this in the power query editor and I don't want to load it to the workbook yet so power query is going to try and combine the files based on a sample file so you can pick the sample file that you're going to use and the problem is whichever one we pick we're gonna have to navigate to the table we want to import and if we pick file one our table is called data table and if we pick file to our tables called my data table so let's just go with file1 and press ok and let's see what we've got so we've got a bunch of sample stuff based on our sample file and then we've got the query that tries to combine those files based on our sample and we can see what happens is the first file we get some data and in the second file we get an error because it's trying to find the table called data table and that file doesn't have such a table so it turns out we can't use the from folder query to combine files with inconsistent data so I'm going to close this query and discard that now let's see what happens when we just try and import one workbook so let's go to the data tab get data from file and from workbook this time and we can select any of those workbooks and import and then we can select our data table in this file and let's go to the Edit the query editor and let's take a look at our applied steps so we got a change data type you can just delete that don't need that and then we get this navigate step so let's just look at the source step before that and we can see that we've listed out all the table objects and sheet objects in her workbook and then this navigate step I was just navigated to that one particular table so I'm gonna delete that navigation step and let's take a look at the EM code that power query has produced for the source data so here we can see the path and file name of the file that we're importing and then we're using the file contents formula to get the contents of that file and then we're using the excel dot workbook formula to get a list of all the tables and sheet names in that workbook so I'm just gonna copy that and we're gonna use that in conjunction with the folder query to get exactly what we want so I'm gonna again close this and discard it and let's go back to the data tab and get data from file and a game from folder so let's just paste in our folder path and press ok and there we go listing of our files again but this time instead of combining the files we're just gonna go to edit and now we've got a list of the folder path along with the filename and so we can use that formula from our single workbook import to get a list of all the tables and sheets in the folder so I'm gonna add a column custom column and let's call this objects and I'm just gonna paste in that formula from my single workbook import and now instead of this hard-coded stuff in here I'm just gonna delete that so that's the folder path and file name and what we're gonna do is reference our folder path column and our file name column and we're gonna concatenate those together with a couple ampersands and a slash in between them so now if we press ok what we get is a table in each of those rows and that table contains rows of data for our sheets and table names so let's just expand that out and press ok and let's scroll over and see what we have so we've got a list of all the table names and sheet names from those workbooks in our folder now and so we can filter on just our tables so let's select just our tables and then we've got four tables in our two workbooks but we're only interested in our data table and our my table my data table so we can press ok and so however many files and tables you have that you want to import there's gonna be a bit of manual work in filtering on those particular tables and once we're done filtering we can then expand our data in the table so this column here contains the data in those tables so if we expand out that column let's make sure expand is selected and press okay then we get our data in those tables so notice that here I've got the data for my first files table and then here I've got the data from column B in my second file so I'm just gonna select all these columns and I'm also gonna select the folder path maybe and the file name and if I right click you can remove the other columns you now we've got all the data that we want combined and we're ready to just go into the Home tab close and load and close and load that to you the workbook and I'm gonna put this in the existing sheet and press ok so there we go that's how we can combine files in a folder when those files have inconsistent table names and column headings as well if you enjoyed this video make sure you hit the like button and subscribe to the channel for more upcoming videos on power query and excel in general that's it for this video see you guys in the next one

Channel: How To Excel

Views: 41,405

Rating: 4.7222223 out of 5

Keywords: Microsoft Excel, Excel, Microsoft, Tutorials, Power Query, Get & Transform

Id: mg_qLIQjjaM

Length: 10min 6sec (606 seconds)

Published: Fri Jan 18 2019