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.
-
14:49
Colion Noir
5 hours agoTim Walz Says I’ve Become Friends With School Shooters During Vice President Debate
26K30 -
1:22:45
Kim Iversen
8 hours agoIsraeli Hasbara Or Iranian Propaganda, Who Is Telling The Truth?
88.7K161 -
1:55:23
Glenn Greenwald
9 hours agoTim Walz Again Defends State Sponsored Censorship; Julian Assange Recognized As Political Prisoner; Post-VP Debate Interviews | SYSTEM UPDATE #345
93K214 -
40:50
Candace Show Podcast
9 hours agoKamala’s Family Reaches Out! | Candace Ep 77
199K830 -
57:49
Man in America
12 hours agoWEAPONIZED Disasters & LAND GRABS: the Blueprint Exposed by the MAUI FIRES
42.1K26 -
1:14:51
Flyover Conservatives
1 day agoDinesh D’Souza’s Shocking Film: Exposing the Real Threats to Trump and America; Will America be Saved or Destroyed? - Drew Allen | FOC Show
58.8K10 -
7:52
Price of Reason
14 hours agoJoker 2 Review - Folie A Doo Doo! (No Spoilers)
37K14 -
54:52
LFA TV
20 hours agoElon Musk: This Could Be America’s Last Election | Trumpet Daily 10.2.24 9PM EST
55.4K11 -
3:11:23
Mostly Sports With Mark Titus and Brandon Walker
11 hours agoMostly Sports College Football Dynasty League | Week 12 Session 1
75.3K1 -
59:13
The StoneZONE with Roger Stone
8 hours agoThe Triumph of Vance & Humiliation of Walz – W/ Catturd | The StoneZONE
66.6K44