Excel - Combining Data from Multiple Workbooks (files) into One Worksheet - Basic Power Query
HTML-код
- Опубликовано: 22 июл 2024
- Learn how to combine data from multiple workbooks into one master worksheet using Excel Power Query. We'll go over a quick and easy way to get this done when you have your setup done correctly and we'll make sure that it automatically updates with changes in source files. More advanced techniques are covered in the following video • Excel - Combine Data f... .
Compatibility: Windows Only, Excel versions 2010 or above. Versions 2010 & 2013 must install add-on Power Query ( www.microsoft.com/en-us/downl... ). Versions 2016 or above don't need to install anything, it's included in your version.
#excel #combine
Can't thank you enough for this clear tutorial - I had 20 files to combine for work and you saved me a lot of time! Subscribed!!
OMG! you made my day!! I'd been doing a troubleshoot for my PQ for 4days... but It only took 10 Minutes to solve when I watched this!!!
Excelent explanation, it worked for me perfectly after adding column titles in each file (did not have that a the beginning). Many thanks
Thanks a Million for loading this, it made my life easy, you are a savior
AWESOME video! You are the man!
Works brilliantly, 21 files combined!
Great!
Nicely done, thank you!
Superb and Thank you for your time its life saver
Great video! Thanks!
Great video thank you so much. Would you happen to have a video that gets the same thing done in Google Sheets?
I have subscribed just for your such true and helpful content
Thanks for subbing
Hi, thats a good solution! But, what if I'm talkin about a sheet that contains like 300 hundreds lines and 30 columns, and I need to get a specific line x column, and the input of this data needs to be on a specific line x column too, is this possible?
I have followed video instruction and when applying play button it will appear to Compile error Expected end sub , Pls help me
you made my day
Could you share the exercise file?
Hi
I have 3 spreadsheet of the learner . collect it from Google forms.
All are contain common column as Mail Id.
1 file have 1400 mail id.
2.file have 1300 mail id.
3.file have 700 mail id.
I want to collect it to single excel and find 2 and 3 spreadsheet how many learner are not enter their details..
Can you help me..
DataFormat.Error: The input couldn't be recognized as a valid Excel document.
Details:
Binary
I have checked all your comments but all my excel is in the correct format but the issue still persists.
Can we do on google sheet?
Hi, great demo thanks. i have managed to transfer all dater from multiple workbooks to 1 as per demo. My question now is, from that data in the new workbook I would like to have a graph on a separate sheet within the same workbook (new tab called Summary) as the newly combined one we have created. how is this accomplished? also the data in the original files will change weekly therefore the final output is shown in the Summary tab in Graph Pie Chart format . Thanks in advance
Depends on your data. You may need to make a Pivot Table and then use it to make a Chart (this is also called PivotChart) or it's possible that you can graph it directly from your data with a regular chart. If you need to summarize your combined data first then you gonna need the Pivot method.
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
This should work ruclips.net/video/Vuh7lh2mhQk/видео.html
please i need your help in google sheet
How we do this technique with protected work book
HI, I am encountering an error while expanding
DataFormat.Error: External table is not in the expected format.
Appreciate your help
Do you have column names in the first row in all files?
Do you know why I keep on geeting an error, so the data only shows for one file the other files are error?
Hi, I am encountering the following error - I am getting the table 2's headers as one of the rows in preview. I did click use first row as headers, but it only changed in the first table. What should I do? FYI, my tables have exactly the same headers, only the data stored under each header is different.
Make sure there is no empty row above headers. Try more advanced version, see if that helps ruclips.net/video/BmxrsDntR44/видео.html
My excel not show Get data Tab ,can you help me how to activate this function plz
great explanation, do the different workbooks have to be under the same folder?
Yes.
what if there are some cells merged?
Hello, do you have another tutor video show that combines different excel sheet in different workbooks into one workbook? Thanks.
Yes, here ruclips.net/video/BmxrsDntR44/видео.html
Does this also work if you add more files into the folder?
Yes.
I am facing problem in sheet " We encountered an error while trying to connect' Detail "External table is not in the expected format.
Well. What's the format?
If you save this workbook and open it later after the source data is changed, will it auto update the info from the query when you re-open the workbook?
nevermind, should have watched a bit further. lol
The only thing that I see that does not happen is the update of the info in the combined data sheet, when I edit the source. It only modifies the data of the first book, but not the other 9 book which I have also modified... If you have any insights on this I would appreciate it very much. Many thanks!
You didn't move the files, did you?
@@ExcelGoogleSheets turns out I had to open the files where the original info is stored, and save them after changes have been done, and then it works. thanks a lot for your help and interest!
i used 2013 excel sheet please help how combine multi pal excel sheet in to one work sheet
ruclips.net/video/NCzf0_oFO6g/видео.html
Great video. Could you please link the video where you demonstrate how to merge excel files where the Tab names are different? Thanks!
ruclips.net/video/BmxrsDntR44/видео.html
@@ExcelGoogleSheets Thanks a lot. I run into issues when trying to merge files with a lot of data. I have about 10m rows that I’ll need on one sheet. After around 1m rows it will quit merging them. Is there a solution to this?
Thanks again!
Excel has max 1m rows. Why do you need it all in one sheet?
@@ExcelGoogleSheets Thanks for getting back to me. I am writing a Python code to read a CSV file and I am not sure if it can extract the data fluently if it is spread over few sheets..I guess I don’t have an option now but to try:)
I guess I don't understand why you need this data opened in Excel. Even if you put it on multiple sheets, it will likely be so slow that it won't be usable.
Thank you. It didn't work for me. Every time i want to expand the file : it shows : DataFormat.Error: The input couldn't be recognized as a valid Excel document.
Details:
Binary
How to fix it ?
It looks like you have a file in that folder that's not an excel file. It could be a hidden file.
so do I . There is no hidden file thoug
Dear friend,
You are awesome..
But the thing that i want is not available...
Ex: in google sheet 1 there are three sheets that is sheet 1, sheet 2 and sheet 3 having different attributes but in all 3 sheets one attribute is common i.e. email...
Some entries are common in sheet 1, 2, 3 and also there are some entries that are available in sheet 1 but not in sheet 2, 3...
1 or 2 entries in sheet 2 but not in sheet 1, 3.. and vice versa
Now my query is how to create master data?
In master data i want all the entries having all the attributes of sheet 1, 2, 3.
Which office is this because mine doesn't have the option for getting data from file
Windows Versions 2016 or higher. Windows 2010 & 2013 versions can install Power Query as an addon, although it does not include all the current bells and whistles.
I'm getting this error message: - DataFormat.Error: The input couldn't be recognized as a valid Excel document. Details: Binary
It looks like you have files in the folder that are not excel files. You need to either move them or filter them out during import.
I am getting an error while extracting the file to excel. Rest everything is fine. There are 107 different excel files that have the same headers in it. Still, I am facing issues. Is there any specific file type
Is it just one specific file that doesn't work?
@@ExcelGoogleSheets I had 107 different excel files and things work fine till choosing true/false. I get an error on the combined file page. please share your email id on adnankhan1234@gmail.com, I will share the details. maybe you can identify the error
Hello Sir, suppose if we have different name worksheet, then how to solve?
Check video description.
Whenever I do this I get “unexpected error. Something went wrong .if the problem continues restart excel”
Thank you! My tables work fine but Sheets don't. It always adds empty rows and right now it is showing 23,295. It should not be more than 700. Please advise? Thanks
Add a filter step to remove blanks.
@@ExcelGoogleSheets Thank you! That is what I did but I was wondering that why in your case no empty rows were added and you didn't add a filter step. Thanks
@@shoppersdream There must be a space or something in your cells below your data that's causing it to think it should be grabbing more rows.
@@ExcelGoogleSheets Thank you! It works fine with tables but with sheets, It does not. I double-checked and tried it with so many different files. Another problem I am facing is that I want to keep the top 3 rows as my header and not the first row as the header. So it makes the first row a header and then adds the rest 2 rows after every table. That is not what I want. Thanks
Hello how can I load data from hidden sheet?
ruclips.net/video/bRnXAz4KaYM/видео.html
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).
You could create a function with sheet name as parameter to get you the result (check the advanced video in description to see how), however, if I'm not mistaken, Power Query supports only one data output at a time, therefore you'll need to build one merge query for each sheet name. You will probably need a macro to automate that final step.
Anything for mac users?
You can use Node ruclips.net/video/IH7XyFde3BE/видео.html
Do they have to be tables on each of the Excel Workbooks?
Question is unclear.
@@ExcelGoogleSheets when combining Sheets through Power Query. Do the corresponding tables in each sheet. Have to be in a Table? Or can they be in raw data format. (With same headings) just curiois
hi sir, can you do filtering from the source file and only reflects to master file?
Yes.
@@ExcelGoogleSheets do you have one already in you videos sir?
This should give you a clue ruclips.net/video/vALA_KC9DtQ/видео.html
@@ExcelGoogleSheets thank u so much
only first file is showing "compile file" popup .plz help
Are you sure the rest are excel files and your user has access to those files?
If all files combined is 2 million rows do you still close and load to Excel?
Found The answer www.masterdataanalysis.com/ms-excel/analyzing-50-million-records-excel/
Close and load to a connection. You can't load it to a table since Excel only has a little over 1 mil rows available.
👍🏻
I want edit some codes in gs Google sheets
Why is this got to be so complicated, got 12 files that add up to +4m rows, and exceeded the row limit, how can get around this?
Why are you combining these? If you're trying to combine and get a summary then maybe try Power BI Desktop and do the same exact thing. It shouldn't have the 1 mil record limit that Excel has.