Combining multiple excel sheets from multiple workbooks into one table

[ABF87]

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]

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

 

[ABF87]

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.

 

[Matt Allington]

I'm glad it helped. I hope you like the book

 

[ABF87]

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]

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]

you should not have the 1 in the first line of code

it should be

(mySheet)=>

Looks like something stuffed up when Rob updated the website.

 

[ABF87]

you should not have the 1 in the first line of code

it should be

(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:

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:

[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:

<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,

 

[ABF87]

I just figured it out! All I was supposed to add was (mysheet)=>

Now, on to the next step!
Thanks

 

[Matt Allington]

Yeah, like I said the website seemed to be a bit stuffed up. I have fixed it, so it should be clearer now.