13 - Combine All the Excel Files in a folder Using Power Query
ฝัง
- เผยแพร่เมื่อ 11 ก.ค. 2024
- In this video, I will show you how to combine all the Excel files in a folder using Power Query.
One great benefit of doing this is that in future in any of the workbook data gets updted or if you add more files to the folder, you can quickly get the combines data with a single click
✅ Power Query Course Download files: bit.ly/power-query-download
☕ If you find my Excel videos useful and would like to support me, you can buy me a coffee - www.buymeacoffee.com/SumitB
Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
Best Excel Books: trumpexcel.com/best-excel-books/
Subscribe to get awesome Excel Tips every week: th-cam.com/users/trumpexc...
#Excel #PowerQuery #ExcelTips
I spent a whole day running circles . I try to import a bunch of datasets from folder for my case study. The error message would not go away till I saw your amazing video! I am grateful for for help. Thanks for the high quality video.
Same was for me as well😮
This is a great example how to load multiple excel files in an automated way. Thanks again for your master class!
This saved me so much time! I had to merge 81 excel worksheets and thank god I found your video. There are a total of 90 rows haha. THANK YOU!
i like the way you present , clear and concise, most importantly easy to follow.. there are a lot of other youtube videos uploaded where the presenter is very confusing and sounds like a total show off.. Truly, people with brilliant minds are those who stay down to earth and just shines through without any effort
Thank you so much for the kind words Penny... Glad you're finding the videos useful :)
you literally just saved me a week's worth of work. GOD BLESS YOU!
Hero bro , so simple and useful , love the way you explain without complications ❤️
Very well explained. Thanks for covering the in and around issues as well.. :)
You sir are a carrier saver. That error kept bugging me for the past week till I watched your video. Thank you! I'm going to check your website immediately.
Absolutely thankful to you Sumit. The whole series of videos for Power Query was like some treasure discovery - so very helpful. Wonderful and detailed explanation
Thanks for the kind words. Glad you liked it
Hi Sumit, thank you so much for sharing your wonderful content, greatly appreciated. I am also like the few other viewers, spent a lot of time trying to figure out some stuff. However, you have covered it all with clear explanations which were easy to follow. In most cases on other videos, error-proofing is never mentioned and covered, leaving me/us behind from progressing. A huge thank you for this, please keep it up!
But for this to properly work, you need to have the same amount of columns and the same names in each file, right? How to circle around this issue if you have one extra column in one file that is not in the other files, and properly include it in the combined file?
You are my saviour Papa. Keep it up.
Many thanks for your video which has just saved me from Expression.Error misery. I followed your steps and success; I was able to merge all the various worksheets into one. Many thanks
I found what I need thanks to you. There is a lot of info in youtube and google. It is a good tutorial.
This video is that one who help a lot working with multiple files in power query, thanks!!!
your tutorials are always easy to follow! thank you!
Amazing explanation... Love to watch your playlist...
Easy Solution to a very annoying problem! Great video!
Gracias por compartir tus conocimientos.
This video helps me a lot! Thank you!
Thanks so much for another wonderful lesson.
Excellent tutorial, Thank You!
Thanks very much.
very well explained & helped me a lot.
Very good tutorial, thank you
Thanks sir highly grateful for your guidance
Great! Thank you! You saved my time.
Excellent tutorial video
thank you after 2 days of searching this worked for me
I did it step by step as you, It worked, You were of really great help men. I would give 10 out of 10
Thanks Sanjeev... Glad you found it useful :)
You saved my life, thanks for the video.
Nice explanation. Kudos
Thank you one again, especially for covering why the 'combine' button can give an error if file names inconsistant.
Very useful info, thank you
Excellent job. Well done!!
Very Well Done!!!!
Fantastic this saved me as I was struggling with this so much
Thank you, on point
I found this method to be the best... thanksssssssss.
Thank you sir
When I am appending the files the headers of each file is coming before data. How can I remove the header of each files from being imported?
Excellent explanation! Easy to follow and implement on my files. I used the power query to combine excel files from a folder. There are hyperlinks in the excel files that are not transferring as hyperlinks. Is there a way to correct this?
Wonderfull
Great Video!
I like very much the way you explain the subject.
All videos are great
Thank you for share the files to work along with the video.
Thank you for sharing the video.
A couple questions
What if spreadsheet C is missing a column that was in Table A. , and you still want to combine all the columns in A, B, C and so forth?
Can we do that?
Can workbooks have extra tabs for which we do not want to combine the data? Suppose all Tables have wanted data, but the colums are in different order?
It looks like the included "Power Query Course Download files" are the result files AFTER performing the exercise. Or at least in some cases. It would be helpful if they were the source files that you start the exercise with so we end up with the same results. Some files also appear to be missing in the zip file compared to the exercise.
Thanks very, very detailed and easily understood. But I have hyperlinks in my data and I lost them. Can you please tell us how to keep the hyperlinks in the query and load ?
Hi ur video is very informative... Thank you.... I have some data but getting error in one column only.... Can u pls help....
Brilliant. You just cleared a cloud of confusion between combining and editing straight and transform data before the combine.
Woow 👍
Good video on teaching me on using power query. But when I follow ur steps in combining the tables for 12 months of a year. I just got 2 columns : Month, Table. But not a full expanded table.
Pls advise how to fix this. thanks
Will it load data if there is changes in headers?
Hi, great tutorial. However, I would like to load the data into an existing sheet, but the option is greyed out. What could it be? The source is a Folder.
Hi Sumit! Thanks for this tutorial video, this is very helpful!
I need to do something like this, however, I would need to add 2 additional columns to the consolidated tables. I need to add "Country" and "Date Extracted (MMM-YY format)". Is this doable in Power Query? Or do you have an existing tutorial on this?
btw, for Country column, each excel file comes from different countries, so for ex. first xls is from Canada, so whole Country column should be Canada. Then next xls is India, so the next combined table will be followed by India.
I hope you can give me advise. Thanks!
Hi Jam. I’ve done something similar. If you have a fixed naming convention, then you can transform with additional column. Eg if the file name is Canada Feb 2021.xlsx, you can transform the file name to create those columns
Hey Sumit does this append all the data from different files? I noticed we didnt need to use the append function of PQ with this method. Can you plz clarify this for me?
What is the logic of removing the other columns and keeping two at 6:08 ? If you keep those column does it give some other result than expected?
Hello in minute 4:35 instead of Excel Files if we have .csv files we can´t do with Excel.Workbook(), What function to use in this case? Tks in advance
When filtering out .extensions, why do you go to Text filter instead of just "uncheck"?? I saw that by many videos of other people...could you tell if there is a reason or any advantage/disadvantage? usually I am using uncheck...
I think that's becase he wants to filter all the files which have the extensions that begin with .xls, so that even macro files with .xlsm would also be considered. If he used the filter option and checked .xlsx only the xslx files are considered ignoring the rest. Hope I make sense.
If I need to combine Sheet kind and get rid of rows which doesn't have values while combining..Do I need to work on sample file query?
Which version of excel you are using?
I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).
Hi sir, thank you for your videos, which is vary helpfull, i have one challange.. everyday i have to send the cdr (calls count) report every hour, i have tried with power query but the problem is wen ever i replace the data in table the power query piot counting histry(yesturday) count also need help on this plz
Tell me please. How to track changes in the source data?
Is it possible to change sheet names in existing power query files.
Hi bro! While adding the column using the syntax Excel.Workbook(Content) under tables I am getting Error and not Table. Where I am doing wrong?
Will merging of file work through power queries if the first column of the 2nd file have duplicates?
Yes, it would still work. You can get rid of the duplicates once the files are combined
Can the query only import or update new data from an updated excel file in the folder? I mean, it will not duplicate old data and only import new data that did not already exist in the excel files every time someone refreshes.
Example: You created the North-west data file in the video (9:45/10:30), the query imported all the rows in the file, which is a duplicate of North data file. What if the North-data file is updated weekly and the folder has North-data_week01, week02, .....Week04.
The North-data_WK04 file has content from week 01, how do you setup power query to only import the newly updated value in week 04 file and not duplicate all of its data?
How can i use this method for folder containing PDF files only including tables?
I want combine excel as a verically.not upend,is this possible??
Hello. I don’t have the “Get Data” option under the “Data” tab. Please help.
what do you mean by using LOCALE?
What do you mean by CONTECT IS THE NAME OF COLUMN, what column are you talking about, there are more than a few columns in a table, I am confuse can you please explain in detail?
@4:01 - it’s not the Content column of the excel files but the Content column that PQ brings back as a reference to the content of the underlying file. So it should be the same when you run the same process. But it’s referring to the name of the column that holds the content of each of the files being combined.
what if we have sheets instead of tables?
I followed these steps and it gave me output… when am updating any rows data it’s giving output accurately however if am updating columns to source data it’s not getting picked in power editor. Source data is in table format only ….. Please help pleaaasseee
Hai I want to help
How to split the 720x400x0.010
Hi, can i merge 30 workbook holding 46 lakh row
If excel files have different columns then what to do
when i add new files , shows key didnt match any rows in the table
What to do if folder location is changing everyday
only Sheet type is showing , table type is not showing while clicking on table.
Did anyone not have the edit option after choosing the folder
This is not I am looking for. It doesn't sum all tables. It just append and there is more easy way to append the tables. But question is how to consolidate inner table data into one table?
Hmm. I have no Tables in my 70 files, only Sheets. I am okay with Sheets if it still grabs all the data :)
I can't emphasize the importance of this video!
***imp
This doesn't show how to merge multiple excel files with multiples tabs... it's so frustrating! Great tutorial nonetheless
यदि सारी एक्सेल के फाइल का डाटा मिला क्या 20 लाख होगा कि तो भी हो जाएगा???
This is not useful at all. You keep vacillating back and forth between the proper method and showing what would generate errors. You need to walk straight through doing it the proper way. Then you should go back and show missteps that would generate common errors.
This is not a useful presentation at all.