Get Multiple Files Containing Multiple Sheets with Power Query

How to import multiple files containing multiple sheets with Power Query, even if the data isn’t formatted in an Excel Table.

🔔 SUBSCRIBE if you’d like more tips and tutorials like this.

💬 Please leave me a COMMENT. I read them all!

🎁 SHARE this video and spread the Excel love.

Or if you’re short of time, please click the 👍

[MyOnlineTrainingHub]

FAQs Answered:

1. Can you get workbooks with a different number of columns? Yes, however the columns will be appended in order from left to right. Therefore, if your columns are not all in the same order you'll end up with a mess!

2. What happens if workbooks have different column headers? The worksheets will be appended in column order from left to right irrespective of the column names. As long as the columns contain the same data and are in the same order, then it doesn't matter what they're called.

3. What happens if workbooks include a blank worksheet or worksheets you don't want to import? These worksheets will be included in the query however, you can add a filter in the query to exclude any sheets you don't want included in the final dataset.

[akramrahamath]

It came right at a time when I needed this tutorial the most, have a load of data to consolidate and it just got made simpler. Thanks Mynda:)

[mpactagj]

I watched a half dozen similar videos trying to figure this out, using VBA, etc. etc., but this was the most comprehensive and did not make non-real world assumptions like having pristine data or even well formulated data. Very helpful!

[felipe]

Hi Mynda,

Always your tutorial are easy, Simple and straight to the point!

Thank you!

[JuanSalvadorEstradaE]

Outstanding !! Just what I needed !! Thanks a lot, they are very helpful, easy to follow and to the point !

[BoubacarDiallo-sptw]

I wanna give you a shout out from Richard Toll, Senegal. Your tutorials are really taking my excel skills to the next level. Thank you!

[onnseanbeats]

Exactly what I was looking for! Very well done and easy to follow.

[wken]

Hi Mynda, I just finished writing an Excel VBA program that did virtually the same thing but this is a much faster solution! I use Power Query in Power BI and I have no doubt that your Power BI videos are as exceptional as your Excel ones - for me the best on the web - so looking forward to reviewing those. Many thanks!

[lianchingyap]

The steps for combining all difference files are very clear and easy to follow, thanks a lot!

[williamthatsmyname]

Absolutely mind blowing. Thanks for the tutorial. Keep up the good work.

[vityavertu]

amazing tutorial, just 8 mins and you got a solid knowledge of powerquery basics. Good job!

[tfvybvt]

The tutorial is clear, informative and super helpful.

[RaviShankar-huft]

Finally I got the consolidation to work right! The vidoe is very helpful with clear instructions. Thanks Mynda!

[marvinjoyceburgstededijk]

Wonderfull video and exactly what I needed to summarise my data from a folder location. Thanks allot and cheers, from Amsterdam!

[frederickzoreta]

Another SUPER EXCELLENT Tutorial! Thanks a LOT for your sharing your expertise! This helped me in 1 of my academic projects that I'm finishing right now :).

[ExcelWithChris]

Thanks a million. As a new user of PQ these basic but very important methods are of great use and value. Thanks.

[douglasbrito]

Hi Mynda, thanks very much for this. I was struggling to achieve this at Power BI, now I'm using your explanation. Cheers

[myesmuratova]

Mynda, you are super star, thank you for sharing us free tutorials.

[vikramgupta]

Your explanations are very clear and informs small actions that are required

[awakim]

As always, found this page to post the most useful and effective videos. Thank you very much for this post.