Premium Only Content

File From Folder w Excel Power Query Tutorial
Get step-by-step instructions: https://excel.tv/how-to-power-query-file-from-folder
Check out our online academy: https://excel.tv/excel-tv-academy-enroll-now/
GET FILE FROM FOLDER
Get File From Folder is what Microsoft has named the functionality that lets 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 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 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 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 grow to the right taking up more columns.
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. https://excel.tv/how-to-power-query-file-from-folder
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:
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.
-
LIVE
The Charlie Kirk Show
38 minutes agoWin, Win, Win Wisconsin: Election Day 2025 | Baris, Halperin, Bowyer, Galaszewski, Formica | 4.1
3,879 watching -
LIVE
The Dilley Show
48 minutes agoWisconsin and Florida Vote! Invasion Elon Drama! w/Author Brenden Dilley 04/01/2025
2,234 watching -
DVR
The Rubin Report
1 hour agoElon Musk Warns How Close Race Could Derail More Than Just MAGA Agenda
5.76K3 -
LIVE
Flyover Conservatives
11 hours agoThe Coup We Forgot: Mel K Unpacks the Treason Behind Crossfire Hurricane | FOC Show
392 watching -
LIVE
Bare Knuckle Fighting Championship
21 hours agoThe Bare Knuckle Show with Brian Soscia
82 watching -
LIVE
Grant Stinchfield
42 minutes agoRFK Jr. Ousts FDA’s Peter Marks—A Win for Medical Freedom and Government Accountability!
146 watching -
LIVE
The Dana Show with Dana Loesch
29 minutes agoThe Dana Show LIVE on Rumble! | 04-01-25
267 watching -
LIVE
Viss
2 hours ago🔴LIVE - PUBG Fueled with Tactics, Adrenaline, and Gasoline! - PUBG
270 watching -
LIVE
Benny Johnson
1 hour agoELECTION-MAGEDDON: Trump Vs. Obama 2028?! Third Term Matchup | MAGA on The Ballot
5,585 watching -
1:01:21
VINCE
4 hours agoScott Presler On The Ever-Important Wisconsin Election | Episode 12 - 04/01/25
192K136