Excel - Combine Data from Multiple Workbooks with Multiple Worksheets - Advanced Power Query
Vložit
- čas přidán 30. 07. 2024
- Learn how to combine data from multiple workbooks that have different worksheet names or multiple worksheets using Excel Power Query. We'll go over different scenarios, start from basic and gradually build over our existing knowledge to get more complicated results. We'll also make sure that the master combined file automatically updates when we add new Excel workbooks to out source folder.
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 #advanced #powerbi
There are a plethora of techniques on combining tables and workbooks out there but I like yours the most for their simplicity and clarity.
Wow this was great & saved me hours of work. New to Excel 2016 and I really appreciate the detailed explanation.
Glad it helped!
GREAT video, thank you. Is there a way to then merge the data/rows and columns based on a primary key/same value in a column?
very useful tutorial, THANKS bro!
This is Great!
hi, is their anyway i can get files from diferente folders?
Masterful thank you. But what if the work files are in multiple subfolders of a shared Google Drive location?
If the files in the folder are added every month so some files have 30 tabs, one for each day and some have31 or 28 for February, I think this should work for that scenario, right?
Thanks for sharing. How can I do the refresh automatically each time I add files into the folder?
There is no easy way to do this. You can only set it up to refresh every n minutes.
This is a wonderful video...of the 3 scenario's...do you have a video giving detail on how to do just the middle scenario? We are trying to combine 20+ files within a folder...each file has 1 tab and each tab has a different name...could you lead us to the correct video please...thank you
I'm not sure, but I think this video already covers everything you need.
Thanks for sharing, this helps alot. Following your method I imported data from workbook, saved a connection. Later, i can’t find the option to create function in left panel. Anywhere I should look in specific? Im on Excel 2016.
Under data tab there is something like quires and connections. There where you can find it.
Crazy!
Hello, is is possible that at a specific date it will stop updating from other workbook? For example IF today() is the day 2019-09-09 linked number stop updating and it will be constant number.
It's always connected to the source data. No, you would just have to disconnect it manually or use VBA to disconnect it.
Hi, this was a great lesson, I do have a similar situation like the Advanced example after following your steps, the table output is showing only "null" entries in the columns. The file names and tab names are being pulled accurately, I'm not sure where I'm going wrong with the columns. Of course the source data does have entries. Any assistance would be greatly appreciated. Thank you.
Are the column names the same in these files?
@@ExcelGoogleSheets Yes, I do have the same column names in all of my 6 files and each file has about 20 tabs uniquely named
@@mohammedm2018 Are column names in the first row?
@@ExcelGoogleSheets The columns I'm interested in are starting on row 3, and in the first row, not every cell has an entry, there are some blanks in the first rows.
It's going to look in the first row to find column headers. If not found it will fail.
You would need to do extra steps to explain PowerQuery where to look for column headers if they are not in the first row.
Removed the question, found the answer. :) The problem I am having now is how do I remove **Different** number of rows from each file automatically before starting to work with them in Power Query? The reason for that is my header rows are X number of rows down in File 1, and Y number of rows in File 2.
When loading data from the query, there's "Expression.Error: The column 'Column6' of the table wasn't found". how to solve this?
Excellent, can you extend this video for POWER BI users?
There is not much difference. It's the same tool.
In the basic procedure, why was it that you only established a connection rather than just having imported the data directly? Given that you needed to create and invoke a function, which could seem like extra work, what advantages do you see in doing it that way? By the way, thanks for the effort into making these tutorials.
Basic example was just Step 1 to explain how you create a function and how it all works. I have a different video that demonstrates the easy way to do the basic example in this video. czcams.com/video/mRznmfaxKDo/video.html
¿Alguna herramienta igual o similar en Google Sheets o google docs? Que transformación y combinación de datos como Power Query.
No, just formulas & scripts.
On the intermediate sample, if the tab names change in the next time i upload something else with the same dataset, will it impact the query?
I'm not sure what that intermediate example was at this point, but if you just open the advanced editor and read through the code, if you see any mentions of particular tab names then it won't work when you change them. If you don't see any mentions or tab names, then you should be good.
Excelente
Hello. I am trying to consolidate 26 spreadsheets of trial balance data. Each tab has the month (ex. 01.2018, 02.2018 etc). How can i make them appear horizontaly ? I tried pivot column but it didn't work. Thanks
I'm not sure what you mean by horizontally.
hi while doing iam getting "[Expression.Error] rows did not match", what should i do about that error
make sure your source file data has column names and they are in the first row.
Could you share the exercise files?
Great video. thank you. Is it possible to reach you out via email for any queries? Much Appreciated.
Hi, great video. Can Power Query help me autobackup my worksheet? basically, I want my main Sheet to transfer and update entries in my back up worksheet.
1. If item in main sheet is not in backup - copy
2. If item in main sheet is in back up - update
3. If item in back up is not in main sheet - do nothing
No. You would nee to use Macros, but frankly it's probably best to use some backup software for this.
wb Ma users? install an add on for 50 usd? why is this not explained before i watched the entire video? waste of time