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