Get Multiple Files Containing Multiple Sheets with Power Query
HTML-код
- Опубликовано: 19 июн 2024
- How to import multiple files containing multiple sheets with Power Query, even if the data isn’t formatted in an Excel Table.
🔻 DOWNLOAD the Excel file & see step by step written instructions here: www.myonlinetraininghub.com/i...
🎓 LEARN MORE: view my comprehensive courses: www.myonlinetraininghub.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
💬 Please leave me a COMMENT. I read them all!
🧟♀️ CONNECT with Mynda on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e... 
Наука
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.
Will it automatically update if you add more data files to the folder?
@@mikeheim44
Hit refresh on the final table and it SHOULD update.
@@garylillich Thank you
How can I separate each text files ( or .asc) to separate columns? In my case they all have the same X axis values but different Y values . HELP please . I have 120 files, cannot do them one by one!
I have 12 workbook ( Jan to Dec) and each workbook have more than 1 sheet, I want to combine all "sheet-1" only into file, how? - its # 3, how to filter?
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!
Great to hear my video was helpful, Mark!
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:)
Glad it helped Akram!
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!
Thanks for your kind words, Ken! Great to hear you enjoy my videos :-)
Hi Mynda,
Always your tutorial are easy, Simple and straight to the point!
Thank you!
Glad you think so, Felipe :-)
Outstanding !! Just what I needed !! Thanks a lot, they are very helpful, easy to follow and to the point !
Glad it helped!
The steps for combining all difference files are very clear and easy to follow, thanks a lot!
Glad to hear that!
Absolutely mind blowing. Thanks for the tutorial. Keep up the good work.
Glad you liked it!
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!
Awesome to hear :-)
Finally I got the consolidation to work right! The vidoe is very helpful with clear instructions. Thanks Mynda!
Great to hear, Ravi!
Hi Mynda, thanks very much for this. I was struggling to achieve this at Power BI, now I'm using your explanation. Cheers
Glad I could help, Douglas@
amazing tutorial, just 8 mins and you got a solid knowledge of powerquery basics. Good job!
Glad to hear that, Vitya!
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 :).
Wonderful to hear, Frederick!
Thank you so much Mynda for sharing your talents. You helped me to do exactly what i was trying to for a while.
Awesome to hear 😊🙏
Wonderfull video and exactly what I needed to summarise my data from a folder location. Thanks allot and cheers, from Amsterdam!
Glad it helped!
The tutorial is clear, informative and super helpful.
Glad it was helpful!
Your explanations are very clear and informs small actions that are required
Great to hear, Vikram!
Thanks a million. As a new user of PQ these basic but very important methods are of great use and value. Thanks.
Glad you found it useful, Chris!
I was struggling for a while with a file a colleague sent that had data across multiple sheets. I used power query to transform and combine that data into a single, unified table, but couldn’t figure out how to attach the name of the sheets along with the data. Searched online, and couldn’t immediately find the answer. I thought I was okay at power query, but I was still at a loss. Thank you thank you for posting this! Saved me a TON of manual work!
Hi Eric, You can use Get Data > From File > From Folder to connect to the file. This will give you a list of the sheets in each file. Note: you cannot use this on the file containing the query i.e. it cannot reference itself. It can only reference a file that is closed.
A gem of a channel!! ... Thank You!
Glad you enjoy it!
thanks a lot !!! your videos summarize whole courses
Glad you like them!
Great tutorial Mynda, very well explained especialy with the use of =#date([Funtion]). Thanks.
Glad it was helpful, Denin!
As always, found this page to post the most useful and effective videos. Thank you very much for this post.
Glad it was helpful!
Mynda, you are super star, thank you for sharing us free tutorials.
You are so welcome!
Thank you for this. This came in super-handy this week!
Great to hear 😊
What an amazing video .Really a game changer. Thank you Mynda.💯
So pleased it was helpful!
Thanks a lot, really helpful. PQ is an amazing tool.
Glad it helped! Have fun with Power Query :-)
Thanks - big help. Especially the #Date formula. I've been looking for that functionality for a while!
Great to hear, Nathan!
Great. Thanks. So clear and concise!
Glad you enjoyed it, Erica!
Thanks Myndy. That was great fun. Loved it!!!!
Glad you enjoyed it, John! Enjoy Power Query :-)
Thank you, you explained this very well. I will review again as a guide to combine multiple excels.
Glad it was helpful!
It is brilliant! Thank you for solving the problems!
My pleasure!
Thanks for this video very help as I have often combine multiply data sets - appreciated
Great to know you'll be able to use it, Clive :-)
I have seen tens of videos about this topic, and this is the best. Thanks.
Wow, thanks!
Thanks, this is pure gold!
Glad it was helpful 😊
Well explained tutorial. Found it very useful. Cannot thank you enough!
Glad it helped!
Exactly what I was looking for! Very well done and easy to follow.
Glad you liked it!
@@MyOnlineTrainingHub Ony one question. Can this work if the column headers are not the same and not in the same order? Since this is not an append, Im getting a mismatch of columns with this method, since one of my files has one sheet with one less column.
Thanks so much for sharing your knowledge on excel.
My pleasure 😊
Thank you, it's helpful. I've not used Power Query before, but I think when the need to do comes, I'll be ready.
Great to hear! You might also like this introduction to Power Query video: ruclips.net/video/L4BuUzccLpo/видео.html
You are a life saver.. Thank you so much!!!
You're welcome!
Super helpful as always ! Thank you !
Great to hear! Thank you :-)
Just awesome.
You saved my life once again!
Glad I could help!
It’s really helpful! thanks so much.
You're welcome!
Thank you ma'am,,this will be my first lesson on my journey especially in an EXCEL..😍😍
Wow! You're off to a great start learning Power Query :-)
Lovely person.... Great teacher.
Thank you, Mahmood 😃
Your simple explanation of how to design a Custom Column to load unstructured data from files in a folder led me to this solution.
I needed to aggregate multiple files in a folder. I have used Get Data "From Folder" for other file types such as XML before but it does not handle HTML data.
Using your suggestion I was able to select the "Name" and "Folder Path" columns from the source data and then create a Custom Column that referenced each file in the folder as an html document.
LoadAsHTML=Web.Page(Web.Contents("file:///"&[Folder Path]&[Name]))
This allowed me to consolidate data from the multiple files in HTML format.
Great to hear, Ian! Thanks for sharing. It's a clever workaround.
Excellent demo. Thank you
Glad you liked it!
Brilliant! thank you very much.
My pleasure 😊
Brilliant! Thank you for this.
You're very welcome!
Very practical, thank you!
Glad it was helpful!
You make this look so s-i-m-p-l-e! Amazing, just amazing! I know for sure this is going to help me a lot, in future tasks. Thank you very much for sharing it with all of us ;-)
You are so welcome, Jose!
Thank you so much, that was so helpful
You're very welcome!
Thank you, and thank you so much for taking the time to reply.
No worries!
Thanks for the content you are amazing!. Please keep making more Power Query and Dax Content
Thanks, will do!
Thank you Mynda. very useful. Wonderful.
Glad it was helpful!
Very well presented. Thank you
Glad you liked it 🙏
informative and short, thank you!
Glad you enjoyed it, Julia!
This is fantastic! Thank you!
Glad you liked it, Liwen!
I love all your videos, its so useful. thank you so much. it save hours of my time. really appreciated it.
You're very welcome!
This thing which i needed at this time. Thank you
Glad I could help 😊
Okay, that was way cool!!! You are AMAZING!!!
Pleased you like it, Mark!
This process order worked much easier for me, thanks.
Glad it helped!
Super helpful, Thank you!
You're welcome!
great lesson. Thank you!
Glad you liked it, Rade!
Great video. Thank you!
Glad you liked it!
Ohh where you was before- I LOVE YOU. TANKS. I run to try!!!
Glad you like it :-)
I was experimenting with power query without fully understanding what I was doing and had a similar file where the date was part of the file name. To extract the date I split the file name with the delimiter option. Afterwards, instead of using the date function(since I wasn't aware of it) I replaced 1 with Jan, 2 with Feb, 3 with Mar etc. and left the year in a separate column. That was enough to get what I needed, but the Date function seems like a better way to do it. Thank you for sharing this video!
My pleasure, Gene!
Thanks a lot, you saved my time
Glad to hear that 😊
It really solved my problem. Thanks a lot!
Great to hear!
yes, this is exactly what I need to know for merging a number of files with worksheets. thanks
Great to hear!
Thank you ... I had a problem yesterday that completed the hard way ... but the next time I will be ready!
Great to hear!
thanks a lot for the great job. we appreciate a lot your courses and i am one of your fidel followers
I appreciate that!
Super helpful, thank you☺
My pleasure!
Hi Mynda. Thanks for this fun Power Query transformation example. I always learn something new from you :)) Thumbs up!!
Glad it was helpful, Wayne!
thank you soo much!! this will save me and my manager several hours of work
Great to hear!
you're awesome. thank you very much
You're welcome, Mehdi!
I really like this, very great teacher you are...
Thank you, Gulzar! 😃
thank you. you helped a lot
Glad to hear that!
Amazing, Thanks a lot from India.
Glad you liked it, Akilesh!
Good job and thanks for sharing your great teacher
Thanks for watching, Saber!
Wow that is Amazing. Thank you for that.
My pleasure :-)
Great Tutorial...Thanks..
My pleasure 😊
Many thanks, Mynda.
My pleasure, Ian :-)
thank you so much, this video got me out of a really sticky situation :)
So pleased I could help 😊
Thank you so much for your content :)
My pleasure 😊
Perfect, Thanks a lot 💖
My pleasure 😊
Thank you ! It is a nice video.
Glad you liked it, Gisele!
Awesome! You solved my problem in 8 minutes 😍
Wow, that's great to hear!
Thanks!! Awesome video!
Glad you liked it, Julius!
Superb ! Thank you
Thanks, Harsha!
Love it, thanks!
So glad!
Thank you! Very well explained. Could you please make a video on different syntaxes that are used in power query? Will appreciate that.
Glad you found this video helpful. Thanks for your topic suggestion.
This is great. Thank you
Glad you liked it, Irene!
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!
Great to hear 😊
THANKS AS ALWAYS
My pleasure 😊
Great video thank you!
Glad you liked it!
Awesome. Thank you Mynda
Thanks for watching, Maki!
Thank you!
My pleasure!
you are really great ,thank you
So nice of you 🙏😊
Amazing explanation 👏
Glad you think so!