Perfect, simple and just what I was looking for; so many of these youtube web cast on this subject always miss out on workbooks and only focus on a single sheet. Excellent job
Searched high and low for such a clear and concise explanation to achieving this task where the data is non-uniform. I had 30 xlsx/xls files representing some annual data each with 22 sheets so not a task to attempt manually! Only problem I had was to get power query to recognise that not all sheets all had the same columns ... some had new columns introduced over the years and some columns had been discontinued but for an accurate record I wanted to keep all data. The way I achieved it in the end was to make a master list of column headings, replace the headings for the 'lead' file with the master and add dummy data in the empty columns. :) So thanks, one more subscriber found. Now to import the data (235,481 rows) into Access and start to analyse.
Load would load the content of the window into an Excel table (or other default load destination). Combine would perform the merge. By using Transform we have the opportunity to exclude files, or extract sheets etc before merging/combining.
Hi Valerie, the list you see as I type the formula I don't believe is available in Excel 2016. It is hard to remember all version differences. I believe all steps in the video are possible although the buttons may be in different places.
Thank you. Sure, you can. This technique imported from a folder. But you can also do this by importing from an external workbook from the Data tab. you can select the sheets you want from a list using this method.
Hi Alan. Trying to download the files from the link above. Get a "Not found, error 404" error. Must be a broken address. Looking forward to following along whenever you fix the link. Thanks!!
@@Computergaga Thanks Alan. Got the files, ran through the exercise, worked perfectly. Thanks for the lesson and for the sample files. Really helps with the learning to follow along and do the steps vs. just watching :)) Thumbs up!!
Perfect, simple and just what I was looking for; so many of these youtube web cast on this subject always miss out on workbooks and only focus on a single sheet. Excellent job
Great to hear! Thank you 😊
Searched high and low for such a clear and concise explanation to achieving this task where the data is non-uniform. I had 30 xlsx/xls files representing some annual data each with 22 sheets so not a task to attempt manually! Only problem I had was to get power query to recognise that not all sheets all had the same columns ... some had new columns introduced over the years and some columns had been discontinued but for an accurate record I wanted to keep all data. The way I achieved it in the end was to make a master list of column headings, replace the headings for the 'lead' file with the master and add dummy data in the empty columns. :) So thanks, one more subscriber found. Now to import the data (235,481 rows) into Access and start to analyse.
Nice work! Thank you. Power Query can take some fiddling to get the exact recipe you need for your situation.
Overwhelming, astonishing, convincing. A powerful weapon to solve problems. Thank you for this video Alan!
My pleasure, Iván. Thank you.
I often have multiple csv files and need to bring them into 1 excel workbook but place each csv file on its own sheet. How may I do that? Thanks.
@3:00 Alan, what would happen if Combine or Load were chosen instead of Transform Data?
Load would load the content of the window into an Excel table (or other default load destination).
Combine would perform the merge.
By using Transform we have the opportunity to exclude files, or extract sheets etc before merging/combining.
@@Computergaga Thanks. Appreciate it.
Brilliantly explained Power Query; much needed video on data consolidation thru PQ 👍
Thank you, Satish.
Hi, Thanks a lot for this clear video. I have Excel 2016, I can’t see as you the list of the formula. How I can get them ?
Thanks
Hi Valerie, the list you see as I type the formula I don't believe is available in Excel 2016. It is hard to remember all version differences. I believe all steps in the video are possible although the buttons may be in different places.
Computergaga Thanks a lot for your answer
Why we need to do custom column? If we click on content the 2 arrows, it will not expand and show other columns of the sheet?
Nice vedio. Can i import data from several sheets of one workbook by this method?
Thank you. Sure, you can. This technique imported from a folder. But you can also do this by importing from an external workbook from the Data tab. you can select the sheets you want from a list using this method.
Very well presented, Alan
Many thanks, Pradeep
The Awesome Power Query Makes Life So Much Easier...Great Stuff Thank You Alan :)
It sure does. Thanks Darryl.
Hi Alan. Trying to download the files from the link above. Get a "Not found, error 404" error. Must be a broken address. Looking forward to following along whenever you fix the link. Thanks!!
Hi Wayne, all fixed. I typed a - instead of a . 😳
@@Computergaga Thanks Alan. Got the files, ran through the exercise, worked perfectly. Thanks for the lesson and for the sample files. Really helps with the learning to follow along and do the steps vs. just watching :)) Thumbs up!!
Absolutely. No worries. Apologies for the original error.
I am your fan now!
Great! I hope I can help you further.
Awesome. Thank you ComputerGaga. This is very useful
Great to hear. Thanks Joshua.
@@Computergaga I have learnt a lot of excel skills from your lessons. Thanks again
Very useful another string to add to the Excel bow. Thankyou🙏
My pleasure, Andrew 🏹
Great video
Thank you, Paula.
My GetSheets column just has error on every line.
Excellent. many thanks youve really helped me
Great to hear. My pleasure.
Very useful, i like Power Query. Thanks Alan!
Very helpful, good work! Thank you.
Thank you 😊
I am using macbook and I cannot find " from file" in excel
I don't use Macs so cannot confidently tell you if this can be done. Macs have been behind with Power Query updates.
Please send Excel link
I don't have these files around anymore.
Thank you :)
No problem 😊
Very Good!
Thank you, Steven.
Great one. Thanks.
Thank you, Chris. Hope you are well, buddy.
Computergaga Doung great. Just a mission to get the Channel up and running. But I will get there.
You certainly will. I hope to see you soon at another Excel event somewhere.
... this doesnt keep the tabs separate!!!! HOOOOWWWWW :(
Sir pls help me sir maliyasia toto, 6d 6"numbers gusseing tommrow draw please reply to me
I got an Error :(
I DONT HAVE THAT OPTION TO DOWNLOAD FiLE AND THEN IT ASKS FOR DELIMITERS AND STUFFF AAHHHHH
Kindly use fonetic englush