ฝัง
- เผยแพร่เมื่อ 4 ส.ค. 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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.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.
Thanks a lot! Just to the point - quick and clear!
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 :) th-cam.com/video/mZbD8aduIJU/w-d-xo.html
Thanks
Yes, even and uneven was the challenge I was presented with.
Several years late to the party, but still extremely helpful. Thank you for simplifying my work day!!!
Thank you. This is very helpful.
Thank you very much. You saved the day.
Loved it. Thanks for creating this man.
Glad you liked it!
Thanks for getting back to me on my question. Your other teaching did solve my problem. Good teaching. Keep up the good work.
Awesome!
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
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.
Awesome video..no junks in explanation..Thanks a lot..
This is a really awesome tutorial.. you made it look very easy. Thanks!
It is actually pretty easy
This is amazing and well explained. Next question is how do you auto refresh the power query to avoid having to manually refresh?
Thank you from Nepal 🇳🇵
Excellent. Very helpful !!
Awesome!!!!!!! Thanks so much!!!!
Thanks a lot and you saved my time more than 3 hours..
Keep on assisting us. All the best
Glad to hear that
Great Work!!!
You're a lifesaver bro!!! Great video.
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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
Thank you so much! Highly appreciated.
Glad it helped!
Thanks a lot, its very useful, i saw others video, but yours one is details and clear info,
Wow. Excel…ent ‼!
Thanks for this so help full tutorial
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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
Thanks a lot. Very kind of you
Thank you for this informative video.🙏😊
Thank you! This is a godsend!
Haha.. thanks Nathan!
Thanks for sharing this video, very helpful.
Glad you found it helpful
Thanks for such a useful video..
good knowledge sharing & here comes the new friend
Thank you so much.
very useful, thanks a lot
You are awesome man
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
Greate job. very usefull turorial is it !.Your description and topic Just awesome.Keep it Up, Thanks
Thanks a lot!
Thank you for sharing this video this is the very helpful for combine multiple excel data into one sheet
Glad it was 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
Amazing and awesome... great video and superb explanation... confusion cleared 🙏☺️
Glad you liked it!
@@GoodlyChandeep please reply to my query.
Thanks!
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...
Wow it is just amazing . I am very thankful for you for this video
Glad you liked it :)
This is very helpful. it saves me a lot of time. thank you
Glad it helped!
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
many thanks
thanks for video.
Thank you 👏🏿
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
Thanks brother
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.
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 ???
very useful
You have explained in best way with all the scenarios tqq soo much sir
You made my work very easy
You are most welcome
Great!
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??
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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
good explanation
Superb way of explanation, thank you so much for your efforts and guidance, Goodly ji
Thanks and welcome!
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 for this
My pleasure!
Amazing .. Thanks so much
Glad you found it helpful
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/
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?
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?
Thanks sir
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
Very helpful
Glad it was helpful Manoj!
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
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?
This is awesome
You'd find this better - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
and I am glad you liked it :)
Teşekkür ederim
Glad you like it!
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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
Cheers
Amezing ❤❤❤❤❤
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?
Wow nice trick.
Thank you! Cheers!
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! - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
This would give you more flexibility!
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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
Can we use powerquery to make the data in panel data structures?
I wonder if you have any promotion code available for your DAX course? many thanks!
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.
Is this new combined file also linked with the source file also.
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
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 - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
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
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
You are a life saver! While i was SUPER excited to try it (and i did try it), i faced a problem. My files' sheet names are dynamic and they are different for every file. Is there anything i can do to overcome it? It's not a very big hiccup but would be nice if i could! and thank you for your video. I subscribed!
Yes you need to do it slightly differently. Use 2 bits of code and then combine, which makes the sheet name irrelevant. May be another video for it. If you send me some sample data I'll help you out - goodly.wordpress@gmail.com
Thanks!
@@GoodlyChandeep i have the same structured files buti tried 6 times a d everytime it was good until combine but error everytime while loading.
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 ??
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?
Very good video, I have to extract common data from 2 different excel files into a new excel file. How?
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
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)
Thank you for sharing this video!!! This is really useful!
I have one question, is there any way I can update the master file and the data files will be updated automatically?
Unfortunately no.. The data always flows from the source to the query output and not the reverse
Goodly
Got it! Thank you!
@@GoodlyChandeep thanks but i am facing an error. I update data files with new rows and new data, but the master file is not loaded, even i click 'refresh' . It is showing ' data sources not found'
@@roniedhaka make sure the data source location is the same.
Thanks mate its really awesome. Quick question for me the tab names are different how can we handle that? do you have some solution?
Make a video on it soon!
Goodly is the video up yet? I am stuck and need to read data on 1400 workbooks with different sheet names, but in all the workbooks, the data is in the same column
@@christianrodriguez841 Sorry Christian, I din't know you were looking out for it, I'll produce the video max by Monday.
If there are any specifics that you want me to cover please shoot me an email - goodly.wordpress@gmail.com
thanks for waiting
Chandeep
Watch this please - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
@@christianrodriguez841 Watch this please - th-cam.com/video/mZbD8aduIJU/w-d-xo.html
Can I use a power query in my 2010 version?
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?
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