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.
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 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.
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!
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.
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!
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!
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 :).
very useful and helpful esp. the part about content column (addcolumn - custom column -Excel.workbook and selct the name of the columns) and filtering objects, right click table field to see data preview, , close and load to :)
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 ;-)
@@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 for the video - really helpful After having used Google Sheets for the past years, moving to Excel was a terrible experience. As if Microsoft have never heard about the word Usability or User Experience. The data integration across Google Sheets is MILLION times SIMPLER vs the convoluted Excel...
Are there stuff you can do in Google Sheets that one cannot do in Excel? Some of my customers send files to me via Google Sheets, but I have not tried doing anything with the file except to download to Excel. Out of curiosity, why did you move to Excel?
Thank you for the detailed information, I have learnt a lot from your video about automation and implemented plenty of them. from 4:28 - Could you also advise how to deal with different fields of data in each sheet of my group of workbooks or if I can once again split them into each sheets post cleaning and transforming to get compilation into a master file for automated report. It would be great if you can guide me through the process.
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 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?
Wonderful video! The content is crystal clear and *almost* covers what I'm looking for - say, if I have 10 tables with 5 sheets, where would this process differ if I want to combine the data not in one single table, but in five tables? (i.e. one for each sheet) Whenever I try to separate it that way, I end up with a clunky amount of queries.
Not sure what you mean, Guillaume. If you have 2 tables on each sheet and you want to create two final query tables (one for each set of tables), then you'd have to select the tables for the first query and repeat this process for the second set.
Thank you! I have a slightly different scenario. I need to populate a target table (my expenses tracker) with the Data coming from 4 excels that have different structures (i.e.the xlw downloads from bank account, american express and other two payment providers). Moreover, before putting the Data together, some transformations are needed for each of the 4 source excels. Do you have some suggestions?
Great tutorial, so many tutorials on each work book containing no more than one table so this was useful. I would have like more on he ' # date' function, not sure how it works?
I have been using Power BI for a lot of things but nice to see we can do a lot of what was in Power BI in excel as often faster for smaller tasks. Can you create a fiscal year marker as you can in Power BI if your Fiscal year and quarters are not aligned to the calendar year?
Yes, the same Power Query tools in Power BI are also in Excel. In this video I show you how to covert dates into their fiscal periods, including those that don't fall in line with the start of a month: ua-cam.com/video/bo-43zg1R8U/v-deo.html
Hi Helpful video! If i already made a query with different excel files and appended all together, and i want to update the query by adding a new excel file. How do i do this?
OMG! This is what I was looking for. Thank you so much. I have this video to my list. Just curious though: how come the date column is detected as text rather than a date? Is there away to auto detect it as date without adding a extra step.
Glad you found this video helpful! If your data has actual dates then Power Query is likely to detect them. My data didn't have dates. I created the date from the file names, which is why there were extra steps.
@@MyOnlineTrainingHub thank you that explains. It totally skipped my brain. I like your dashing dashboards and layman's way of explaining. You make it seem pretty easy to create dashboards. Thank you for keeping us motivated.
Great tutorial, Mynda. What you could have done is showing how to do it with 2 worksheets. Save the query and then adding the 3rd worksheet to the folder and refresh so you show it how to use it each month.
Ah.. this was going to be my question .. if you add a new file into the folder.. and refresh.. it picks it up. Is this right? Mynda thank you for this tutorial, you explain everything so well. 😊
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 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 :-)
Love your vids. So easy to follow and very well presented. Im far from advanced in excel but your tutoring has really helped a lot tgabks very much.
Awesome to hear! 🙏
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!
Finally I got the consolidation to work right! The vidoe is very helpful with clear instructions. Thanks Mynda!
Great to hear, Ravi!
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.
This is going to save me a lot of time. Thanks for explaining in a simple and effective way
Great to hear!
As always, found this page to post the most useful and effective videos. Thank you very much for this post.
Glad it was helpful!
Absolutely mind blowing. Thanks for the tutorial. Keep up the good work.
Glad you liked it!
I have seen tens of videos about this topic, and this is the best. Thanks.
Wow, thanks!
Hi Mynda,
Always your tutorial are easy, Simple and straight to the point!
Thank you!
Glad you think so, Felipe :-)
This tutorial is a lifesaver for my workflow monitoring integration file transfer logs
Happy to hear that you're saving time with this!
You are the best powerquery master I've ever seen.
Thanks for your kind words, Kang!
@@MyOnlineTrainingHub Thanks for your response.^^
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!
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 😊🙏
The steps for combining all difference files are very clear and easy to follow, thanks a lot!
Glad to hear that!
Your explanations are very clear and informs small actions that are required
Great to hear, Vikram!
Excellent solution and review...thank You so much for sharing.
Glad it was helpful!
The tutorial is clear, informative and super helpful.
Glad it was helpful!
Mynda, you are super star, thank you for sharing us free tutorials.
You are so welcome!
yes, this is exactly what I need to know for merging a number of files with worksheets. thanks
Great to hear!
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!
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: ua-cam.com/video/L4BuUzccLpo/v-deo.html
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.
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!
thank you soo much!! this will save me and my manager several hours of work
Great to hear!
Hi Mynda. Thanks for this fun Power Query transformation example. I always learn something new from you :)) Thumbs up!!
Glad it was helpful, Wayne!
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 :-)
thanks a lot !!! your videos summarize whole courses
Glad you like them!
Thank you, and thank you so much for taking the time to reply.
No worries!
Thank you for sharing your knowledge ! Incredibly helpful !
My pleasure!
This process order worked much easier for me, thanks.
Glad it helped!
Outstanding !! Just what I needed !! Thanks a lot, they are very helpful, easy to follow and to the point !
Glad it helped!
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!
Thank you for this. This came in super-handy this week!
Great to hear 😊
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.
Glad it was helpful 😊
Wonderfull video and exactly what I needed to summarise my data from a folder location. Thanks allot and cheers, from Amsterdam!
Glad it helped!
Thanks so much for sharing your knowledge on excel.
My pleasure 😊
Thank you ... I had a problem yesterday that completed the hard way ... but the next time I will be ready!
Great to hear!
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!
very useful and helpful esp. the part about content column (addcolumn - custom column -Excel.workbook and selct the name of the columns) and filtering objects, right click table field to see data preview, , close and load to :)
Glad it was helpful, Tina😊
A gem of a channel!! ... Thank You!
Glad you enjoy it!
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!
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 for the video - really helpful
After having used Google Sheets for the past years, moving to Excel was a terrible experience.
As if Microsoft have never heard about the word Usability or User Experience.
The data integration across Google Sheets is MILLION times SIMPLER vs the convoluted Excel...
Glad I could help.
Are there stuff you can do in Google Sheets that one cannot do in Excel?
Some of my customers send files to me via Google Sheets, but I have not tried doing anything with the file except to download to Excel.
Out of curiosity, why did you move to Excel?
thank you so much, this video got me out of a really sticky situation :)
So pleased I could help 😊
Awesome! You solved my problem in 8 minutes 😍
Wow, that's great to hear!
very useful. combining 10 workbook with multiple sheets within 5 minutes. thanks
Glad it was helpful!
Thanks a lot for this vid. You just saved me from making a mess 😀
Glad I could help!
thanks a lot for the great job. we appreciate a lot your courses and i am one of your fidel followers
I appreciate that!
Excellent demo. Thank you
Glad you liked it!
Thank you for the detailed information, I have learnt a lot from your video about automation and implemented plenty of them.
from 4:28 - Could you also advise how to deal with different fields of data in each sheet of my group of workbooks or if I can once again split them into each sheets post cleaning and transforming to get compilation into a master file for automated report. It would be great if you can guide me through the process.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Ohh where you was before- I LOVE YOU. TANKS. I run to try!!!
Glad you like it :-)
Excellent solution! Thank you!
Glad it helped!
Just awesome.
You saved my life once again!
Glad I could help!
Great tutorial Mynda, very well explained especialy with the use of =#date([Funtion]). Thanks.
Glad it was helpful, Denin!
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 😊
What an amazing video .Really a game changer. Thank you Mynda.💯
So pleased it was helpful!
Thank you, you explained this very well. I will review again as a guide to combine multiple excels.
Glad it was helpful!
Very well presented. Thank you
Glad you liked it 🙏
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!
Lovely person.... Great teacher.
Thank you, Mahmood 😃
Thank you Mynda. very useful. Wonderful.
Glad it was helpful!
It really solved my problem. Thanks a lot!
Great to hear!
Thanks - big help. Especially the #Date formula. I've been looking for that functionality for a while!
Great to hear, Nathan!
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 :-)
Well explained tutorial. Found it very useful. Cannot thank you enough!
Glad it helped!
Thanks, this is pure gold!
Glad it was helpful 😊
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 Joey, Power Query can't access password protected Excel files.
Amazing explanation 👏
Glad you think so!
Great video, this helped me a lot
Glad to hear it!
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.
Wonderful video! The content is crystal clear and *almost* covers what I'm looking for - say, if I have 10 tables with 5 sheets, where would this process differ if I want to combine the data not in one single table, but in five tables? (i.e. one for each sheet) Whenever I try to separate it that way, I end up with a clunky amount of queries.
Not sure what you mean, Guillaume. If you have 2 tables on each sheet and you want to create two final query tables (one for each set of tables), then you'd have to select the tables for the first query and repeat this process for the second set.
Thank you! I have a slightly different scenario. I need to populate a target table (my expenses tracker) with the Data coming from 4 excels that have different structures (i.e.the xlw downloads from bank account, american express and other two payment providers). Moreover, before putting the Data together, some transformations are needed for each of the 4 source excels.
Do you have some suggestions?
Power Query can absolutely do this, but each Excel file will need to be transformed first, then you can append them into one table for analysis.
It is brilliant! Thank you for solving the problems!
My pleasure!
I really like this, very great teacher you are...
Thank you, Gulzar! 😃
Thanks a lot. It fulfilled my purpose. :🙂👍
Glad to hear that 😊🙏
Thanks a lot, you saved my time
Glad to hear that 😊
Excellent video. Is there any way to add new excel workbook files into the table after the initial setup has been established? Thanks
Yes, just add more sheets, the query will include them on refresh.
Thanks for the content you are amazing!. Please keep making more Power Query and Dax Content
Thanks, will do!
Good job and thanks for sharing your great teacher
Thanks for watching, Saber!
Great tutorial, so many tutorials on each work book containing no more than one table so this was useful.
I would have like more on he ' # date' function, not sure how it works?
The #date function takes 3 arguments: #date(year, month, day) to create a date, as you saw me create in the tutorial.
I have been using Power BI for a lot of things but nice to see we can do a lot of what was in Power BI in excel as often faster for smaller tasks. Can you create a fiscal year marker as you can in Power BI if your Fiscal year and quarters are not aligned to the calendar year?
Yes, the same Power Query tools in Power BI are also in Excel. In this video I show you how to covert dates into their fiscal periods, including those that don't fall in line with the start of a month: ua-cam.com/video/bo-43zg1R8U/v-deo.html
This thing which i needed at this time. Thank you
Glad I could help 😊
Thanks Myndy. That was great fun. Loved it!!!!
Glad you enjoyed it, John! Enjoy Power Query :-)
Hi Helpful video! If i already made a query with different excel files and appended all together, and i want to update the query by adding a new excel file. How do i do this?
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)?
You're welcome. It is dynamic as it stands. i.e. if you add another file to the folder, the query will pick it up.
OMG! This is what I was looking for. Thank you so much. I have this video to my list.
Just curious though: how come the date column is detected as text rather than a date? Is there away to auto detect it as date without adding a extra step.
Glad you found this video helpful! If your data has actual dates then Power Query is likely to detect them. My data didn't have dates. I created the date from the file names, which is why there were extra steps.
Many thanks, Mynda.
My pleasure, Ian :-)
Thank you so much, that was so helpful
You're very welcome!
Can PQ be used to extract the changes between files that get updated monthly? I like the way you explain everything. Thanks
Thanks, Colin. Yes, you can compare lists/tables using Power Query. www.myonlinetraininghub.com/excel-compare-two-lists
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 :-)
You are a life saver.. Thank you so much!!!
You're welcome!
I got lost in the expand table. Why was beverages displaying twice in column values? Thank you for detailed explanation of videos.
Because there are 3 Excel files I'm combining into one table, so each category is repeated multiple times.
@@MyOnlineTrainingHub thank you that explains. It totally skipped my brain. I like your dashing dashboards and layman's way of explaining. You make it seem pretty easy to create dashboards. Thank you for keeping us motivated.
Okay, that was way cool!!! You are AMAZING!!!
Pleased you like it, Mark!
Thanks a lot, really helpful. PQ is an amazing tool.
Glad it helped! Have fun with Power Query :-)
Great tutorial, Mynda. What you could have done is showing how to do it with 2 worksheets. Save the query and then adding the 3rd worksheet to the folder and refresh so you show it how to use it each month.
Great suggestion, Bert! Next time ;-)
Ah.. this was going to be my question .. if you add a new file into the folder.. and refresh.. it picks it up. Is this right?
Mynda thank you for this tutorial, you explain everything so well. 😊
Super helpful, thank you☺
My pleasure!
Enjoyed the tutorial, but would have liked an explanations of the '# date' DAX function.
The #date function takes 3 arguments: #date(year, month, day) to create a date, as you saw me create in the tutorial. Hope that clarifies things.