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!
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?
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!
@@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.
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?
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?
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 :)
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 :).
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!
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.
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.
I watched the video "Get Multiple Files Containing Multiple Sheets with Power Query" you were clear and the video very instructive. Can you tell me how can I activate autocomplete in the formula bar of "Power Query". I have Microsoft office professionnel Plus 2019.
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.
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 ;-)
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?
Very simple way of teaching style 👍thank you. Have a quick question. Will power query get updated if there is a change in source folder .. like addl files or change on data?
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
Thanks for the tutorial. It is simple and easy to understand. Is there also a tutorial that show how to put all the extracted files in pivot table and dashboard as a daily sales tracker. Thanks again :)
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. 😊
Great video Ma'am. Can you please guide me if there is way to make columns of date in power query so that when I am receiving daily sales figures for each item, the new sales figure just get added in the corresponding date column?
Hi Gaurav, I don't recommend having a separate column for each date. This goes against the recommended tabular layout (www.myonlinetraininghub.com/excel-tabular-data-format) you should use to store data. From the tabular data you can use PivotTables to summarise the data into columns for the dates.
@@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.
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?
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!
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 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 :-)
This tutorial is a lifesaver for my workflow monitoring integration file transfer logs
Happy to hear that you're saving time with this!
This is going to save me a lot of time. Thanks for explaining in a simple and effective way
Great to hear!
Finally I got the consolidation to work right! The vidoe is very helpful with clear instructions. Thanks Mynda!
Great to hear, Ravi!
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 😊🙏
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!
Hi Mynda,
Always your tutorial are easy, Simple and straight to the point!
Thank you!
Glad you think so, Felipe :-)
I have seen tens of videos about this topic, and this is the best. Thanks.
Wow, thanks!
Absolutely mind blowing. Thanks for the tutorial. Keep up the good work.
Glad you liked it!
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
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 😊
As always, found this page to post the most useful and effective videos. Thank you very much for this post.
Glad it was helpful!
The steps for combining all difference files are very clear and easy to follow, thanks a lot!
Glad to hear that!
yes, this is exactly what I need to know for merging a number of files with worksheets. thanks
Great to hear!
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!
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@
Your explanations are very clear and informs small actions that are required
Great to hear, Vikram!
You are the best powerquery master I've ever seen.
Thanks for your kind words, Kang!
@@MyOnlineTrainingHub Thanks for your response.^^
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!
amazing tutorial, just 8 mins and you got a solid knowledge of powerquery basics. Good job!
Glad to hear that, Vitya!
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.
This process order worked much easier for me, thanks.
Glad it helped!
Thank you, and thank you so much for taking the time to reply.
No worries!
thanks a lot !!! your videos summarize whole courses
Glad you like them!
thank you soo much!! this will save me and my manager several hours of work
Great to hear!
Excellent solution and review...thank You so much for sharing.
Glad it was helpful!
Thank you ... I had a problem yesterday that completed the hard way ... but the next time I will be ready!
Great to hear!
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 :-)
Thank you for this. This came in super-handy this week!
Great to hear 😊
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?
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 sharing your knowledge ! Incredibly helpful !
My pleasure!
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.
Outstanding !! Just what I needed !! Thanks a lot, they are very helpful, easy to follow and to the point !
Glad it helped!
A gem of a channel!! ... Thank You!
Glad you enjoy it!
Lovely person.... Great teacher.
Thank you, Mahmood 😃
thank you so much, this video got me out of a really sticky situation :)
So pleased I could help 😊
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?
very useful. combining 10 workbook with multiple sheets within 5 minutes. thanks
Glad it was helpful!
Thanks so much for sharing your knowledge on excel.
My pleasure 😊
Awesome! You solved my problem in 8 minutes 😍
Wow, that's great to hear!
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.
Thanks a lot for this vid. You just saved me from making a mess 😀
Glad I could help!
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😊
Just awesome.
You saved my life once again!
Glad I could help!
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, this is pure gold!
Glad it was helpful 😊
Excellent solution! Thank you!
Glad it helped!
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!
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!
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 :-)
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.
Hi Mynda. Thanks for this fun Power Query transformation example. I always learn something new from you :)) Thumbs up!!
Glad it was helpful, Wayne!
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.
Excellent demo. Thank you
Glad you liked it!
It really solved my problem. Thanks a lot!
Great to hear!
Ohh where you was before- I LOVE YOU. TANKS. I run to try!!!
Glad you like it :-)
thanks a lot for the great job. we appreciate a lot your courses and i am one of your fidel followers
I appreciate that!
Thanks - big help. Especially the #Date formula. I've been looking for that functionality for a while!
Great to hear, Nathan!
It is brilliant! Thank you for solving the problems!
My pleasure!
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.
I watched the video "Get Multiple Files Containing Multiple Sheets with Power Query" you were clear and the video very instructive. Can you tell me how can I activate autocomplete in the formula bar of "Power Query". I have Microsoft office professionnel Plus 2019.
You either have auto complete or you don't. You can try updating your Excel installation, but there's no guarantee you'll get this new feature.
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
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!
Great tutorial Mynda, very well explained especialy with the use of =#date([Funtion]). Thanks.
Glad it was helpful, Denin!
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!
Thank you Mynda. very useful. Wonderful.
Glad it was helpful!
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.
Very simple way of teaching style 👍thank you. Have a quick question. Will power query get updated if there is a change in source folder .. like addl files or change on data?
Hi Praveen, to get new files added to the source folder, simply click the Refresh button for the query.
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
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
Thanks a lot, you saved my time
Glad to hear that 😊
Very well presented. Thank you
Glad you liked it 🙏
Well explained tutorial. Found it very useful. Cannot thank you enough!
Glad it helped!
Hello, find this very useful! :) btw what if a new file is added? How will this get updated to reflect the totality? Thank you in advance :)
Add your file to the folder and click the Refresh All button on the Data tab of the Ribbon.
This thing which i needed at this time. Thank you
Glad I could help 😊
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.
Okay, that was way cool!!! You are AMAZING!!!
Pleased you like it, Mark!
you're awesome. thank you very much
You're welcome, Mehdi!
Great video, this helped me a lot
Glad to hear it!
Brilliant video! Very well explained, thanks a ton! Can the same process be followed for combining files in Power BI through its Power query as well?
Yes, indeed 😊
Many thanks, Mynda.
My pleasure, Ian :-)
Thanks a lot, really helpful. PQ is an amazing tool.
Glad it helped! Have fun with Power Query :-)
Thanks for the tutorial. It is simple and easy to understand. Is there also a tutorial that show how to put all the extracted files in pivot table and dashboard as a daily sales tracker. Thanks again :)
Glad it's useful, Myat!
Thanks a lot. It fulfilled my purpose. :🙂👍
Glad to hear that 😊🙏
Great content, but can I ask what screen recorder you used
? It really clean.
Thanks, Niyi! I use Camtasia Studio for my videos.
I really like this, very great teacher you are...
Thank you, Gulzar! 😃
Amazing explanation 👏
Glad you think so!
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.
If you're stuck, you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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 :-)
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. 😊
Great video Ma'am. Can you please guide me if there is way to make columns of date in power query so that when I am receiving daily sales figures for each item, the new sales figure just get added in the corresponding date column?
Hi Gaurav, I don't recommend having a separate column for each date. This goes against the recommended tabular layout (www.myonlinetraininghub.com/excel-tabular-data-format) you should use to store data. From the tabular data you can use PivotTables to summarise the data into columns for the dates.
Great Video, you just solve my data gathering problem
Great to hear it was helpful, Ludger :-)
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.