Today I am going to spend some time trying to convince (and demonstrate to) Excel Power Pivot users that it is easy and valuable to learn some simple SQL code. SQL is one of the easiest languages I have ever taught myself and you can do it too. The reasons SQL is easy to learn include: It has a very simple syntax that uses English language expressions. You can start with simple short queries and get more complex only when…
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.
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.
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…
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…
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…
***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…
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
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.)
Yeah what the heck IS that? From a trusted Microsoft source, we have this definition: