Combine Data for Multiple in Excel 2020

Intro:

today I am talking about How to create combine data for multiple Excel

Combine Data for Multiple in Excel Today let's take a look at a common task you might have already come across, which is to combine multiple Excel files into one file. So for example, let's say you sent out a template to your colleagues to collect some data. You got them in separate files. Now you want to combine them. Basically, you want to consolidate or append the data in one single file. One solution has always been VBA, but this time we're going to use a simpler approach. We're going to use Get & Transform, also known as Power Query, from the data tab. (air whooshing) (bubble popping) My aim is to combine the data from these files by directly connecting to the folder. Now, there are a few requirements. I don't want to include any files that don't have _Data in it, and I also want to be sure to exclude any non-Excel files. Let's quickly take a look at the content of the files. I have cells information for a specific month. The data is not in an Excel table. The structure of the files, though, are identical. They all have identical headers. Now, I want to make sure I exclude this file. Now, what want I want

Fore More Details Visit More Side:

Combine Data for Multiple in Excel my end result to be is a PivotTable that combines the data from these files. I want to get a summary sales report by a company and customer. Let's open a blank workbook. Go to Data + Get Data + From File + From Folder. Browse for the folder. My files are sitting right here. OK and OK. Now, Power Query goes ahead, creates a connection to the folder and checks what's inside that folder. If all the data was in a proper format, if it only included the files that I need, I could go ahead and Combine & Load the data directly to the workbook, but I need to make some transformations on this. So let's go ahead and transform the Combine Data for Multiple in Excel data. Before I combine these files, which we can see right here, what I want to do is to filter out the files that I don't need. So I set that I don't need anything that doesn't have _Data. So for text filters, I want to include everything that contains _Data and OK. I also want to make sure I've restricted everything to Excel files, Begins With .xls and OK. Those are my two steps. Now I'm ready to go ahead and combine the content. All I have to do is click on this double-down arrow, Combine Files, and Power Query is going to bring up the navigator, asking me which tab or which data I want to combine. The decision I make here is based on a Sample File. So it's picked the first file, which is Bere Kleid in that folder. From this first file, I get to pick the tab that I want, and let's go with OK. Now, Power Query's going to go and try to figure out what transformation it should apply to that sample file, apply that transformation to all the other files and append the data. This is all the work that it's done for me. Now, unfortunately at this point, it's not perfect because my data wasn't in perfect Excel tables. So I need to make some

Combine Data for Multiple in Excel adjustments but it's gone ahead and added all these steps. My last step before I click this button was this step. What it did is it added another filter. It filtered the hidden files. So if I happen to have one of these files open in the background, I'm not going to run into problems. It's going to be automatically filtered. Then it went ahead and created all these transformations, which is not that much here. It tried to figure out what it should do to that sample file to get it in a proper format so that it can apply that to all the other files before it appends them together. The function here takes this transformation and rolls it out to other files before it combines it here. That's what this step means. Then it went ahead and decided to rename this column to source that name, and then it removed all the other information about the file, and then it expanded our data and it applied a Changed Type. So now, we know we need to clean this before we load, but where do we do the cleaning? Do we clean the appended version, or do we clean the sample version? Now, it's up to you, but wherever possible, try to do the cleanup on the sample file level before Power Query appends them together. Now, some steps, though, you need to do on the appended level because only on the appended level do I have the file name, which I could actually use to grab the

Combine Data for Multiple in Excel company name. So if I go back a step, before we expanded the table, I could actually take care of that right after this. I could also take care of it in the end here. In my case, I'm going to take care of it here, so I'm going to rename this to Company + Insert a Step and I'm also going to extract the company name from the file name. Transform + Extract + Text Before Delimiter. My delimiter is an underscore, but I'm just going to add _Data to this and go with OK. Now, once it expands it, I have the correct company name expanded. Now, because my header changed, the Changed Type step doesn't apply. I'm actually going to exit out because it doesn't make sense to apply a Changed Type to these. I should clean these before. Let's take a quick look at the cleaning we need to do. First off, the column headers are repeated, and second, we have a bunch of null rows at the bottom of some of the data sets here. Let's clean that by cleaning the sample. Inside the sample file, I don't need the Promoted Headers there. What I actually need to do is remove the top two rows. Remove Top Rows, put a two and OK. Now, I want to promote the headers here, so Use First Row as Headers, and next, I want to take care of these null rows. So Remove Rows + Remove Blank Rows. Now, another thing it did automatically when I promoted the headers is it decided to change the type. That doesn't really help me, though, in the append side because I still need to do the

Combine Data for Multiple in Excel type here. So what I prefer to do is remove the Changed Type step from here, and get that correct in the last step here. So I'm just going to press Control + A to select everything, Transform + Detect Data Type and just double check that everything is correct. 'Kay, I have clean data. Let's go and load this as a PivotTable. So Close & Load + Close & Load To + PivotTable Report. Let's just put it in the existing sheet here and OK. Now, let's analyze the sales values by customer and company. Let me just update the design here to show in tabular format. This way, I can see the total sales by each company to the customers and see which ones are selling to the same customer. Now let's say I get new data. Lucas Basics here decides to send their data, so I'm going to grab the file and drop it here. They've inputted data, they just have put the columns in a different order. So Sales Value's no longer in the end, it's in the E column instead of where the other ones have it, which is the F column. Is that going to give us problems? Let's check. Let's go right-mouse click and Refresh. We have Lucas Basics' data right here. The order of columns doesn't matter. What matters is to have identical column headers, because check this out. If I open Lucas Basics_Data

Combine Data for Multiple in Excel and instead of Sales Value "S" with a capital "S," I have a small "s," I'm just going to save this, and let's go back here and Refresh, the data's not going to show up. Now, for problems such as this, you need to decide where you want to do the cleanup. Do you want to cleanup the source file or is this a common problem that some headers could be capitalized, some headers aren't? Is this something you want to take care of in the Power Query side? If you decide to take care of it on the Power Query side, you could add a new step and capitalize all the headers before you combine them But to do that, you need to be familiar with M functions. This is something we're going to cover in the advanced section of the course. If you're interested to find out more my Power Query course, check out the description below this video. Now, what if you wanted your end result to be a table instead of a PivotTable? Well, all you have to do is change the load destination. Right-mouse click on the final query + Load To, select Table instead of PivotTable. Existing worksheet $A$1 is fine, and click on OK. It's asking us if we really want to remove this PivotTable. We do, and that's our table. All the data appended below one another. So that's how you can use Excel's Get & Transform feature to combine data from multiple files into one file. I hope you enjoyed this video. If you did, give it a