Get Multiple Files Containing Multiple Sheets with Power Query

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.

Thank you Mynda. very useful. Wonderful.

This process order worked much easier for me, thanks.

Hi, is there any way to combine for example only the same sheets of different workbooks rather than combining all sheets of different workbooks in one place?

What if I wanted to combine multiple workbooks but wanted to keep the separate worksheets? i.e., combine 2018-2020 workbooks but keep the "Seafood", "Dairy", "Beverages", etc. worksheets still separate?

Awesome! You solved my problem in 8 minutes 😍

I have to combine data from multiple worksheets to a single worksheet. But my data have 1605 columns. When I bringing the data it only imports only 256 columns. What is the solution to bring all 1605 columns.

Amazing explanation 👏

Excellent demo. Thank you

Very well presented. Thank you

I am kinda going crazy here trying to make a excel calculator an iframe. I have a xlsm file that is in 9MB range, with vba code that I don't want to break, where I can't figure out how to add it as an iframe without going over the 10MB limit that would break the functionality of it. It seems that power query won't work if I need to pull data from certain cells without other coding languages. Is there any options here?

Thank you!

When it comes to Excel and Power Query, it seems "The devil is in the details." The custom column was the very thing i was missing, so thanks for that!!

I loaded my power query data as a table. Then I created a pivot table. However, my pivot table won't refresh even though my power query table updates. Do you know how could I make my pivot table refreshed at the same time as the power query data refreshed? Thank you.

It's great video! regard date column, is it a good practice to convert to 01/01/2020 or leave it as 01_2020? if we leave it as 01_2020, does it mean it won't be easy for further data cleansing? just thinking about using the new table for power BI, if we leave it as 01_2020, it won't be easy to write DAX in power BI?how about if I name 01_2020 as P01 (period 1)? Thanks

Thank you very well, but if we need to be a dynamic for the upcoming data (custom function or blank qurey & use excel.workbook without add column)?

This is genius level. So many excel users don't know how to create useful data but create data that is a nightmare to work with.

Thanks, this is pure gold!

Thanks a lot for this vid. You just saved me from making a mess 😀

Is it possible to transform the tabs before combining the tabs in different files together?

Just awesome.
You saved my life once again!

Hi, want to connect two product feeds in csv format to update a stock according to sku, automatically every 6 hours, and how do I make the resulting file can be exported live for the web site to use that data for stock update, thanks, have a great day.

Thanks a lot, you saved my time

Pretty good. Can you please do a tutorial when the data in the shits is even more messy; like when you have different columns and column names.

what if I have CSV files?

You are Genius

Pure genius🙏🙏🙏

THANKS AS ALWAYS

Well,i will fin out tomorrow at work if this is the solution I am looking for...I am VERY certain that it is!

Super helpful, thank you☺

Restricted excel files
Thanks for this solution...I have used it many times..works great!!
Is there anyway to use this with restricted excel files (No password) the source file is just restricted to a few users.
Thanks
Charles

Do you have any tips for loading data from DBF files? I have several DBF files in subfolders I was hoping I could just treat as text but it didn't work that way :(

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

thank you so much, this video got me out of a really sticky situation :)

The tutorial is clear, informative and super helpful.

thank you. you helped a lot

Super helpful, Thank you!

Hi Mynda. Your method is different from the way I did when I merge all files in folder (I use sample data merging). But your method seems to be optimised . I bet it is much faster to run the query compared to using sample files. I will try it. Thanks a lot!

my excel files are imported from SAP with file extention .xls when i import the data in excel it gives error and does not load the file. please advise.

Really game-changing function. But is it possible to separate dates for each year in a separate column. So this means still consolidating all data and sheets together. But have all 2019-related stuff on left, followed by 2020 and 2021 on the right horizontally?

It really solved my problem. Thanks a lot!

Whenever I refresh my excel sheet the data jumps around and switch location in the sheet. Do you know what I can do to prevent this? :)

I have seen tens of videos about this topic, and this is the best. Thanks.

Absolutely Amazing tutorial, thanks❤

Hello,
I have 2 excel files Jan-22 & Feb-22, each file contain 5 sheets sales, product , region, date & category, when I get 1 file and transform it all 5 sheets showing 5 queries separately, how can I combine feb-22 file in it and make it dynamically. Thanx

Need to understand, How can we Create multiple sheets from different multiples files

cool

Great straight forward tutorial! I guess I have an example question for you given that it's a very specific question!

Say you have a team who keep all of their sales data for the year in an excel file on SharePoint. That excel file is password protected.
1. Would the data from the password protected file be accessible to a user using your Power Query method?
2. Would the information be updated in real time when the Excel files are updated on the SharePoint?

Hi Mynda, could you please explain why my query picking up all black rows as well as the rows containing data? because of that, I got an error message saying my data is too big to load:(