Combining multiple excel sheets from multiple workbooks into one table

ABF87

New Member
Joined
Mar 19, 2016
Messages
6
Hello,

I have 12 excel 2010 workbooks with 6 tabs each that I need to combine into one table using either PowerQuery or PowerPivot. I could do it manually, but that would take a few hours and it's not really efficient, so I have been trying to figure out a way to do it using one of the BI tools (or VBA if that would be better). Here's an example of what a workpaper would look like:


  • (1 wb) January 2010 workbook
  • (6 tabs) Tabs A,B,C,D,E,F
  • each tab has the following columns that I need to combine: 1) Price Variance, 2) Mix Variance, and 3) Volume variance
  • The Tabs names are consistent across the 12 workpapers (i.e. tab A), and the variances are all located in the same columns (i.e. price variances are in column "C" in each of the six tabs for all 12 workpapers)

1) Each of those variances are formula-driven. Would I need to hardcode them before I do anything else in order to combine them?

2) Other than in the name of the workbook, the month is not listed anywhere else. Do I need to add a column indicating the month for each of the variances? Or is that step redundant since one of the BI tools would already separate the data by month based on the name of the file?

3) Similar to question 2), Other than in the column heading, the type of variance is not indicated anywhere. Do I need to add a column indicating the type of variance for each of my three variances before I can combine the data into one table? (i.e. have the data in tabular form)

4) Would it be easier to combine all the data into three different tables (i.e. one table per variance) and then bring all together into one table?

5) Once I figure out steps 1-4, how would I go about combining the data? (I found multiple tutorials online, but all of them assume that the data is hardcoded, and they are only trying to combine one column)

Thank you very much in advance!
ABF87
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
Power Query is a great tool for this. I have written many blog posts on the topic.

Consolidated Worksheets with Power Query - PowerPivotPro
Combine Excel Workbooks with Power Query
Combine CSV Files with Power Query - Excelerator BI
Combine Excel Workbooks with Power Query - Method 2 - Excelerator BI

Answers
1. No
2. Yes. You can do that in Power Query.
3. Yes, as with 2
4. Probably. Do this in Power Query. Import each of them, reshape the and "create connection only". Then append them together
5. Look at my links
 
Last edited:

ABF87

New Member
Joined
Mar 19, 2016
Messages
6
Hi Matt,

I can't thank you enough for your help. I was planning on spending an entire Sunday doing all this work manually, so you have effectively saved me hours of work that I will get to spend with my family (plus this is something that I will be working on every month, so the savings will compound!)

I've noticed that you are the author of Learn to Write DAX, and since you clearly know your stuff, I will buy it from Mr. Excel.

Thank you once again.
 

ABF87

New Member
Joined
Mar 19, 2016
Messages
6
Hi Matt,

I am working through the steps of Consolidated Worksheets with Power Query - PowerPivotPro, but I am getting an error message when I add the first line of code:


<span style="color: #ff0000;"><span style="color: #000000;">1 </span><strong>(mySheet)=></strong></span>


When I click on "Token Literal Expected", the < symbol (smaller than) gets highlighted as the error. If I try to remove that symbol, then the word "style" gets highlighted as an error.


Any insights into what I could be doing wrong?


Thank you,
 

ABF87

New Member
Joined
Mar 19, 2016
Messages
6
Rich (BB code):
1 (mySheet)=>
let
    Source = Excel.Workbook(File.Contents("C:\Users\ABF\Downloads\Consolidate-Worksheets.xlsx"), null, true),
    Jan_Sheet = Source{[Item="Jan",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Jan_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProductKey", Int64.Type}, {"OrderDate", Int64.Type}, {"CustomerKey", Int64.Type}, {"SalesTerritoryKey", Int64.Type}, {"SalesOrderNumber", type text}, {"SalesOrderLineNumber", Int64.Type}, {"OrderQuantity", Int64.Type}, {"UnitPrice", type number}, {"ExtendedAmount", type number}, {"UnitPriceDiscountPct", Int64.Type}, {"DiscountAmount", Int64.Type}, {"ProductStandardCost", type number}, {"TotalProductCost", type number}, {"SalesAmount", type number}, {"TaxAmt", type number}, {"Freight", type number}, {"RegionMonthID", type text}})
in
    #"Changed Type"
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
you should not have the 1 in the first line of code

it should be
Code:
(mySheet)=>
Looks like something stuffed up when Rob updated the website.
 
Last edited:

ABF87

New Member
Joined
Mar 19, 2016
Messages
6
you should not have the 1 in the first line of code

it should be
Code:
(mySheet)=>
Looks like something stuffed up when Rob updated the website.

Hi Matt,

I actually didn't have a 1 in the first line of code (it's my first posting here, so I wasn't sure how to post code. Here's what I see when I select January and go to Advanced Editor:

Rich (BB code):
let    Source = Excel.Workbook(File.Contents("C:\Users\ABF\Downloads\Consolidate-Worksheets.xlsx"), null, true),
    Jan_Sheet = Source{[Item="Jan",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Jan_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProductKey", Int64.Type}, {"OrderDate", Int64.Type}, {"CustomerKey", Int64.Type}, {"SalesTerritoryKey", Int64.Type}, {"SalesOrderNumber", type text}, {"SalesOrderLineNumber", Int64.Type}, {"OrderQuantity", Int64.Type}, {"UnitPrice", type number}, {"ExtendedAmount", type number}, {"UnitPriceDiscountPct", Int64.Type}, {"DiscountAmount", Int64.Type}, {"ProductStandardCost", type number}, {"TotalProductCost", type number}, {"SalesAmount", type number}, {"TaxAmt", type number}, {"Freight", type number}, {"RegionMonthID", type text}})
in
    #"Changed Type"
and here's what I am adding to it:

Rich (BB code):
[COLOR=#006FE0 !important]<[/COLOR][COLOR=teal !important]span [/COLOR][COLOR=#002D7A !important]style[/COLOR][COLOR=#006FE0 !important]=[/COLOR][COLOR=#1FBBDD !important]"color: #ff0000;"[/COLOR][COLOR=#006FE0 !important]>[/COLOR][COLOR=#006FE0 !important]<[/COLOR][COLOR=teal !important]span [/COLOR][COLOR=#002D7A !important]style[/COLOR][COLOR=#006FE0 !important]=[/COLOR][COLOR=#1FBBDD !important]"color: #000000;"[/COLOR][COLOR=#006FE0 !important]>[/COLOR][COLOR=#009999 !important]1[/COLOR][COLOR=#006FE0 !important] [/COLOR][COLOR=#006FE0 !important]<[/COLOR][COLOR=#006FE0 !important]/[/COLOR][COLOR=#002D7A !important]span[/COLOR][COLOR=#006FE0 !important]>[/COLOR][COLOR=#006FE0 !important]<[/COLOR][COLOR=#002D7A !important]strong[/COLOR][COLOR=#006FE0 !important]>[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#002D7A !important]mySheet[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#006FE0 !important]=&[/COLOR][COLOR=#002D7A !important]gt[/COLOR][COLOR=#333333 !important];[/COLOR][COLOR=#006FE0 !important]<[/COLOR][COLOR=#006FE0 !important]/[/COLOR][COLOR=#002D7A !important]strong[/COLOR][COLOR=#006FE0 !important]>[/COLOR][COLOR=#006FE0 !important]<[/COLOR][COLOR=#006FE0 !important]/[/COLOR][COLOR=#002D7A !important]span[/COLOR][COLOR=#006FE0 !important]>[/COLOR]
and I end up with this:

Rich (BB code):
<span style="color: #ff0000;"><span style="color: #000000;">1 </span><strong>(mySheet)=></strong></span>
let
    Source = Excel.Workbook(File.Contents("C:\Users\ABF\Downloads\Consolidate-Worksheets.xlsx"), null, true),
    Jan_Sheet = Source{[Item="Jan",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Jan_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProductKey", Int64.Type}, {"OrderDate", Int64.Type}, {"CustomerKey", Int64.Type}, {"SalesTerritoryKey", Int64.Type}, {"SalesOrderNumber", type text}, {"SalesOrderLineNumber", Int64.Type}, {"OrderQuantity", Int64.Type}, {"UnitPrice", type number}, {"ExtendedAmount", type number}, {"UnitPriceDiscountPct", Int64.Type}, {"DiscountAmount", Int64.Type}, {"ProductStandardCost", type number}, {"TotalProductCost", type number}, {"SalesAmount", type number}, {"TaxAmt", type number}, {"Freight", type number}, {"RegionMonthID", type text}})
in
    #"Changed Type"

Thank you very much,
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
Yeah, like I said the website seemed to be a bit stuffed up. I have fixed it, so it should be clearer now.
 

Forum statistics

Threads
1,082,295
Messages
5,364,359
Members
400,792
Latest member
Dxmiian

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top