Menu

Importing Data

Webinar Wednesday on Cloud-Based Dashboards

image

Today’s “post” is actually going to be a series of small updates/announcements.

Connection Cloud is one of our new partners at Pivotstream.  They do something amazing:  they make your cloud data sources like SalesForce and Facebook look like normal databases.

Which means PowerPivot can connect to those sources and pull data just as easily as you pull data from a database like SQL or Access:

Read the Rest

Combine Multiple Worksheets/Workbooks into a Single PowerPivot Table

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:

image  image  image

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:

Read the Rest

Monday Bonus: SalesForce Data Imported into PowerPivot Without Programming?

 
SalesForce.com data loaded into PowerPivot (No Special Skills Required)

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

Simple import of Salesforce.com data into PowerPivot, just select and click

What’s the Trick?

Read the Rest

Excel 5-Calendar Date Table

Guest Post by Colin Banfield [LinkedIn]

image

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.

Read the Rest