Other (Better) Ways to Get All Measures As Text

October 25, 2012


The Second Time I’ve Used This “Egg on My Face” Picture

Feeling Silly

OK, on Tuesday I published a technique that I’ve been using for years now – a way to get a list of all measure formulas as text.  And it took about five minutes for people to respond and tell me about all the better ways to do it.

I’d like to say that none of those ways were better than mine.  But ALL of them were better Smile

Read the rest of this entry »

PowerPivotPro: The Book (Coming Soon)

September 11, 2012

***UPDATE:  Window Extended!

I think I can leave the “get your name in the book” offer open until the weekend.  See this post.

Where’s Rob?

You may have noticed a certain absence over the past couple weeks.  David Churchward has been on quite a run of guest posts, and that is no accident.  He’s been keeping things afloat here for me, as a HUGE favor, while I finish the book.

It was time

Yes, the book.  One that captures the essential lessons that I’ve informally recorded and scattered on the blog over the years in random order.  One that sequences all of those lessons properly, explains them more fully than the blog, and forces me to not cut corners.  One that captures the way I teach when I train people onsite.  Ideally, one that helps the Excel Pro in particular make the transition from Excel Pro to All-Out Data Monster.

I’ve been thinking about this forever.  Hey, it’s always sounded like a great idea.  But it’s one thing to think about it and another to actually do it.  I actually started over New Year’s this past January, but then shelved it as things got hectic at work.  Well…  August rolled around, things were STILL hectic at work, and then I noticed THIS on Amazon…  “rut roh Shaggy.”

WAIT!  If you want to pre-order, do it tomorrow, and I’ll put your name in the book.  See below for details.

WAIT!  If you want to pre-order, do it Wednesday, and I’ll put your name in the book Smile
(See below for details)

At the beginning of August, the release date was listed as September 1st.  It was quite the wakeup call Smile.

Plus, this is being published by Bill Jelen’s Holy Macro Press.  It’s a Mr. Excel title!  Bill has been fantastically helpful to me in this whole process, dating back to last calendar year.  Thou shalt not let down Mr. Excel!  So again, yeah, it was time.

I’ve been heads-down ever since, and the process is now happily in its final phases.  The book will be released Nov 10.

150 Pages?  Bah.

Yeah it’s listed at 150.  That was the estimate that I submitted nearly a year ago.  Then I dug in and quickly started realizing that I needed to exceed it. 

Read the rest of this entry »

Friday Bonus: PowerPivot “Owns” Excel Now!

April 13, 2012

I was recently sent a link to the best-selling books on Amazon, in the “Microsoft Spreadsheet” category.

Check it out:

The Top Two Best-Selling Excel Books Are Both PowerPivot Books!

The Top Two Best-Selling Excel Books Are Both PowerPivot Books!
(Click to Visit the Page on Amazon)

This Surprises Even Me

I’m a little surprised to see this.  This very much seems “correct” to me – PowerPivot books SHOULD be most popular, but to see this happening already is really cool.  I thought it might be another year before we saw something like this.

By the way, I’ve been checking all week, and those two books have held the top slots all week.  It updates every hour, and sometimes Mr. Excel’s book is tops, other times it’s the Italians (as above), but the top two haven’t changed.

VLOOKUP Week: Who Needs VLOOKUP Anymore?

March 27, 2012


VLOOKUP Week – Brought to You by Mr. Excel
(Click Image for the Official Site of VLOOKUP Week)

What is the “Scariest” Feature in Excel?

A couple of years ago at lunch, Bill remarked to me that VLOOKUP was the “scariest” feature in Excel, and that PowerPivot’s introduction of relationships was going to make Excel a lot “friendlier” to the average user.

This sparked a few minutes of friendly debate, as I had always considered pivots themselves to be the “scariest” feature in Excel.  To be “scary,” a feature must be very useful if you know how to use it, and yet 80% or so of the Excel audience doesn’t know how to use it.  VLOOKUP and Pivots both clearly meet those criteria, so it was an interesting discussion.

Should PowerPivot be Named SimplePivot?

In traditional pivots, VLOOKUP is often a required step to prepare your data before pivoting it (combining multiple tables into one).  So I remain hopeful that Bill is correct.  Wouldn’t it be ironic if PowerPivot ended up being a Simpler way to create pivots, and didn’t just dramatically increase the power of pivots, but also broadly expanded the audience that even uses pivots?

That would rock.  Is there anyone out there who can corroborate Bill’s theory?  Were you put off by VLOOKUP before, and now use pivots thanks to PowerPivot?  Let me know.

Anyway, time to do my part for VLOOKUP weeka full week of posts by Bill and the Excel community focused on that fearsome monster, VLOOKUP.

My take?  With PowerPivot, you literally do not need VLOOKUP.  Ever.

From the Archives #1:  Relationships as Alternative to VLOOKUP


That Looks a Lot Easier than VLOOKUP…  Because it IS

This article I wrote in CIMA Insight is probably the best intro I’ve written to relationships in PowerPivot:


From the Archives #2:  Using =RELATED() to Inspect Your Data

This post comes from all the way back in 2009 and is an example of me using a new function, RELATED(), that does exactly the same thing as VLOOKUP, but only takes one argument:


Note that once you are done with your =RELATED calc columns for inspection purposes, in most cases it makes more sense to then delete those columns and just use the columns from the other table in your pivots.

Full post here.

PowerPivot V2 is Released!

March 8, 2012

Well the wait is over.  It is now released, for real.  No longer a beta or RC.  The real deal.

Download it here.

Other important links:

  1. My first glimpse at new features here.
  2. Important warnings about compatibility with V1.

More Live PowerPivot Web Apps!

February 9, 2012

Excel Pro Turned Web Developer with a SINGLE CLICK!  MUHAHAHAHA!

“IT’S ALIVE!!!!”
(Just your average Excel Pro after converting
his first PowerPivot Workbook into a Web App)

I’ve got another article about to go live on CIMA Insight, but I’m gonna jump the gun a bit and post basically the entire thing here ahead of time.

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.

At Pivotstream we recently went live with our first full-time demo site for Hosted PowerPivot.  We’re going to be adding to it over time, but it’s got enough on it already that I think it’s worth looking at – it shows that “spreadsheets have become live web applications” thing that has to be seen, live, to really sink in.

Customizable Homepage

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Pretty self-explanatory – customizable content plus a menu of applications.  Link below.

(OK, one note:  I say “customizable” but customization is only allowed for Authors/Owners of the site.  What you are seeing here in the Consumer experience, and Consumers cannot customize this home page).

App #1: Based on Microsoft’s “AdventureWorks” Sample Data Set

This is the workbook from the Budget vs. Actuals Part One and Part Two posts. Here’s a picture of that same workbook after it’s been saved to a SharePoint web server and then accessed in my browser:

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Notes on this application:

  1. This is just an XLSX file, created with PowerPivot and then saved to the server
  2. But it’s rendering in my browser (Firefox in this case), and the XLSX is NOT being downloaded to my computer
  3. I do NOT have to have Excel installed on my computer in order for this browser application to function
  4. This picture is of the menu page of the app, which is just a worksheet with the gridlines and headers turned off
  5. The graphics are two image files inserted into the sheet via Insert.Picture on the ribbon
  6. The four hyperlinks are merely links to other worksheets within the workbook

If I click the “Sales vs. Budget” hyperlink, I am taken to the report I built in last month’s article:

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Click the slicer – it works Smile


Application #2: Retailer Overlap Competitive Analysis

This application is based on two real-world data sets. One is a list of the addresses of almost every retail food/drug store in the United States. The other is a detailed list of demographic information about every ZIP code (postal code) in the United States.

Blend them together in PowerPivot and you get an application like this:

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Note that I have selected the two warmest temperature ranges, circled in orange. If I click the link at the top of the sheet I then see the following analysis:

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple


  1. I have the application set as if “I” were Walgreens, and evaluating my competitors’ geographic overlap with my Walgreens retail locations
  2. The first column indicates to me that CVS competes with me (Walgreens) for 41.5% of the potential customers that I try to reach
  3. In other words, 41.5% of the people who live near a Walgreens also live near a CVS
  4. The second column reports that Walgreens competes with me for 47.3% of my customers in Warm locations
  5. So the first column ignores the slicer selections I made on the menu page, and the second column respects them!
  6. The third column represents the delta between columns 1 and 2. Interesting, for instance, that Rite Aid does NOT compete with me at all really in warm locations
  7. Try it out, slice away – there are many ways to slice and analyze this data set

Application #3: CRM Analyzer

This one is also based off of a sample data set, but it is one pulled from a popular CRM package and therefore represents real-world value.

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple

Note that there are multiple reports in this application as well, plus a menu page, but I’m just showing one here for space reasons.

Application #4: UFO Sightings

Many of you have seen this one already, on Mr. Excel’s Hosted PowerPivot site, but we put it on this demo site as well.

Hosted PowerPivot Demo - PowerPivot for SharePoint Made Simple


Downgrading From PowerPivot v2 to v1

February 6, 2012

OK, let’s say you already installed v2, whether inadvertently or…  advertently.

And now you need to downgrade to v1, so that your workbooks function on your server, or can be opened by other people running v1 on their desktops.

So… how to do that?

The Obvious Thing Works for Most People

Simple:  just uninstall v2 (from Windows Control Panel) and then install v1.

But if you see this error when you try to build a new workbook, or open an old one, well, it’s not working out:

An operation that uses the database driver could not be completed - seen after downgrading PowerPivot v2 to v1.

What to do if you get that error?

1) Open Start Menu

2) Find “Command Prompt” – it is under Accessories

3) Right Click on Command Prompt, choose Run as Administrator

4) Once in the cmd window (looks like the old DOS window), type the following:

regsvr32 “C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msolap100.dll”

You may need to correct for your actual folder location – that’s the location on my machine.  The quotes ARE required.

Microsoft’s PowerPivot Download Links Updated

February 6, 2012

My former colleagues at Microsoft have responded to the issue I raised last week and have updated the PowerPivot.com download page to include links to both the “beta” of v2 as well as the latest, trusted version of v1.

Still not perfect though, as the v2 link remains first Sad smile

And since not everyone knows that “2012 RC0” means “test version that won’t work with your servers, or anyone using v1” and that “2008 R2” means “version 1, the one you need for production environments,” well, this could still use some serious clarification. 

So let me make it 100% clear which link is good for what purpose:


First Link is a Test Version of V2, Second Link is Released Version of V1

See Also

Downgrading from V2 to V1

Warning: PowerPivot.com download is v2 beta!

February 3, 2012


“Danger, Will Robinson!”

I’m seeing this pop up a lot now, both in our hosting business and on forums.

If you have gone to PowerPivot.com lately and downloaded PowerPivot, you downloaded a beta (a release candidate) of PowerPivot v2, and probably didn’t know it.

That’s not a big deal except for:

  1. The release candidate likely isn’t as stable as the most up-to-date release of PowerPivot  v1
  2. Workbooks created in v2 CANNOT be opened by v1, and that includes v1 server!
  3. There is no way to “downgrade” a v2 workbook to v1, so if you want to use a v2 workbook on v1, you have to start over

So be careful – v2 is awesome but using it unknowingly may have side effects you didn’t expect, at least until v2 is official released and the rest of your organization upgrades.

I’ve notified MS of this problem but so far they haven’t fixed it.  Both v1 AND v2 should be available for download, with v1 the default, and both clearly marked.

If you want to download v1, here is the link:

V1 PowerPivot for Excel – Download Here

See Also

Downgrading from V2 to V1

Microsoft Fixes the Download Page…  Sorta

Mini Post #2: Mr. XL Launches PowerPivot Forum

January 26, 2012


Click Image to Visit the Forum

The other day I suggested to Bill Jelen that he might want to add a PowerPivot-dedicated forum on MrExcel.com.  I wasn’t sure what he would say, to be honest.  I mean, he didn’t have a dedicated forum for really anything else – there wasn’t one for VBA, wasn’t one for formulas, wasn’t one for charts.  Instead basically all of those are combined in one single forum.

I respect that approach, really.  If my question involves formulas, charts, and VBA, where would I put such a question?  If he’d segmented the forums like that, it may have fragmented the discussion and ultimately resulted in an ineffective forum.

So I was perfectly prepared for him to say “good idea Rob but I’d rather not introduce noise with a new forum.”  Grudgingly I would have been forced to accept that.  But I was hoping he’d bend the rules just a bit here and make an exception, since PowerPivot still IS very new to the Excel audience.

I was overthinking it.  He loved the idea.  And the forum is already off to a decent start.

So if you have a question, pop on over there and post it.  Also, feel free to answer questions as well – I’m pretty sure I’m going to fall behind soon.

Oh, and be kind to newcomers.  One of the benefits of this new forum is that the legions of Excel pros who frequent MrExcel.com will undoubtedly notice all the activity and wonder what this whole “PowerPivot” thing is all about.

Here’s the link again:


Mini Post #1: List of all 300+ posts

January 26, 2012


“I have detailed files.”

A few quick mini-posts today.  First up, I was recently asked if there was a way to see the title of every post and its URL all in one place for reference.  And the answer is…  no, but there should be.

Next week I hope to add a page to the site that provides that sort of index.  In the meantime, here’s a really rough export of all 300+ post titles and URL’s.  It’s not perfect by any stretch but you may find it useful in the meantime.

It is, of course, in Excel format Smile

Click here to download the list of all
300+ posts and their URL’s

The Ultimate Date Table

November 15, 2011


“Looks like it’s time for me to get myself a date.”

-Ace Ventura, PowerPivot Detective

The Importance of a Date/Calendar Table

I get a lot of questions from people who are struggling with the time intelligence functions in DAX.  And nine times out of ten, the answer is that they don’t have a proper date table.

I know it’s tempting.  You’ve got your sales table, and hey, there’s a Date column in there!  So you use it, and pass that column as a parameter to, say, DATESBETWEEN, or DATEADD.

Sometimes that will give you an error.  And other times, it won’t…  but the results will be funky.

You need a separate Dates table, or perhaps you prefer to call it a Calendar table.  A separate table, whose only purpose is to store dates (and the properties of dates, like DayOfWeek, etc.)  And it contains consecutive dates – no “gaps.”  Even if your business is never open on weekends, you need unbroken ranges of dates.

Oh, and then you need to relate it to your Sales table.  (Or whatever fact/measure tables you have).

Much More Than a Single Column

A single-column table that contains merely dates is enough to make the time intelligence DAX functions operate smoothly.  But you will almost certainly want other fields too.  Like Year.  MonthName.  DayOfWeek.  The list goes on.

Maybe something like this:


And yes, you can cobble this together on your own in Excel.  Tedious work though.

Would You Like One for Free?  Try DateStream from Boyan Penev!

Imagine just being able to open up PowerPivot and always having three nice date tables awaiting import:


That’s what Boyan Penev has put together for you.  Three great calendar tables that you can download directly into PowerPivot, for free.

He published them to Azure DataMarket, a service from Microsoft where data providers can actually sell you their data sets – things like weather, demographics, etc.

Boyan did this for free though – I suspect half as a service to the community, and half as a project to learn how to provide a service on DataMarket.

It’s pretty damn cool, and really, the story should end there.  If you’ve used DataMarket before, then it DOES end there.  Go get the date tables and try them out.

But if this is your first exposure to DataMarket, it takes a few minutes to get it set up.  It’s not bad as long as you don’t make the mistakes I did.

How To Get It – Short Version

Hey, it’s on Azure DataMarket.  The URL is in the next section below, or you can just go to Azure DataMarket and search on “DateStream.”

DataMarket is going to be a wonderful service someday, but right now it has a few warts, so there is a Long Version too.

How To Get It – Long Version with Occasional Snarky Commentary

Step 1:  Go to the DateStream page on DataMarket.


Step 2:  Get confused.  OK, now is where things get choppy, because frankly, the DataMarket site itself has a terrible user interface.  I sent a full page of feedback to the DataMarket team about a month ago and as far as I can tell, they ignored it.  (Which is pure karma – I used to be one of the people at MS who ignored 90% of the feedback coming in, and now I get to be the one who is ignored).

I don’t want this to be a tutorial on how to navigate their website, or even how NOT to design a website.  So let’s just hit the highlights and try to get to Boyan’s date tables as soon as we can.

Step 3:  Get an account.  OK, this isn’t bad.  Another MS site that requires a Live ID.  Most of us have three of those by now.

Step 4a:  Scan the DateStream page looking for the “Download to PowerPivot” button or link.

Yeah that’s right.  There is no such link – you can get to one by navigating a few levels deeper but I’m going to skip that.  Don’t despair though, good things await you!

Step 4:  Find the URL of the DateStream Service

This is NOT the same as the URL of the DateStream page.  But it IS displayed on the page.  Here’s the URL you need:


OK, copy that.  You will need it.

Step 5:  Launch PowerPivot, Go Into the PowerPivot Window

And click this button:



If you don’t have that button, you need a newer version of PowerPivot.  Go get that from PowerPivot.com and resume the next step.

Step 6:  Fill in the Dataset URL From Step 5


Step 7:  Account Key

See that last text box in the picture above?  The one with the long code in it that I’ve partly blurred?  That’s my account key.  I highly recommend clicking that Find button.  It’s actually pretty damn useful.

Be careful – the DataMarket site has TWO long nasty codes like that for you.  One of them is the one you want, and the Find button takes you to that one:


THIS is Your Account Key

Do NOT, under any circumstances, do what I did, and confuse Account Key with Customer ID:



This is NOT Your Account Key.
Do NOT Be Tempted to Use This!

Step 8:  Click Next, and Pick Your Table or Tables


NOW we are on familiar ground.

Last Note:  Parameterization?

One thing I have not yet figured out is how to limit the date range I import.  The table starts in the year 1900, which goes back a bit far for my needs, and makes the dataset take a long time to download.

You’ll notice that when importing from DataMarket, the Preview and Filter UI lacks the filter dropdown buttons:


No Filter Dropdowns, Just Checkboxes

But the DateStream homepage DOES indicated that parameterization is possible:


So if you’ve got that figured out, drop me a note Smile