Combine Data From Multiple Excel Files into a Single Excel File - With Dynamic Columns and Sheets

how your Magendie bad goodly once again and in this video I'm gonna talk about that how can you combine data from multiple Excel files into a single excel file now I've spoken about this topic in my youtube channel in the past as well but what is different this time is that I'm gonna talk about two peculiar challenges there are a lot of people face which I did not address it in the last time when I was making a video challenge number one is that when you're combining data from multiple Excel files let me show you now the problem could be that in these files the data could be stored on different sheet names the sheet name may not be sheet 1 or the same sheet in all the excel file so how do you handle that not only even that you could also have multiple sheets in one file but less sheets in the other file that is problem number one problem two is that in any of these excel files on any of the sheets that are there a new column could be added and how does the data automatically capture the new column that has been added into the data set that you're trying to combine I'm gonna write slight bit of M code not too much we mainly use the user interface of power query but we're gonna get the job done so let's just take a look at how do we combine data from multiple Excel files so we have these files ABCDE let me just open one or two files and show you what I'm trying to combine so let me just open file a and we have file a open if you take a look at the bottom we have sheet 1 and she two standard names but we have these columns now as of now sheet 1 and she two also have the same columns that means they'd sales have customer amount profit in the region they get replicated again right here now let's just take a look at the other excel file which is Excel file B and if you take a look at this excel file although the column names are the same date sales rep custom ROM amount profit and the region but the sheet name is different so this is this sheet and then you may have a new data so that sheet names can be different also there could be multiple sheets in one of the excel file which I don't have it but there could be possibly so we also going to handle that after we handle the sheet name issue we then going to handle the column Edition issue later so I'm in a blank Excel workbook which is called book 1 I'm gonna go to data I'm gonna click on get data from file from folder now it obviously is going to ask me the path of the folder which I'm gonna supply and I'm just gonna paste the path right here test combined say ok and like you would expect this would give me the list of all the files which are there in the particular folder good to go I'm not gonna click on combine neither am I gonna click on load I'm gonna click on transform data to enter to power queries window now once I am there in the power query window typically what most people would do is they would end up clicking on the double arrow and then they would combine the data from all the files but this would allow you to combine the data from the sheet name which is the same name in all the excel files but that's not the case that we have so we gonna follow a bit of manual approach write some M code and try to combine the files the first thing that I'm gonna do is I'm gonna extract all the sheets from the binary binary is nothing but a file I'm gonna extract all the sheets from the binary so I'll click on add column I'll click on custom column and I'm gonna write a new formula the formula that I'm gonna write is Excel dot workbook and start the bracket and Excel dot workbook accepts a workbook as binary and you can see that in the content column we have binary binary binary binary nothing means a file and I'm just going to write the content column here close the bracket and press Enter now what this is going to do is this is going to go inside of this content column find a binary which is nothing but an excel file get all the sheet names and all the data that the file has although this is an excel file but in your case this could also be a CSV document or a text file in that case you would not use Excel dot workbook rather you will use CSV dot document I'm gonna press on okay in this case and a new column gets inserted and if I just hover my mouse on top of this and just click on the empty space here I will see at the bottom that it starts to show me both the sheet names that I have right so let's just expand this column so I'm just gonna click here and expand this column uncheck on the name prefix all these columns are good I'm just gonna click on OK and since every excel file had two sheets it has gotten me all the sheet names and it has also gotten me the data of every sheet we're gonna also keep the name of the sheet that which sheet name is the data coming from and we're also going to keep the name of the file that which excel file does this belong to alright the other thing that you will find here in this data is that you will see that data is there although but the headers are not promoted so what I'm gonna do is I'm gonna create another column and convert this table into a promoted header table so let's just take a look in the add columns tab I'm gonna click on custom column and I'm gonna write a small function called table dot promote headers and it is asking me for a table that means which tables should I take and promote the headers so I'm gonna say hey why don't you pick up the table which is better present in the data column so we have a table here we have a table here pick up all of these tables one by one and promote the headers so I'm gonna write data here close the bracket press ok and we now have a custom column which is where you will find the headers are promoted all right let's keep the custom column let's keep the name one column and let's keep the name column which is the name of the excel file right click remove other columns and I'm only left with the three columns that I have here now I'm gonna click on expand it it shows me all the columns that I have in all the sheets since we just had the same column in all the sheets I am even if I click on load more there is nothing that I'm gonna see I'm gonna click on OK and that's what I have now I can swiftly click on home and click on close and load and let me just also change the case here make it as a day and I can just go back here click on close and load and of course I will have all the data in front of me from all the Excel files no matter whatever is your sheet name in that excel file alright let's try two things I'm gonna go back to the folder I am going to go to file a in file a what I'm going to do is I'm gonna change the name of the sheet from sheet 1 to data sheet I'm also going to add column let's just call this as a blank column and I'm gonna write nothing in the column just save it I just want to see the column appears or not so I click on save close the workbook come here hit a refresh now let's just take a look that in the name one which is nothing but the name of the sheet data sheet starts to appear so that's good but you can see that no way here I'll start to see the blank column that I added to be able to get that column what you'll have to do is you'd have to go back to the query and at the step where you expand it you'll have to click on the gear icon and then you'll have to click on load more for that column to show up and then checkmark that column click on ok and then load back again the table and you will start to see the extra column that we added but this is a manual process what if the back in Excel files often have an additional column or they edit a call and remove a column and you want this table that is getting loaded to dynamically handle the columns that you're adding let's see how can we get that done so I'm gonna go back to the query and I'm gonna go back to the step where I am left with the table which has promoted headers the name of the sheet and the name of the excel file now what I'm going to do is I am going to extract all the column headers from all these tables as a list here why don't you take a look so I'm gonna click on add columns tab and click on custom column it says do you want to insert a step in between yep I don't mind that so I'm gonna reduce a formula called table dot column names and from which table do you want to pick up the column names it is asking for a table the table is kept in the custom column this formula means that why don't you pick up the name of the tables which are kept in this column the custom column and give those column names as a list why don't you see what happens when I click on OK so you can see that it shows me a list if I click on the list not on the list on the side of the list it shows me all the columns that I have here now let's just take a look at do we have an additional column in the data sheet that we added or not so if I click here I start to see the blank column which is correct and if I click on the list as well I again start to see the blank column all right now what I'm gonna do is I'm gonna only keep this custom column with me so I'm gonna right click here remove other columns it says do you wanna answer the steps sure enough I want to insert a step now I'm only left with the custom column and I'm gonna expand all the columns so extract new rows sure enough and insert a step once again I get all the columns from all the tables now obviously since the column names are repeated what I want to do for this column list is I want to remove the duplicate column so come here and I'll say remove the duplicates click on insert and I'm only left with the unique columns that are possibly there in all the excel files and the sheets that I have the is a table as of now and I want a list let me show you what I'm trying to say so if you take a look at the expand column step it is giving you an error because we added a couple of steps which we're gonna fix but if you just take a look at this step and if you take a look at the inputs of this step the first input here is the name of the table then it asks you hey which column should I expand so I'm telling hey expand the custom column then there are two further lists here if you see a curly bracket that means a list it tells you what are the names of the columns that you want to expand and what you should rename instead so you can see that here is a list and here is a list although the second list is an optional list the first list is a compulsory list now if you see this list this list is hard coded that's why the additional blank column wasn't going through automatically but if you take a look at this this list that we have it's not a list as of num but we will make one but this list that we have here you can see the blank column got inserted automatically now what we do is we'll convert this into a list so that this list can be inserted right here right here let's just take a look so I'm gonna come back here I'm gonna right click here and I'm gonna say that drill down which will convert this into a list or you can just maybe come and start to type here custom CUSD over m dot 1 which will also convert this into a list any of these 2 methods is absolutely fine I'm gonna right click on the remove duplicate steps and I will say that I want to rename the step and I would like to call this step as not res remove duplicates but as column name list all right now let's just go back to this step and let's just fix this step so let's just see that way do we have our table this is where we had the table and after this we expanded the custom column so you will see that we have a custom column here and these are the vary tables that we would like to expand so I'm gonna tell the expanded custom that hey the table name which is the first input is not column name list but it's actually remove other columns that's where the full table lies so I'm going to put hashtag inverted commas remove other columns then the next input is the column to expand the column to expand this custom and that that is what is the input right here custom and then it asks me for a list us list is still hard-coded which I want to automate so I'm gonna write the name of the list column name list and the last part is not necessary so I'm just gonna close the bracket press ENTER and this will automatically get all the names of the columns in all the possible Excel files and the sheets that it could possibly find and of course we change the type of the date I'm just gonna click on home say close and load and we are now good to go let's just test the output before we close on the video so let's just go to some other file maybe C and over here I'm gonna add another column let's call it new column and throughout the column let's just add the value 1 I'm gonna save it and close it and come here and start to refresh now I should see here then you call him that I've added so right click here refresh your bingo we have the new column added and if you take a look at the drop down you're the only value that I will see is one if I select that there that value it will only appear in the C excel file only in sheet 1 I hope that solves a lot of the questions there are a lot of people were asking me a big shout out to Christian who requested me to make a video on this and thanks so much for watching this if you have any questions please feel free to put them down in the comments I'll be more than happy to help you out thanks once again you take care of yourselves bye bye you

Channel: Goodly

Views: 33,720

Rating: 4.9052629 out of 5

Keywords: Combine Data from Multiple Excel Files

Id: mZbD8aduIJU

Length: 12min 51sec (771 seconds)

Published: Sat Feb 01 2020