One of the most powerful features (and there are lots) that I’ve found about Power Query is the ability to hold an entire table in a single cell. Once I’ve got a table in a cell, then I can start…
Intro How did I come to PowerPivot and the related tools? Desperation in trying to fix a process problem. How did I get to the point where I can return a reasonable answer to a data query using the Power…
Back on May 18, Reid posted a great post on how you should set up your Power Queries using referencing. It’s a technique that I’ve been preaching on my blog for years, and is something that we teach in the…
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…
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…
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…
***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…