***Update: check out Scott Senkeresty’s review of Power Update over on Tiny Lizard.

***Update #2: a Free Version of Power Update is now available. More info here.

***Update #3: There is now a forum for Power Update questions, located here.

A brand-new software utility designed from the ground up as
a “Companion” to Power Pivot, Power Query, and the entire Power BI stack.Definitely Click on the Image for Larger Version – Surprises Lurk Therein

Do Any of These Sound Familiar?

Power Update Helps With ALL of These (And a Few More, Too)

“What IS It?”

As part of the microsoft.com/learning team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.

I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.

Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.

View 1: Monthly Sales by Car ModelTypical view available in BI, but not very insightful

View 2: Cumulative Sales since Launch, by Car ModelClear view into adoption ramp of various products

Intro from Rob: Never fear, last week’s series is still slated for completion, and in a special way. Watch this space on Thursday for some fireworks. For now, please enjoy Avi’s thoughts on the new forecasting component of Power View / Power BI.

PASS Business Analytics conference saw the announcement of a pretty cool Power View feature: Forecasting. I felt lucky to have been there and also to have had the opportunity to attend both of Rob Collie’s sessions (Data Revolution, Industrial Strength Excel). The Data Revolution session, I must say, was unlike anything I expected. No DAX formulas, no bullet points; just a path to data nirvana

The Power View forecasting feature was cool enough that I just had to play with it! I wanted to try it out with a few real world data sets. I ended up using Climate Data and Stock Market performance.

– First a quick look at the Power View forecasting functionality
– Then I show you how I built the files using Power Query (The more I use that tool the more I like it)

You can find the link to the finished Excel file here. You can also watch me walk through the whole process in the video below:

[youtube http://www.youtube.com/watch?v=-rqQBJFMofw&hl=en&hd=1]

Video Walkthrough: Forecasting in Power View and Power BI

Power View Forecasting in a Nutshell

In the ‘cloud first’ spirit that Microsoft has been following, the forecasting feature is only available in the online Power BI site (See microsoft.com/powerbi for more and to sign up for a free trial). To enable the forecasting feature, after opening your file on the Power BI site, you need to switch to the HTML5 mode by clicking on the icon at bottom right.

Click on this icon to enable the HTML5 mode with forecasting functionality

Power Pivot is the Engine that Turns Data Into Information!But We Can’t Understand This Properly Without Examining the Three Big Lies of Data

Goal: Answer Four Frequently-Asked Questions

So many things to say this week. Let’s jump in. Here are the questions I ultimately aim to answer, which are questions I get basically everywhere I go:

How do all of the Power BI Components relate to each other? Power Pivot, Power Query, Power View, Power Map, Q and A, etc. = Power Confusion for some folks. I get it.

Has Power Pivot become less important, now that we have all of these other new “Power *” tools?

Which tool should I learn first in the Power BI family?

Should I consider abandoning this stuff altogether in favor of <hot new technology X>? Tableau, Hadoop, R, etc.

In order to answer these, first we must confront some insidious lies that we are told every day.

Examining: The Three Big Lies of Data

The world of data, today, is clouded by Three Big Lies. These lies originate with all of the tools vendors – Oracle, IBM, Tableau, etc., and yes, Microsoft too is very much playing along.

Even though the Vendors are the Purveyors of these lies,they are NOT “at fault” for them. Because the world actually WANTS to be told these lies. BADLY wants to be told them, in fact. And because the audience is so receptive to these lies, the vendors naturally learn to tell them, and tell them well.

Vendors who DON’T learn to tell these lies? Well, those vendors don’t win many customers. And then those vendors disappear.

So while the lies COME from the vendors, the PROBLEM, really, is with US – the people who BUY the tools.

***BONUS:In addition to getting your name printed in the book, ALL pre-orders from MrExcel.com will include IMMEDIATE access to the “rough cuts” version of Alchemy in PDF form. Think of this as the 99% complete version of the book, a “final beta” of sorts. You can start reading next week, and then receive the final version when it’s ready in a few weeks. (Immediate access to the PDF is included with pre-orders of the physical book OR eBook).

About 160 People Got Their Names Printed in the First Book, and Seemed to Really Enjoy It.Time to Do That Again for My Long-Delayed New Book, Alchemy.

The long tug of war draws to a close…

Yes folks, it’s basically done. For over a year now, Bill and I have taken turns playing the roles of “Busy Guy Who Keeps Putting it Off” and “Impatient Guy Who Wonders Why the Other Guy Keeps Dragging His Feet.”

For the record, it looks like the game is ending with me holding the hot potato. Bill will forever remind me that I was the last hold up, I know this.

Order Tuesday April 1st Between12 and 1 PM US Eastern Time

Pre-order the book on MrExcel.com during that 1-hour window and we will include your name in the book before it goes to the printer! (Yes we still have a narrow window for changes).

You read about my Power Pivot journey in my first blog post and in my subsequent blog post I elaborated on migration to Analysis Services Tabular Model (SSAS Tabular). I realize now though, that I did things out of order and need to address that in some way. As my journey outlined, before we switched over to SSAS Tabular, we moved our Power Pivot workbooks to SharePoint and started using Power View Reports. And Power View has been a key element of our success. For this post I’ll go back to the future and speak about

– Our success with Power View

– All the settings in Power Pivot related to Power View

p.s.: When I refer to Power View I am referring to Power View on SharePoint. I am not referring to Power View functionality built in to Excel 2013, since that is a fairly different experience than Power View on SharePoint.

Power View Success Story

I love Power View, except when I don’t. It can feel limiting at times and frustrating, especially to an excel user (which is all of us ). After demonstrating a really slick Power View report with all the bells and whistles (check out a sample from Microsoft BI at Power View Demo. Mine don’t look as good as this), the first question I often get from the user is, “Great, now how can I export this to Excel?” And my answer is – you can’t “Export to Excel is the third most common button in data/BI apps…after Ok and Cancel” (click for a real fun post!), and Power View does not have it. Yet! If the powers that be are reading, I think it’s feasible that an icon appears when you hover over Power View report elements, to export the underlying data in excel in a simple table format. Please consider that for the next release. Now that I am in begging mode might as well ask for – ability to re-label measures/column names in Power View Report and show numbers as Percentage of Total (like in Excel Pivots). The latter is doable using DAX but not easily so.

***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…

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:

The genesis of this post is an email I’ve been meaning to send to my contacts at Microsoft – one I’ve been thinking about writing for at least a year. But I also figure it’s the sort of thing you folks might find interesting, and I really don’t have time these days to write the same “opus” twice, so here goes – two birds with one stone. And it’s a friendly stone.

Has there ever been a tool as flexible as Excel?

Let’s take a moment and just marvel at Excel’s “range.” (VBA macro programmers – yes the joke is intentional).

But those are just the outliers really – the novelties. The truly valuable examples are much less dramatic and happen hundreds of thousands of times every day. I’ll give some examples in the next section.

Feature A Was NEVER “Intended” to Be Used With Feature B!

“Hey You Got Your Slicer in My Conditional Formatting!”
(And then the whole jar fell into a bucket of DAX)