One of those simple but indispensable tricks
Back to a “real” post now after all the book stuff, but it’s going to be a short one while I get back on my feet.
Let’s say you have multiple worksheets (or workbooks) that all contain the same sort of data:
Multiple Worksheets (or Workbooks), All Contain The Same Type of Data
You Want to Combine ALL of Them Into a Single PowerPivot Table
These worksheets all come to you separately, but really you just want them as one big table.
Naturally, if it’s a small number of sheets, and each sheet isn’t massive, you can just copy paste them all into one table in Excel, then copy/paste into PowerPivot, or link the table into PowerPivot, or export as CSV so you can import it.
And you could also use Paste Append to directly paste into PowerPivot.
But if the combined data set exceeds 1 million rows, you won’t be able to combine the sheets into one – you will exceed the worksheet row limit. And a data set of that size is not something you can paste into PowerPivot directly with Paste Append – pasting large data sets into PowerPivot takes forever, if it completes at all.
Here’s what I do when I find myself in this position: