Get file from Folder
Get File From Folder is what Microsoft has named the functionality that let’s you take multiple files and bring them together as one using Power Query.
Back in the day, the process to do something like this was very much VBA driven.
You would use something the File System Object to read every spreadsheet in a folder. You’d then use some code to reformat that data you pulled in and spit it out to a specific location in a File. This method usually worked, but it wasn’t without its issues.
For one, there could be weird characters that you might not expect. A lot of development time was spent on trying to clean the data. Because if the macro crashed, that was usually the end of the whole thing. Going through an entire set of folders could take an upwards of 15-30 minutes.
Power Query changed all of that. You no longer need macro code. And the resulting query created is really pretty cool.
In this video…
We go through an example that was inspired by multiple clients.
Here’s the background. A person who works out in the field has to go to different field sites to run an audit. They create a report that comes back in a standard format. However, the standard format itself isn’t really conducive to being analyzed. For instance, where they could have used a table that continues to grow down as more data is added, they instead used a table that would continuously grows to the right taking up more columns. This choice was the right one when their main uses for these reports was to print them.
This is where Power Query really shines. It allows us to select a file in a folder of all the same reports – that file selected becomes an exemplar file. Then whatever type of transformation we do it that file, Power Query will perform the same transformations to all the other files in the same folder.
Once Excel is finished all of those transformations, Power Query then does what’s called an append query. An append query will effectively stack tables. So if you have two tables with the same column fields of data in the same order, you would use the append query to perhaps tack on the rows of table 2 to table 1. Power Query does this for all the transformed tables in the same folder and creates one large table (named, by default, after the folder name).
The best part is how quickly it does this. Again, this used to be something I had to program pain painstakingly in VBA.
Watch the video to really get a sense of how to do this in your own. Then you can check out the instructions below I snagged from our academy.
(Yes, I’m admitting to being lazy and reusing the instructions – but they do a good job!)
Instructions to follow along
I have written sample instructions to follow along. These instructions are actually part of our Excel TV Online Academy.
In the academy, I might be using different filenames. But the instructions are the same.
Click here to download instructions to follow along
Learn about Online Academy NOW
Excel.TV Power Query Articles
We have lots of articles on Power Query. Check them out!
- POWER QUERY – SPLITTING NAMES FROM SUFFIXES – https://excel.tv/power-query-cleansing-data-the-easy-way/
- EXCEL DATA VISUALIZATION: PRESIDENTIAL APPROVAL RATINGS WITH SLICERS & POWER QUERY – CHART TRICKS – https://excel.tv/excel-data-visualization-presidential-approval-ratings-with-slicers-power-query-chart-tricks/
- QUERY ACTIVE WORKSHEET – EXCEL POWER QUERY TIPS – https://excel.tv/using-power-query-to-connect-tables-for-reporting-excel-tv/
- USING POWER QUERY TO CONNECT TABLES FOR REPORTING – https://excel.tv/query-active-worksheet-excel-power-query-tips/
- UNPIVOT WITH POWER QUERY – https://excel.tv/unpivot-power-query/
Could you see yourself combining files together into one table?
Let us know in the comments.
And, as always, keep on Excel’n.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
