Has Power Pivot Changed Your Life? I Want to Know!

August 19, 2014

This face obviously represents happy.

We Want to Hear Your Cool Stories!

Unprompted Confirmation

In last week’s post I explained why I now “kick off” my training sessions with the bold promise of “Power Pivot will change not only your career, but also your LIFE.”  (Please go back and read that, briefly, if you have not – because this post won’t make as much sense otherwise).

Well, a funny thing has been happening lately – I’ve been getting emails from people saying that it HAS changed their life.  Literally in those words.

Are these people just being nice to me, confirming my bold statement?

Nope, because none of them has heard me say it before – these are people I have never met in person!

In fact, this pattern of emails was the reason why I got started writing last week’s post.  I actually intended to JUST write THIS post, the “has it changed your life” post, but along the way I realized I needed to add the background, so this got split into part two.

Please Share Your Stories!

If you are feeling like this, please leave a commentIf you’ve experienced life change as a result of Power Pivot, please leave a comment at the end of this post!

Or if you’re a bit shy about that, drop me an email.  My address isn’t hard to find or guess.

Take it Away, Rob’s Inbox!

imageThese are real emails from real readers, slightly edited to maintain their privacy.  In all cases, I am bolding the “life changing” portion of the quote:

Read the rest of this entry »


Power Pivot: a revolution in slow motion

August 14, 2014

By Avichal Singh

The first time you truly experience Power Pivot – not a demo, not some random public data set or someone else’s data – your own business data all lit up using Power Pivot. It is a transformative experience. And you know there is no turning back.

I remember that moment, I remember thinking this is HUGE, this gets BI in the hands of people who really need it. Power Pivot is surely is going to explode.

Sadly, it does not feel like that is happening :-(

As I have become more involved with the Modern Excel User Group (also on LinkedIn), I have realized that there are way too many people in buckets a) and b) below and not nearly enough in c).

a) Sad smile Unaware. Have not heard of Power Pivot or Power BI
b) Disappointed smile Aware but have not truly experienced the capabilities of Power Pivot
c) Red heart In Love with Power Pivot

Note: Are there stages between b) and c)? Between experiencing Power Pivot and falling in love? Perhaps. But guided by the right hands that path should be really short :-)

Unaware: Don’t know what they are missing

For the unaware group, I do my part; talking to anyone who would lend me an ear, about Power Pivot and Power BI at any event or gathering. But overall we would need to trust the higher powers :-) within Microsoft with that task. However I will make the offerings below to the powers that be:

- Power Pivot should be a free add-in for all versions of Excel 2013, just the way it is for Excel 2010. I feel it even makes good business sense for Microsoft. Power Pivot has the potential to attract a large user base and would solidly anchor Office and Office 365 in the rapidly changing world. These users can then be up sold services like Power BI.

- Power Pivot add-in should be easily discoverable. Both Power View and Power Map have buttons as part of the standard Excel ribbon, which can be used to easily activate these tools. Not so for Power Pivot.
It takes 8 mouse clicks to activate the add-in so you can launch Power Pivot.
File > Options > Add-Ins > Click Dropdown > COM Add-Ins > Go > Select ‘Microsoft Office Power Pivot for Excel 2013’ > Ok
As the first born in the Power BI suite, it is time for Power Pivot to claim it’s rightful place on the standard Excel ribbon and it would fit perfectly under the Data tab.

Currently in Excel 2013 Desired in Excel 2013
Currently in Excel 2013: Powe Map, Power View Desired in Excel 2013: Power Pivot or Data Model

Note: There was a shift in Excel 2013 to weave in Power Pivot so closely with the tool (and call it Data Model) that potentially many users would not even need to learn or use the Power Pivot window. IMHO, that is just keeping people away from all the goodness and richness of Power Pivot. Let them come, let them explore and experience.

Aware but not in Love: Mind the Gap!

While I may feel helpless in making a dent in the Unaware group, I feel impassioned and even obligated to help the Aware but inexperienced group.

I have spoken to many Excel users and noticed that there is a gap between knowing about Power Pivot and actually trying it out yourself to experience its true power. There is a step there that many users simply cannot take. Being on the other side, it baffles me a bit. I feel like yelling “Hey, come on over, the grass IS greener on the other side!”

From Excel to Power Pivot: Mind the gap!

Read the rest of this entry »


Vending Machine or Kitchen: Two Kinds of Self-Service

August 12, 2014

Preface:  Breaking the Consulting “Rulebook”

Power Pivot Allows Us to Set a Much Higher Bar - and Then Clear It

This may seem bold:  Starting about six months ago, whenever I find myself in a room with spreadsheet/business/database people, one of the first things I tell them is that Power Pivot won’t just change their work, it will change their LIVES.  That’s right – I tell them, with a straight face, that this business technology will actually make them happier.

That violates one of the unwritten rules of consulting, which is to underpromise and overdeliver – set expectations low enough that no one ever has reason to complain or be disappointed.

I understand why it’s traditionally a good practice to “set the bar low.”  I get it.  I truly do.

“Consumer with Choices” vs. “Empowered Producer” is a BIG Difference

But Power Pivot is different.  Yes, in the truly breakthrough, transformational sense – you knew I was going to say that.  But different in another critical sense as well – it requires (and incents!) the analyst types on the Business side to participate to a degree unlike with any other BI tool.

No matter how they are marketed, every other BI tool I have ever seen treats the business user as a glorified Consumer.  Yes, Business Objects and Cognos for sure, but I’m also looking at you, Tableau and Qlikview and Spotfire.

Read the rest of this entry »


Filters CAN Flow Up Hill – Via Formulas That Is

August 7, 2014

By Matt Allington

Intro from Rob

imageMany of you already know of Matt Allington and his background as BI Director for Coca-Cola Asia Pacific.  Matt recently flew around the world (literally) to attend my training course with the purpose of becoming accredited by me as the official PowerPivotPro University Trainer for Australia (we even have a picture to prove it – displayed at right).

Now that Matt has my approval to teach my material, I have invited Matt to be a regular blogger on PowerPivotPro.com.  This is Matt’s first post in this capacity, so over to you Matt.

Confessions of a DAX student

Matt here:  I want to share with you a simple mistake I made early on in my DAX journey, and also create awareness of how easy it is to fall into a similar trap. This post will explain the mistake and provide the solution to how you can get filters to flow up hill – via formulae that is.

If you learnt DAX the Rob Collie way (like I did) you would be very familiar with Rob’s best practice of placing the lookup tables above the data tables in the PowerPivot Diagram View.

schema

The reason Rob teaches it this way is because it is very easy for the reader to visualise the flow of the Filter Context.

Read the rest of this entry »


A Neat Trick/Macro for More Readable Pivots

August 5, 2014

 
OK, you’ve been a good Power Pivot author and given your measures clearly descriptive names.

Your punishment is spending all day looking at pivots like this:

Your Pivot is Too Wide for the Screen

Hey, Where’s the Rest of My Information?
(Hint:  It’s in “Scrollsville.”)

Nice and Cleanly Readable Pivot

MUCH Better:  Last Two Measures Completely Visible, With Space to Spare!
(Assuming Vertical Space Isn’t a Problem, Of Course)

A Trick I “Harvested” From a Client

Awhile back I was working with a gentleman named Tom Phelan who repeatedly used a series of click mouse clicks to achieve the sort of layout pictured above.  After seeing him do that about ten times I asked him to slow down so I could see what the clicks were.

Read the rest of this entry »


Toggling Between Different Units via Slicer?

July 29, 2014

Is this possible?

Someone at Microsoft asked me this question the other day:

“Sort of like how you’ve used a slicer for conditional formatting, is it possible to use a slicer to change the custom formatting of a number?  In my use case, I want to be able to display currency as either full number ($1,500,000.00) or abbreviated ($1.5M) as the viewer wishes.  See below for an example of the desire.”

Use a Slicer to Change Number Formatting from Raw to Millions/Thousands M/K?

Can We Do This in Power Pivot?

My Answer:  No, not possible.  Wait, maybe.  Hmm.  OK, yes, mostly.

All of these thoughts flashed before my eyes:

  1. Power Pivot measures/calc fields must always have a consistent data type.  You can’t have a measure return numbers sometimes and text other times, for instance.  All “exits” from an IF or a SWITCH must have the same data type.
  2. Apparently, #1 is no longer true in SSAS Tabular, in the 2014 release.  They now support “variant” data type measures. 
  3. But no, Power Pivot still lacks that “variant” measure capability, at least for now.
  4. Whoa, hold on a second.  The desired result above does NOT use different data types!  It’s all numbers!  So we just need to change the math!
  5. Oh, ouch, not so fast.  The “M” and the “K” – I don’t know how to add those labels in a numerical data type.

So this means…  text measures!

Read the rest of this entry »


Next Week’s Sold Out Class: One Spot Re-Opens

July 25, 2014

 
Hi folks.  Next week’s 2-day class (Wed-Thu, 7/30-7/31) taught by me (Rob) has been sold out for awhile now, but we had a last minute cancelation.

So if you live in the Cleveland area, or feel like some last-minute travel, drop us a note at empower@powerpivotpro.com

If you ARE considering the trip, you won’t be the only one traveling in, btw.  Of next week’s class, 16 of 17 students are coming from out of town.  Maybe you want to be lucky number 17 of 18.

We’re also doing a social outing/dinner for the class on Wednesday night.  The famous Matt Allington will be joining us from Down Under.

Note that we’re doing another class in August as well, so if you’re not inclined to join us on short notice, there’s another option as well.


FrankenSpark! (Cube Formulas Meet Sparklines)

July 22, 2014

Cube Formulas in Power Pivot Combined with Sparklines:  AKA FrankenSpark

That’s a Single Spreadsheet Cell with a CUBEVALUE Formula AND a Sparkline in It!

I was working with a client last week when a question occurred to me:

“Can I put a Sparkline in a cell that already has a Cube Formula in it?”
”Oh cool, it worked!” (Cackles Maniacally)

-me, last week

Anyway, we were off and running at that point:

 
Cube Formulas in Power Pivot Combined with Sparklines and a Chart

“FrankenSpark” Used as Part of a Larger (Redacted and Obfuscated) Client Scorecard
(Yes, the Colors Still Need Some Work)

The How-To

Read the rest of this entry »


Adding tables to a Power Pivot model from VBA (in Excel 2013)

July 17, 2014

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:

clip_image001

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 of this entry »


The Simple Art of Being Prepared

July 15, 2014

Saluting a Mentor

image

Heikki Kanerva, Right.
(At left is Juha Niemisto, a Programmer from the Excel Team and Mutual Friend)

Taking a bit of a break today from “technical” posts and doing that whole “professional observations slash storytelling” thing.

My first real mentor at Microsoft was a guy named Heikki Kanerva.  I learned many, many things from that man, but the way in which he cared about his people, and PROTECTED his people, will always eclipse even those things.  I wouldn’t have had the time or the space to learn had I not lived in the Heikki-created “safe zone” for those crucial early years of 1997-1998.

And the charisma.  The leadership.  You know those fantasy stories about barbarian clans in the frozen wastelands of the North, where the clans have perpetually been at war with each other, but now a New and Evil Threat looms from the South, and the clans must set aside their pride and biases and band together, or face certain extermination?

In those stories, there’s always a charismatic leader who emerges to unite said clans, the Strong Leader who possesses brains, brawn, and charisma in just the right proportions.

And Heikki was pretty much the real-life embodiment of that character, in every way.  He played that role every day for the entire Office organization, and I have never seen anything like his powers since.

A Mentor’s Parting Lesson

Years later, when I no longer worked for Heikki, he passed away suddenly under tragic circumstances – a story that I may someday tell here, but not the focus for today.

But leave it to Heikki to still manage to teach AFTER departing this world.

Read the rest of this entry »


“I Modified an Existing Table in Power Query and Now it Won’t Refresh”– A Fix

July 10, 2014

Guest Post by Ted Eichinger

Note, this fix to re-establish a broken connection is performed using Excel 2010

It’s the same old story, I mashed and twisted some data through Power Query, pulled it through Power Pivot, spent hours creating calculated columns and measures, made a really nice Pivot Table with conditional formatting and all the bells and whistles.  Then I show it to the Business Manager that requested it, and he wants me to add a field that I didn’t pull in through Power Query.  I’m going to have to change my Power Query which is going to break my Power Pivot connection, and then I’ll be spending hours to rebuild my model from scratch!

This happens to me more that I’d like.  I’ve searched the far reaches of Google, only to find a lot of threads asking about how to fix, but I’ve never seen an answer.  Most threads end with no,  you just can’t do that.  Just playing around on a hunch, I figured out a way to fix it without having to rebuild the model!!!  Some already know the “fix”, for those that don’t , I’ll walk through the method that’s been working for me.

If you want to follow along download the file below, I’ve used some public data on camera lenses so anyone can refresh the data model (use Anonymous access if prompted), or follow along just using the pictures.
Yes You Can Example.xlsx

In this example a Business Manager wants me to add the field, “Closest Focus” to the Power Pivot table.

Power Pivot Rejected by Business Manager

This is the Power Pivot table that requires an additional field

This should be easy, just open the Power Query (in the example file) named, “Macro photography lenses”, and delete the “RemovedColumns” step and the “Closest Focus” column should now be back.

 Power Query reveal column

“Closest Focus”, has now been added.

Save that Power Query.  Open the Power Pivot window, there is only one table, let’s refresh it.

Power Pivot Error After Editing Power Query

The dreaded Power Pivot error!

This will always happen anytime we make any kind of change to a Power Query that is connected to a Power Pivot table.  If you’re working with Excel Power BI tools you’ll eventually see this error.  The error detail reads:

The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.

More Details:

OLE DB or ODBC error: The query ‘Macro photography lenses’ or one of its inputs was modified in Power Query after this connection was added. Please remove and re-add the connection. This can be done by disabling and re-enabling download of ‘Macro photography lenses’ in Power Query..

The FIX!!

Read the rest of this entry »


You’re “Poisson” Running Through My Veins: A Truly Epic Guest Post on Call Centers and Erlang C

July 8, 2014

Alice Cooper has Poisson Running Through His Veins, DAX-Style

“I Want to Taste You But Your Lips are Venomous, PWAH-SAAHHHNNN!!!!”
(Get It?  Poisson/Poison?  OK, Read on for a Bell Biv Devoe Reference)

Intro from Rob

Um, wow.  A few things:

  1. Brace yourselves for a dose of awesome.
  2. I don’t understand everything that’s happening in this guest post.
  3. So if you “get” all of this, fantastic. 
  4. If you don’t, don’t sweat it – just bask in the power of our toolet – it can truly do anything.
  5. Our new friend Josh is absolutely killing it with his song references.

Take it Away, Josh…

Since taking on a role in Work Force Management about a year ago, I’ve learned one thing: Staffing a call center is expensive. What I mean is: the staffing software, it’s is rather pricey. So much so, that smaller call centers just can’t afford the tools needed to easily create an accurate staffing model.

But as someone raised to the mantra of: “if you are going to do something, do it right” I decided to learn me some DAX. (To be fair though, what my dad really said was: “Aim low, that way no one can tell when you fail.” But for the sake of this post, we’ll go with the first quote. )

Luckily, Rob was nice enough to teach us the core of using complex equations in his Experiments in Linear Regressing, Parts 1 & 2. So we won’t be entirely lost in new territory, it’ll be more of a: “lost with friends and colleagues, ‘Danger Will Robinson’” sort of excursion.

Using RankX and SumX to create a weighted moving average

The staffing model I use relies on a weighted average of the 4 most recent weeks of incoming calls. Often times however, a week’s data may have been inaccurate, causing us to go a week further back.

The way a weighted average works is that each number is multiplied by the given weight and then divided by the the sum of all weights. So the weights 40, 30, 20, and 10 are assigned to the weeks, giving us an average number of calls that is more heavily influenced by the most recent week.

Moving weighted average in PowerPivot

The wrong way to do this:

I include it here because the interactions between the eight weight measures are really, really neat to watch.

Read the rest of this entry »