Combine all the files in a folder using Power Query | Excel & CSV | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post & download the example file★

★ About this video ★
In this post, we use Power Query to import all the files in a folder. We give Power Query a folder path, click a few buttons, and it imports and combines all the files into a single table. It’s like magic!

0:00 Introduction
0:53 Setting up the example
2:03 CSV Method 1
9:20 CSV Method 2
13:07 CSV Adding more files
13:57 File Uniformity
15:36 Excel Workbooks
16:23 Excel Workbook Method 1
20:50 Excel Workbook Method 2
23:39 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel #PowerQuery

[flaviogarlatticosta]

Thank you Mark for your amazing video tutorials, I can understand them even if I'm not English speaking (I'm Italian).👍

[guilleavellaneda]

Marc Thanks for explanation, everything is clear

[wmfield]

This is one of the most valuable Excel videos I've seen on GTQ. So thank you, Mark. One snag I ran into as a USA user was the date format, which I think other users may run into. Using the Change type > Using Locale… > Date > English USA will not work if the UK date begins on a day (the first number in the format) above 12. (I'm using Excel 2016 so I know nothing about what newer versions may do in this case.)

My solution was simply to split the columns by delimiter, place the 3 new columns in the order I need them, then merge them as a new date column. Now everything is copasetic.

[mohammedelsakally]

Thank you very much, Mark. Much appreciated.

[IvanCortinas_ES]

Excellent material. Thanks for sharing it!

[rajeevgosavi]

Very powerful tutorial with easy to understand " basic concept". Thanks for such a superb material and content. Sir may I request you to upload a tutorial explaining multiple headers in PQE. Thanks.

[jasontan]

Thanks Mark for the awesome video 👍 I need help to retrieve Excel files from OneDrive Personal Folder. Thanks

[eitancohen]

Thank you Marc.
Is there any performance difference between those 2 methods?

[tompetermann]

Awesome tutorial Mark!
I have been using method 2 with xlsx files since then.

However I face a performance issue:
I have raw data (about 50-100MB) with hundred thousands of rows.
Every transformation take ages. It takes sometimes hours.
Every time it goes through all rows.

I wonder if it is possible to load the file into the RAM first before I start the transformation. So the loading would go much faster.

Can you advise how to handle huge data?

[arturakhmerov]

Great video! But there's no such function on the Mac version of office. Soyou have any suggestions for Mac users?

[Dan_De_Man]

Would this also work if combining excel workbooks if there were subfolders in the import folder

[rajeevgosavi]

Sir, You did not explain how to solve the inconsistancy in column names.

[MaiHa-oomz]

I have one issue when I do combine excel file in a folder that there are two sheets (sheet 1 and sheet 2) in a file. But I only need to combine sheet 1. However, when I choose from a folder, it automatically load 2 sheets. How can I remove sheet 2 while transform data? Could you please help? Thank you.

[m.bouguerra]

sorry, but you have not solved the problem of the inconsistency of the headers