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:
SalesForce.com data loaded into PowerPivot (No Special Skills Required!)
This Whole Cloud Thing Just Might Catch On…
We live in pretty exciting times. Sometimes it’s simply amazing what I can do from my desk, without having to take off my Excel hat. All of these various technologies available to us in the cloud, plus PowerPivot’s ability to talk to them… the net impact really starts to add up sometimes.
All of the Tables Available, Just Select and Click Finish
I didn’t have to do anything “technical” to pull this off really, I just end up using the PowerPivot import wizard:
Simple import of Salesforce.com data into PowerPivot, just select and click
For some time, I have been looking around for a fairly complete date table in Excel for use with PowerPivot. If you are working with data derived from a data warehouse, a date table is perhaps the most common dimension table that exists in the warehouse. However, not every scenario involves working with a data warehouse directly, and I simply wanted a “portable” date table. I found very little online, the best perhaps being this Excel table offered by the Kimball group (the table has been expanded since I originally downloaded it). I could have modified the Kimball table for my particular needs, but I decided to create one from scratch. Late last year, Rob posted an article titled the Ultimate Date Table, which is available from the Azure Marketplace. I considered using this table instead of the one I was building in Excel, but the “Ultimate Table” lacks fiscal periods. Much of the analysis work I do includes fiscal periods.
“There are people out there whose jobs force them to be the place where two sources of data meet, and they are the ones who integrate and cross-reference that data to form conclusions… …I think a lot of the…
So cool… Chris Webb ALSO posted today about downloading weather data from DataMarket. And any post that starts with the words “I don’t always agree with what everything Rob Collie says” gets an immediate boost in credibility – very wise…
“And I feel, so much depends on the weather… So is it raining in your bedroom retail?” Example: 800 Days of Weather in New York City 820 Days of Weather Data from New York City, Pulled From DataMarket (Temps in…
How many discoveries are right under our noses, if only we cross-referenced the right data sets? Convergence of Multiple “Thought Streams” Yeah, I love quoting movies. And tv shows. And song lyrics. But it’s not the quoting that I enjoy…
Johnny take a tour of your data, and tune……up your columns and your rows, to free up some roomYou’ve been following some rules, with the former VertipaqNow you’re seeing some results, that you don’t understand(Click image for the original…