So back in 2015 when I was trying to write a book, I took time out to rant that despite every version of Excel since 2013 having the Data Model baked in as standard, it wasn’t a heck of a lot of use to you in the event that:
You wanted to mash together say a UnitPrice from one Table with a SalesQuantity from another, in order to display the derived Total Revenue in a PivotTable; and
You were a tightwad that didn’t happen to have one of the premium PowerPivot SKUs installed.
Because while you could create a relationship between two Tables without PowerPivot:
…the option to create a calculated field to multiply Qty against Price was greyed out.
Disheartened with the lack of functionality in the non-Premium-Excel space (and sternly talked-to by my wife about the need to shave and go out earn a crust) I flushed the nearly-finished manuscript down the toilet in disgust. But now that I’m flush again, I’ve dusted off that manuscript (it wasn’t really down the toilet) and am updating it, so that I can nearly-finish it all over again.
Anyways, I was subsequently looking at this DataModel thing again, and rewriting the bit that said how sucky it is that you need to pay a premium in order to do a pretty standard thing, when I came across this:
Wow…It IS possible. Tight-wad analysts of the world: You too can do PowerPivoty stuff, without paying a cent more. You heard it hear first. Or second, rather.
Thanks, Mike Girvin, from the bottom of my wallet. :-)
Look Ma…no PowerPivot:
I wonder what limitations this PowerPivot back door has, if any?
I found this from Chris Webb’s blog: PowerPivot has been integrated into Excel, kind of. This means that the xVelocity (aka Vertipaq) engine is now native to Excel, and you can do all the basic PowerPivot stuff like loading vast amounts of data from multiple data sources and querying it via PivotTables directly in Excel, without installing any extra addins. PowerPivot does still exist as an optional extra however: you need it if you want to use the more advanced functionality that exists in PowerPivot today, such as filtering data before import, using diagram view, defining hierarchies and perspectives and so on.
And further down the page there’s some great commentary between Chris and Colin Banflied, including this from Colin:
xVelocity is divorced from PowerPivot, and built into Excel
PowerPivot is now simply a UI to administer the data model.
I’d always thought that while any edition of Office 2013/2016 can interact with a PowerPivot Excel Workbook (open it, click on slicers, change the pivot table etc.), if you wanted to “author” the Model you would need the correct version of Office, which includes the Power Pivot authoring functionality i.e. the PowerPivot window, which lets you define relationships/measures etc. But to some degree it appears not. Which has got me wondering…to what extent is PowerPivot just a UI for the DataModel baked in to every model of Excel? Sure, it can help you filter data before import, but so can PowerQuery, which is free in every version of Excel 2013 or later. Meaning there’s probably nothing stopping a savvy Excel VBA developer from simply rolling their own UI to help users of any post 2010 version of Excel do 99% of what they’d likely otherwise need PowerPivot for.
Join us in Amsterdam to learn how to Excel from our Experts (all MVPs): Jon Peltier, Bob Umlas, Roger Govier, Henk Vlootman, Oz du Soleil, Tony de Jonker, Jan Karel Pieterse.
Attend this comprehensive training event and you will:
Improve your Power Query skills
Learn how to Customize the ribbon for your workbooks and add-ins.
Get insight how to Build Excel models based on ranges and positions.
Understand how to create involved Array Formulas
Get advice on Best practices in Power pivot.
See how to use VBA to customize charts
Receive Tips & Tricks, documentation and lots of valuable files
The Excel Charting And Dashboard Masterclass
May 27th 2016
The Amsterdam Excel summit also features a post-conference training. Attend this one-day masterclass and:
Excel MVP and charting Guru Jon Peltier teaches you how to visualize your numerical information in the most effective way.
Excel MVP and financial expert at najlepsze strony bukmacherskieTony de Jonker and communication & visualization expert David Hoppe unveil the secrets of creating powerful and flexible dashboards.