Use the new Combine Binaries feature in Excel to combine all of the workbooks in a folder. Revisiting the Clean Data with Power Query podcast # 2037
Power Query can now combine all Excel files in a folder.
Improved: They automatically delete the headers from all but the first file.
You choose which file to use as the Sample file.
Choose which Worksheet, Table, Named Range to import
Use the query editor to do any transforms
Close & Load to combine all files
Later, refresh the query to have it update
Power Query can now combine all Excel files in a folder.
Improved: They automatically delete the headers from all but the first file.
You choose which file to use as the Sample file.
Choose which Worksheet, Table, Named Range to import
Use the query editor to do any transforms
Close & Load to combine all files
Later, refresh the query to have it update
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2077: Merge Workbooks Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Just 40 episodes ago, talking about cleaning data with Power Query, how to take a whole bunch of CSV files and bring them into a single Excel file.
Well this feature has just gotten dramatically better.
So I have here today is a folder and in this folder are a whole series of monthly Excel files, 22 of them for almost two years.
I need to combine all these files.
Now, these all have the same column layout, alright?
So they all have 9 columns, all have the same headers and so on, alright.
So we're going to combine all these without a macro.
If you're in Excel 2016, you're going to come to the Data tab to Get & Transform data.
If you're in Excel 2010 or 2013, you're going to go out and download a great product from Microsoft called Power Query.
You can download the whole thing and have it installed in less than a minute, alright.
So, we're going to come here to Get Data, From a File but not from a single workbook.
We want to get it From a Folder like this.
Alright, here they are.
They’re asking you to Browse to the folder but I'm just going to click in there and paste Ctrl+V because I pasted the directory, click OK.
So they showed me a preview of the data I have.
I open this little Combine drop-down here and choose Combine & Edit.
Alright, and then the step here is that we're going to choose one file that's a good representative file, alright, and they're just choosing the first one that happens to be alphabetically.
It doesn't matter which one you choose.
And then this Sample Query that we're building from the first file that we choose, in this case, I want Sheet1.
If you have multiple sheets you could say Sheet2 or the sheet called Income Statement or the Name Range or a table.
And they showed me what it looks like and I click OK.
Now, there is a lot going on in this screen.
What they've actually done here is they've created a Sample Query, ahh Sample Query, and then created that as a function and run that function against everything in the folder.
They add a new column that tells me where the file came from and in this particular case I already know where it came from because we have a date filled out here.
So I don't need that, I'm going to right click and Remove that.
And maybe there's some data in there that I don't need.
Maybe this data is going to go to a customer so I don't want to show the Profit so I can remove that column and even remove the Cost column, and maybe there's some annoying things like, you know, they used bad headers and I want to rename these headers.
So right click and Rename, we'll call it QTY for Quantity.
Maybe things are in the wrong sequence, maybe I want to take this item and move it to another spot, alright?
Just all these little annoying things that you might possibly have and, of course over here, they're doing all the applied steps and then you can continue to do any Power Query things that you need to do.
But in this case, it's just simple.
I just want to get the data in.
So I'm going to Close & Load, a few seconds go by and there it is.
I have my final.
Let's just sort this here by date; you see there we go from January of 2018 all the way out through October of 2019.
How cool is that.
Now here's the amazing, amazing thing about this.
Let's say that something changes in our folder.
So back out here in the Month files, I get two new files, Paste, and now there's two more records here, two more Excel files that I have to combine.
Just come back to my Excel file which currently has 516 rows, find the MonthFiles and click the Refresh button.
And that quickly I now have the new records from November and December.
Power Query is an amazing new tool free for Excel 2010, free for Excel 2013.
It was beautiful when they could combine CSV files but now that they can combine Excel files as well, just phenomenal.
So, I've updated my book, Power Excel with MrExcel, the 2017 Edition, includes Power Query, Power BI and all the new tools.
Check it out.
Click that “i” in the top-right hand corner.
Alright, wrap-up of this episode: We're revisiting the Clean Data with the Power Query podcast #2037 where we combine multiple CSV files.
We can now use that same tool, an improved version of that tool, to combine all Excel files in a folder.
Another improvement, the change from 2037, they automatically delete the headers from all but the first file and promote those headers in the first file.
You choose which file to use as the Sample file.
In my case, I used January.
Work- Choose which Worksheet, Table, or Named Range to Import and then do any transforms in the query editor.
We click Close & Load, it will combine all the files.
And then, if you get more files dropped into the folder or some files taken away or some files changed, just refresh the query and it will go do all of those steps again.
Power Query, an absolutely amazing tool.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Just 40 episodes ago, talking about cleaning data with Power Query, how to take a whole bunch of CSV files and bring them into a single Excel file.
Well this feature has just gotten dramatically better.
So I have here today is a folder and in this folder are a whole series of monthly Excel files, 22 of them for almost two years.
I need to combine all these files.
Now, these all have the same column layout, alright?
So they all have 9 columns, all have the same headers and so on, alright.
So we're going to combine all these without a macro.
If you're in Excel 2016, you're going to come to the Data tab to Get & Transform data.
If you're in Excel 2010 or 2013, you're going to go out and download a great product from Microsoft called Power Query.
You can download the whole thing and have it installed in less than a minute, alright.
So, we're going to come here to Get Data, From a File but not from a single workbook.
We want to get it From a Folder like this.
Alright, here they are.
They’re asking you to Browse to the folder but I'm just going to click in there and paste Ctrl+V because I pasted the directory, click OK.
So they showed me a preview of the data I have.
I open this little Combine drop-down here and choose Combine & Edit.
Alright, and then the step here is that we're going to choose one file that's a good representative file, alright, and they're just choosing the first one that happens to be alphabetically.
It doesn't matter which one you choose.
And then this Sample Query that we're building from the first file that we choose, in this case, I want Sheet1.
If you have multiple sheets you could say Sheet2 or the sheet called Income Statement or the Name Range or a table.
And they showed me what it looks like and I click OK.
Now, there is a lot going on in this screen.
What they've actually done here is they've created a Sample Query, ahh Sample Query, and then created that as a function and run that function against everything in the folder.
They add a new column that tells me where the file came from and in this particular case I already know where it came from because we have a date filled out here.
So I don't need that, I'm going to right click and Remove that.
And maybe there's some data in there that I don't need.
Maybe this data is going to go to a customer so I don't want to show the Profit so I can remove that column and even remove the Cost column, and maybe there's some annoying things like, you know, they used bad headers and I want to rename these headers.
So right click and Rename, we'll call it QTY for Quantity.
Maybe things are in the wrong sequence, maybe I want to take this item and move it to another spot, alright?
Just all these little annoying things that you might possibly have and, of course over here, they're doing all the applied steps and then you can continue to do any Power Query things that you need to do.
But in this case, it's just simple.
I just want to get the data in.
So I'm going to Close & Load, a few seconds go by and there it is.
I have my final.
Let's just sort this here by date; you see there we go from January of 2018 all the way out through October of 2019.
How cool is that.
Now here's the amazing, amazing thing about this.
Let's say that something changes in our folder.
So back out here in the Month files, I get two new files, Paste, and now there's two more records here, two more Excel files that I have to combine.
Just come back to my Excel file which currently has 516 rows, find the MonthFiles and click the Refresh button.
And that quickly I now have the new records from November and December.
Power Query is an amazing new tool free for Excel 2010, free for Excel 2013.
It was beautiful when they could combine CSV files but now that they can combine Excel files as well, just phenomenal.
So, I've updated my book, Power Excel with MrExcel, the 2017 Edition, includes Power Query, Power BI and all the new tools.
Check it out.
Click that “i” in the top-right hand corner.
Alright, wrap-up of this episode: We're revisiting the Clean Data with the Power Query podcast #2037 where we combine multiple CSV files.
We can now use that same tool, an improved version of that tool, to combine all Excel files in a folder.
Another improvement, the change from 2037, they automatically delete the headers from all but the first file and promote those headers in the first file.
You choose which file to use as the Sample file.
In my case, I used January.
Work- Choose which Worksheet, Table, or Named Range to Import and then do any transforms in the query editor.
We click Close & Load, it will combine all the files.
And then, if you get more files dropped into the folder or some files taken away or some files changed, just refresh the query and it will go do all of those steps again.
Power Query, an absolutely amazing tool.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
