Combine Data from Multiple Excel Files in a Single Excel Sheet - Part 1
HTML-код
- Опубликовано: 22 июл 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video you'll learn to combine data from multiple excel files into a single sheet using Power Query. In this case I am assuming that your data structure will be the same
- You can watch Part 2 (for combining uneven data) - • Combine Data from Mult...
- New video for handling multiple sheets and multiple columns in a dynamic way - • Combine Data From Mult...
- The full blog post can be found here - www.goodly.co.in/merge-data-f...
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - - Blog - - - -
www.goodly.co.in/blog
For combining data from multiple excel files with multiple sheets and dynamic columns, watch this video - ruclips.net/video/mZbD8aduIJU/видео.html&
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Well explained and here comes the new subscriber ☝️
@@karma9237 Welcome to the inner circle! :)
Instablaster...
You have provided the best explanation of this process that I have found online. Precise and to the point!. Well done!!
Wholeheartedly agree with you. Thank you Chandeep.
This was the best tutorial ever! Best explanation in a very methodical manner and straight to the point.
Just an amazing tutorial. Straight to the point. Best explanation I've seen as it covers both even and uneven data structures
You'd find this better :) ruclips.net/video/mZbD8aduIJU/видео.html
Thanks
Yes, even and uneven was the challenge I was presented with.
Thanks a lot! Just to the point - quick and clear!
Several years late to the party, but still extremely helpful. Thank you for simplifying my work day!!!
Thank you very much. You saved the day.
Thank you. This is very helpful.
Loved it. Thanks for creating this man.
Glad you liked it!
This is a really awesome tutorial.. you made it look very easy. Thanks!
It is actually pretty easy
Thanks for getting back to me on my question. Your other teaching did solve my problem. Good teaching. Keep up the good work.
Awesome!
Awesome video..no junks in explanation..Thanks a lot..
Amazing this saved me 30 min of daily work , used to copy 15-17 files manually into a master excel for my work
Glad it helped.
thank you for this ,i was searching this solution from last 3 hrs but didn't get exact solution ... n finally i m satisfied with this video
Thanks a lot, its very useful, i saw others video, but yours one is details and clear info,
This is amazing and well explained. Next question is how do you auto refresh the power query to avoid having to manually refresh?
Wow. Excel…ent ‼!
Thanks for this so help full tutorial
Great explanation!!! I have a custom made invoice template, total of 200+, I"d like to combine the columns data but The invoice # and invoice date are not part of the columns. Thanks...
AMAZING! THANKS WHAT A GREAT VIDEO!!!! you just saved be a whole bunch of work today and in the future!!
Glad it helped! You'd find this one slightly more robust and dynamic - ruclips.net/video/mZbD8aduIJU/видео.html
Thank you very much, it was very helpful. How Can i make the Same formatting And keep the formulas also? This way it is giving me a simple Excel sheet without the formatting That I had
Thank you from Nepal 🇳🇵
Excellent. Very helpful !!
Thank you for the amazing video. It's a great help. I have xml spreadsheet 2003 files and I cannot apply this. I have to manually save them as xls, then it works. Is there a way to set them differently in the query?
Thanks .. glad you like the vid. There are many vba macros / applications out there that help you convert the xls files into xlsx files with a single click.
one link is here - www.zamzar.com/convert/xls-to-xlsx/
another one - www.docspal.com/convert/xls-to-xlsx
I just googled them, really not sure how efficient they are!
hope it helps
Thank you so much! Highly appreciated.
Glad it helped!
Thanks a lot and you saved my time more than 3 hours..
Keep on assisting us. All the best
Glad to hear that
You're a lifesaver bro!!! Great video.
Really helpful. Thank you so much. Shall I get material for the same. Actually I am interested in analytics side. I am trying learn more about pivot's and vba
Very unique lessons found no where else!
Glad you found them useful!
really awesome my man. was looking for something like this to use at work. thank you
Glad I could help!
You should see this as well - ruclips.net/video/mZbD8aduIJU/видео.html
Thank you for this informative video.🙏😊
Great Work!!!
Awesome!!!!!!! Thanks so much!!!!
Thanks a lot. Very kind of you
very useful, thanks a lot
Thank you! This is a godsend!
Haha.. thanks Nathan!
Thank you for sharing this video this is the very helpful for combine multiple excel data into one sheet
Glad it was helpful!
Hey dude thanks so much information was really helpful for me n could apply in my work as well. 2nd important thing is the way you have exlained throughtout the viseo its just awesome to understand. Thanks again keep it up. Grt efforts Regards, Sachin
Thanks for sharing this video, very helpful.
Glad you found it helpful
Thanks for such a useful video..
This is very helpful. it saves me a lot of time. thank you
Glad it helped!
HI I need to do this with rows. Where I have 1000 lines of logging information from an instrument where the time is indicated on the rows. and Where the different values are indicated in the columns. can you assist.
Hello, Thanks!! I really understand how to combine Excel files in one excel sheet.
I have a little problem in the last step when you charge the editing file, He charges only 28 rows in 270 rows when you combine it. May I have an explanation ?
Thank you very very very much , it was really helpfull ,
URGENT Q- can i separate the combined files by inserting each combined file's name before it's data?
example : file year - 2005 then data , then cell contains "file name : year 2006" then data ,,,etc ???
Hello,
Very great tutorial on this. When I click close and load, I get an error message stating “[Expression. Error] They key didn’t match any rows in the table.”
Do you know why this could be?
Superb way of explanation, thank you so much for your efforts and guidance, Goodly ji
Thanks and welcome!
Thank you so much.
thanks for video.
many thanks
Thank you 👏🏿
Hi, I want to add an additional column to the table where the combined data is placed, this column is required to update the status of each line item, which feeds to another report. the problem is that the additional column is not linked to the rows. i.e. whenever the data is refreshed, the status column is mismatched from the entire data.
Can you help??
Wow it is just amazing . I am very thankful for you for this video
Glad you liked it :)
Hi, many thanks.
I did consolidated my 5 different worksheets into a new sheet. But i got error when i add a new row of of data in any of these 5 sheets and click 'refredh all' button in power query. It is showing ' database not loaded' why?
You are awesome man
Thanks!
Greate job. very usefull turorial is it !.Your description and topic Just awesome.Keep it Up, Thanks
Thanks a lot!
Amazing and awesome... great video and superb explanation... confusion cleared 🙏☺️
Glad you liked it!
@@GoodlyChandeep please reply to my query.
You have explained in best way with all the scenarios tqq soo much sir
You made my work very easy
You are most welcome
This is awesome. Solves my issue when combining multiple csv files with similar column headers , but column headers are not in the same order in the files. Thank you.:-)
The order of the columns doesn't matter
Nevertheless this should solve your problem - ruclips.net/video/mZbD8aduIJU/видео.html
Great!
good explanation
sir this is very helpful.. what if i want to combine data from different excel files but would like to start from the 6th row instead?
Thank you for this
My pleasure!
Amazing .. Thanks so much
Glad you found it helpful
good knowledge sharing & here comes the new friend
very useful
Nice video!!! I have another problem, I have multiple files with same structure. Every file has some duplicate entries. I want a way to merge data into one sheet by removing duplicates from each file individually. can you help?
Thanks brother
The explanation was excellent. Can you tell me if there is a way to combine data horizontally instead of one below the other? It would be a huge help.
Thanks in advance.
After combining the data (vertically) you can transpose or pivot it to make it horizontal. I'd be able to tell precisely only if I see that data.
Thanks
I wonder if you have any promotion code available for your DAX course? many thanks!
Dear sir, your videos are really informative and amazing. But you should provide the practice files for the same.
Please download the files from this link - www.goodly.co.in/merge-data-from-multiple-excel-files-into-a-single-workbook/
Thank you for this video this really helped me. But I've a question, is there any way to make this concept to work while extracting the data from Password protected sheets?
Nope it doesn't work on Password protected sheets
Amezing ❤❤❤❤❤
I suppose it's important to format the range in each workbook as table in any case new rows may be added or removed. Right?
it's absolutely fantastic, how about if the file which I want to combine contains more than one tab and other tabs contain different data? Thanks
Watch this one please - ruclips.net/video/mZbD8aduIJU/видео.html
Bro I have a question in power we create custom functions by using variables or parameters. How can we create a custom function to merge the outputs of two functions. Like as nested queries in Sql. Is it possible.
Very helpful
Glad it was helpful Manoj!
Thanks sir
Thanks for your generous sharing and teaching of this powerful tool. When I tried all worked out except the last part after I hit "Close and save", I got an error message in the master file "(Expression. Error) the key didn't match any rows in the table". I tried several times but having the same problem. I was able to see the combined files data in PowerQuery but just when I hit "close and save", the numbers won't be in the master file and the error message will show up. Any suggestions? Thanks.
Try this instead - ruclips.net/video/mZbD8aduIJU/видео.html
I am so deeply grateful for your video, I have learned so much. Can I just ask, if I continue to add data to one or more of the files, will the data be combined into the main spreadsheet using refresh? I have tried, but it doesn't seem to want to display any extra data? I would value your help enormously Kind regards
It should automatically add the new file and its data associated.
Alternatively you can watch this video for more control over combining files - ruclips.net/video/mZbD8aduIJU/видео.html
Cheers
Teşekkür ederim
Glad you like it!
This is awesome
You'd find this better - ruclips.net/video/mZbD8aduIJU/видео.html
and I am glad you liked it :)
Very instructive. My problem is how to have your masterfile work from different computers or from a flashdrive so that it updates even when then folder or drive is different
Can we use powerquery to make the data in panel data structures?
Is this new combined file also linked with the source file also.
I need to use the LEFT function so that my dates don't include the seconds. Will this formula update with the new information added?
great stuff! is it possible to combine data from different files with multiple tab and combine only for a specific tab which is exsiting in all files? Appreciate your help. Thanks!
Yes you can! - ruclips.net/video/mZbD8aduIJU/видео.html
Hi I am also using 2016 but in data I getting opetion from access, from web, from test
Can I use a power query in my 2010 version?
Very good video, I have to extract common data from 2 different excel files into a new excel file. How?
hi this is really helpful, but i want ask about autoupdate, i have 2019 date from date 1 to 20th i want how can i create autoupdate firther from 21 to 30th date. pls advise
2. in this 30 days cobine data i have 60 employee with heading of emp number, name , date, and status and under status i have worked hours in numbers i.e 8,7,10, and L , SL, C/O etc. how can i create automatic single page report individual row. i tried pivot table in pivot only number will appers in value place i need numbers and text for L, SL, C/O etc. pls advise if possible
Wow nice trick.
Thank you! Cheers!
Amazing video- thank you so much! Quick question, for Excel (Office 365), what is the max # of workbooks that can be combined at one time using Power Query? Is there a limit or can this easily handle 300 workbooks, for example?
THANK YOU IN ADVANCE!
I don't think there is a limit on the number of workbooks but it depends on the memory of your machine (computer). Power Query might give an error if there are far too many workbooks.
I have seen people easily combining 200 to 300 workbooks with no sweat.
Hope that helps!
@@GoodlyChandeep thanks, loved your video, very informative straight to point, ,I am going to try this on my shared folder, it has around 700 excel files so let's hope 😉
Thanks once again
@@aymanayman7634 Try this one - ruclips.net/video/mZbD8aduIJU/видео.html
This would give you more flexibility!
Thanks for this, I have a question.. if the excel files have 8 tabs, how can you consolidate this into onne excel file?
Please watch this video instead - ruclips.net/video/mZbD8aduIJU/видео.html
Great Video
I have a task which is quite the opposite. I have data for 50 US states combined in one tab within one excel file called MAIN. Also, I have created 50 excel files one for each state, each file is named after the abbreviated state name, i.e. AL, AZ, CA, OH....etc.. My goal is to extract the data, only for each state, from the MAIN file, and place it in the corresponding excel file for each state.
Any help will be appreciated. Thank you (PS: the headings in every individual state file, matches the headings in MAIN)
Subscribed bro , what if there are some duplicate data ? Will it exclude it or not ??
Bro can i merge together all the data from different sheets into one sheets while not adding the duplicates ??
Hi, thank you for the video. I have a question. After following all the steps, the final excel sheet has lots of empty rows in between the data from two different files. How do I get rid of those empty rows?
You'll have to open the query and transform the column to remove empty rows. Please watch part2 to get an understanding of it
How do you combine multiple sheets from one file into one sheet in a new file? When exporting from Oracle to Excel the Excel data spills into multiple sheets due to row limits per sheet. Thank you
Hey chandeep, how about I want to create a calender from where users will select a date and corresponding to that date, the excel files generated on that date gets imported. You may expect the filename as abcd_yyyymmdd.xlsx. Can you make a video on this or if you can tell me this by some other means
what about the end number of sheet pls make a video on it
Can I add date range before combining multiple excel?