One pivottable from several datasheets with Powerpivot possible?

0
votes

I have an Excel-workbook with 12 sheets, all sheets contain between 700.000 and 1.000.000 rows. All sheets have identical column-headings.

I would like to make one pivottable with Powerpivot from those 12 sheets with data.

I have added all sheets to the datamodel, after that I try to make a pivottable by clicking Powerpivot > Home > Pivottable. Although when I click a certain columnname to use as a pivot table field, my tables are still separated in the pivot table output. Is there an option so that Powerpivot sees all 12 sheets as one table?

thank you!

Marcel

1
excelpowerpivot

1 Answers

1
votes

Please check out this method

http://www.contextures.com/PowerPivot-Identical-Excel-Files.html

In your case I would recommend to use Power Query for merging data from all list into one table, because it more manageable and flexible way.

https://support.office.com/en-us/article/Append-queries-Power-Query-e42ca582-4f62-4a43-b37f-99e2b2a4813a?ui=en-US&rs=en-US&ad=US&fromAR=1

Best regards Artem Glazkov