Excel - Combine Data from Multiple Workbooks with Multiple Worksheets - Advanced Power Query

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.

#excel #advanced #powerbi

[decentmendreams]

There are a plethora of techniques on combining tables and workbooks out there but I like yours the most for their simplicity and clarity.

[kevinwitthar]

Wow this was great & saved me hours of work. New to Excel 2016 and I really appreciate the detailed explanation.

[caitlinryan]

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?

[goncalobarroso]

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?

[triesjeflagg]

Thanks for sharing. How can I do the refresh automatically each time I add files into the folder?

[xperia]

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.

[wuilmerponte]

Excellent, can you extend this video for POWER BI users?

[merlinstelzersales]

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

[HeadingForTomorrow]

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.

[mohammedm]

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.

[cm]

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?

[nataliaoleksander]

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?

[wahidsayed]

Great video. thank you. Is it possible to reach you out via email for any queries? Much Appreciated.

[hojasderuta]

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.

[nayating]

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

[expertoexcelgt]

¿Alguna herramienta igual o similar en Google Sheets o google docs? Que transformación y combinación de datos como Power Query.

[puiyeetou]

When loading data from the query, there's "Expression.Error: The column 'Column6' of the table wasn't found". how to solve this?

[aurimasvalciukas]

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.

[elenatsimachidou]

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