Adding tables to a Power Pivot model from VBA (in Excel 2013)

Guest Post by Dany Hoter

After I published a post about manipulating relationships, Rob suggested that I take a step back and cover the entire scope of what’s possible with the object model.

Can you build a model from scratch? Can you add a new table to an existing table? Can you add calculated columns? What about calculated measures? , Can you change a connection for an existing table in the model?

The short answer to these questions is Yes, Yes, No, No, Yes

The longer version is the rest of this post. Everything in this post is NOT possible in Excel 2010 – this stuff works in 2013 only.

The object model consists of the following elements:


The only property that I found useful in this list is ModelRelationships collection which I used extensively in the previous post.

The ModelTables collection looks promising as it contains ModelTableColumns and could be the way to introduce new tables, new columns or even measures into the model. Unfortunately all these collections are read-only and cannot be used for adding to the model.

So how is still possible to add new tables or even to start a model from scratch?

It all has to do with the method add2 of the Connections collection.

Read the Rest

TopN as viewed by DAX Table Queries

Guest post by Scott Senkeresty


Get it?  “Median?”  SO Funny!


Rob is on-site with a client this week, so the reins, mic, baton or other appropriate metaphor gets handed to me today.  We get to today’s topic by means of a discussion on calculating a median in DAX:

Scott:  That sounds easy.  Just use TopN to grab half the numbers in ascending order, then another TopN against descending values to grab the final value(s)  (Glossing over odd vs even number of data rows)
Rob: What are you going to do about ties?
Scott:  <Blank Stare>

It turns out that calculating a median in DAX is pretty tricky.  Rob contends that sneaky street fighting tactics are required to deal with ties…where in my heart of hearts, I believe an elegant solution exists.  Hopefully we can get to the bottom of that in a future blog post.

Read the Rest

The Dramatic Impact of the 2013 SKU Decisions

Impact of the Power Pivot not included in all versions of Office 2013 decision, visualized dramatically.

This is a Pretty Dramatic Dip and Recovery.  What Else Could Explain It?  I’m all ears.

No DAX Required

Nothing fancy here, just a chart of page views of a very specific page on this site – the What is PowerPivot? page.  I use that page as an indicator of new interest – most people viewing it are “early” in their Power Pivot journeys. 

That dip starting in February is incredible.  What else could explain it other than the decision to remove Power Pivot from most “flavors” of 2013?  Eight months later, after Power Pivot re-appeared in Excel 2013 Standalone, we’re back to the same “slope” of the line that we had in February.  Maybe a little better.

Of course, Power Pivot usage was STILL growing, a LOT, during the dip.  And in fact growing by a lot more than the same months in 2012.  It’s just that the RATE of growth fell during those months.  The faucet of new users was still flowing, and flowing fast.  It was just “turned down” from full speed for a few months.

But imagine where we’d be WITHOUT this 6-8 month dip in growth rate.  The curve leading up to February looks awfully exponential doesn’t it?  Let’s take a look…

Read the Rest

Update on Power Pivot 2013 Outside the USA

 OK, the haze is clearing a bit.  The news is mostly good but this whole situation never should have existed anyway. 1) People outside the USA *are* succeeding with Excel 2013 Standalone – it IS giving them Power Pivot, although in some cases it is requiring a reinstall before it shows up. 2) Amazon USA won’t sell to non-US customers.  OK, no biggie, if you’re outside the USA, you can get it direct from MS (link below). US Customers can get…

Read the Rest

Verified: Yes, Excel 2013 Standalone Now Includes Power Pivot. (For real, normal people have it.)

  If You Install Excel 2013 Standalone, and Update to Version 1511, You Will Have Power Pivot I got confirmation yesterday, both from Microsoft and from a reader, that this week’s patch update from Microsoft fixed the problem that’s been lingering for about six weeks. 1) So, if you have Office 2013, but it’s one of the versions that lacks Power Pivot, you can buy a downloadable version of Excel 2013 Standalone (available here from Amazon), install it, and Excel…

Read the Rest

***UPDATE: Excel 2013 Standalone Will NOT Add Power Pivot/View Until September 10, 2013

 ***Update Oct 11, 2013:  I’ve been given the “all clear” by Microsoft and from readers that as of this week, Excel 2013 Standalone DOES include and successfully install Power Pivot! See this post for details. *** After a few readers of last week’s post reported that installing Standalone did NOT add Power Pivot and Power View, we followed up with my contacts at Microsoft. Microsoft quickly tracked down a problem in the upgrade logic, and it will be fixed in…

Read the Rest

Power Pivot, Power View Now Available in 2013!


Update Oct 11, 2013:  I’ve been given the “all clear” by Microsoft and from readers that as of this week, Excel 2013 Standalone DOES include and successfully install Power Pivot!

See this post for details.


…and there was much rejoicing

Well folks the wait is over.  Microsoft now offers a way for us to get Power Pivot at home in 2013!

It’s not quite “free” like it used to be (and still is) in 2010, but it’s not super expensive either, and the mere fact that we CAN buy it through retail channels is a big deal.  All in all, I call this a Good Thing.  It is most welcome.

All you need is love.  Oh, and Excel Standalone.

So, if you have Office 2013 already installed and you want to add Power Pivot, you just buy Excel Standalone and install THAT over the top, and you get Power Pivot and Power View.

If you have don’t have 2013 installed and have no desire to get all of the other apps, hey, you can just get Excel Standalone and forgo the rest I guess Winking smile

Power Pivot Inside:  A 2013 Option At Last             Power Pivot Inside:  A 2013 Option At Last

Amazon Purchasing Options:  $99 Download (Left), $79 “Non-Commercial” Version (Right)
Click Images for their respective product pages
HOLD OFF UNTIL SEPTEMBER 10th – see the update at the top of this post

(Note that both of those image links are affiliate links – if you purchase through those, I get a few dollars and you pay nothing extra.  Support the site, that sort of thing.)

“Non-Commercial” Version?

Yeah what the heck IS that?  From a trusted Microsoft source, we have this definition:

Read the Rest

PowerPivot vs. Power View: What’s the Difference?

I’ve been getting this question a lot lately:  How does Power View relate to PowerPivot?  Is PV a replacement for PP?   (And why does PV have a space in it while PP does not?)

(For more information on the various “Power *” offerings from Microsoft, see also the newer posts What is Power BI? and The Three Big Lies of Data).

First:  Understand that PowerPivot is Kinda Two Things

Let’s rewind all the way back to Office 2010, a world in which PV does not exist.  (For most of you, we call this time Today.  And for those stuck on 2007 or 2003, you may refer to this as Tomorrow.  Or maybe even the Day After Tomorrow).

In that world, which is where this blog largely lives, it’s helpful to reflect that PowerPivot has two parts:  the PowerPivot window, and the Excel window.  They have the following relationship:

PowerPivot's Relationship to Excel


Read the Rest
  • 1
  • 2