Excel Power Query #09: Merge Multiple Worksheets in Workbook To New Table using Append Feature

See how to Merge Multiple Worksheets in Workbook To New Table using Append Feature:
1. (00:08 minute mark) Problem Setup
2. (00:39 minute mark) Table Feature for getting Tables into Power Query
3. (00:53 minute mark) Use “From Table” feature in Power Query to get Tables into Power Query
4. (00:59 minute mark) Keyboard Differences in Power Query between Excel 2010 and Excel 2013
5. (02:13 minute mark) Discussion of Keyboard Shortcuts as a “Dance”
6. (02:33 minute mark) Merge Multiple Sheets in Workbook To New Table using “Append Feature”
7. (03:51 minute mark) Bug in Power Query November 2014 Update: Number Format not being picked up when Query is refreshed.

[excelisfun]

Excel Power Query #09: Merge Multiple Worksheets in Workbook To New Table using Append Feature

See how to Merge Multiple Worksheets in Workbook To New Table using Append Feature:
1. (00:08 minute mark) Problem Setup
2. (00:39 minute mark) Table Feature for getting Tables into Power Query
3. (00:53 minute mark) Use “From Table” feature in Power Query to get Tables into Power Query
4. (00:59 minute mark) Keyboard Differences in Power Query between Excel 2010 and Excel 2013
5. (02:13 minute mark) Discussion of Keyboard Shortcuts as a “Dance”
6. (02:33 minute mark) Merge Multiple Sheets in Workbook To New Table using “Append Feature”
7. (03:51 minute mark) Bug in Power Query November 2014 Update: Number Format not being picked up when Query is refreshed.

[thecrucible]

Thanks ExcelIsFun you wouldn't believe how long I've been searching for a solution to consolidating tables.

[qaisarc]

As always wonderful to watch Power Query in action. Thanks for the regular stream of videos!

[joeshaer]

Perfect perfect perfect!!! This is exactly what I've been looking for. Thanks!

[every-dayjoe]

Mike, I can't express the gratitude I have for your knowledge and passion for what you do. I have a rather large project I'm working on and the dataset size varies from month to month. I handle all rebates for our company and we have over close to 200 locations and growing daily. Each month we have customers that send in rebate requests. Those rebate requests consist of Invoice #'s and amounts,  and the customer list changes each month. Currently, I have a Macro which combines each individual workbook from a folder into one file. So, we now have all requests in one workbook and each customer is on their own sheet. Is there a way to get Power Query to recognize all sheets in a workbook automatically? The problem is that each month the sheet number could vary from 100 to 120 etc... I know I could right a macro to combine all data sets into one sheet but at this point I'm so impressed with power Query and Power Pivot that Macros have taken a back seat to this wonderful addition to Microsoft.
Note: I'm using 2013 but I have 2010 as well.

[galenaz]

OUTSTANDING just what I needed THANK YOU

[HumbaHarry]

Great Vids ! Awesome !

Is there in P Query a similar function which selects uniqu data from a column like the DITINCT function in DAX?

[xezox]

Great tip. If I append data from a different workbook with 22 tabs (23 appends), then later want to change the source file, is there an easy way to change the file name in the Advanced editor section?

[sumardjo]

thanks you your knowledge
useful for me
thanks you to sample file
thanks very much

[supadupaho]

Is this very process capable if your version of Office 2013 (365 Home) is not compatible for the power query add in via some other method?

[fashiongate]

It is awesome! thanks for this wonderful job !!! 

[ExactProBi]

Hi, excellent tutorial. I have challenge : How do I combine multiple Trial balances (month trial balance has three columns - Code, Description and Amount columns) of each month to one trial balance where Account code, Description would be common among all months and I will have months/amount columns separately for each month side by side (say Jan to Dec)??

[DanAlvard]

Hi MIke.. In this exercise, how can I consolidate all the data sets from each sheet such that the resulting consolidated file will include a column from respective Sales Agent which is part of the filename? I tried to make some runs to solve this problem guided by similar example in Power Query 8 exercise. Any help?

[jocelynbeier]

I'm having issues with my Append Table not refreshing. I am working with a very large data set (WS 1 - 613 Rows, WS 2 2676 rows, WS 3 2399 Rows). My original query to each of the Worksheets originates from SharePoint, and there is no issue with the refresh on each worksheet. However my worksheet which has my Append table on it will not refresh. What am I doing wrong?

[alistairsmith]

How do we transfer the same "query" to another source please? - without reinventing the wheel?

[preludefugue]

Is there any easy way if I have more than 20 sheets per each workbook need to combine? I thought about using macros to do it, but the problem is this 20 sheets have all different tab name.... also each workbook contains some other sheets that I don't want to combine...

[kataramukesh]

Not work for above 1000 row and column

[GenePatNic]

There is a way to avoid this bug. Do not load the AllData query as a table just create a connection then create the consolidated table by going to Data:Existing connections and choosing the Power Query AllData.