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 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 »


Updates from July 4th / World Cup Week

July 2, 2014

A Summer State of Mind

image

Let’s Call This “Recharge Mode.”

After an INSANE couple of months, we’ve been taking it this week at PowerPivotPro HQ.  July Fourth holiday week, World Cup games on TV, a relatively quiet email inbox, lots of physical therapy for the still-ailing knee…  all adds up to “no new posts this week.”

But I do have a few quick updates:

August Power Pivot Class, Book Bundle, Spanish Book

Power Pivot Training Course   Power Pivot Training Course

 

Our July class here in Cleveland completely sold out – demand exceeded our supply of seats.  That’s a good sign for Power Pivot awareness and adoption, for sure, and

So we’re going to do another class in August.  Details here.

Power Pivot Book Bundle

Reader Jim Fleming wrote me and pointed out this bundle.  Get all three for $27 (eBook versions – web delivery) or $54.95 (print AND eBook versions combined – US shipments only)

Power Pivot Book en Espanol!

Last, but NOT Least – Miguel Escobar’s Translation of “DFPP,” Right Here in My Hand.
Ultimately, No Country or Language Should be “Safe” from the Benevolent Virus Called Power Pivot.


Compare product performance after launch

June 26, 2014

By Avichal Singh

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 Model
Typical view available in BI, but not very insightful

Power View Graph Monthly Sales by Car Model

 

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

Power View Graph Cumulative Sales since launch by Car Model

Read the rest of this entry »


We Have a “Crush” on Verblike Reports

June 24, 2014

image

Even Very “Sophisticated” Reports/Dashboards are Often “Couch Potatoes” in Practice
(They Sit There and Expect You to Do All the Work)

Intro From Rob

I’ve been meaning to blog this for a long time – it is, after all, one of my absolute FAVORITE things to talk about.  Now, Scott beats me to it.  But he does such a good job below that I don’t have much to add, except a few graphics here and there, like the one above.

Besides, how can I not love a blog post that starts off talking about how right I am? Smile

Guest Post by Scott at Tiny Lizard

By nature, I am a rather skeptical person.  When I first hear an idea, I generally think it is probably wrong.  This isn’t one of my finer traits and  I am sure it drives Rob insane, since he has a rather impressive track record of being correct.  Thankfully, this blog entry is not about one of the times I assumed Rob was wrong…

It is actually the complete opposite.  It is one of those times when Rob told me something and it just instantly SUPER resonated with me and got stuck in me.   I suspect it will do the same for most of you.

Nouns and Verbs

imageIf you were fortunate enough to hear Rob speak recently at PASS Business Analytics Conference, you have already heard this idea.

It is super typical for us, as report authors, to generate The Report.  The Noun.  We hand it off with pride.  And The Report has all the information somebody could possibly need to make a decision.  All of it.   Row after Row. Column after Column.  Unfortunately, even with the fanciest conditional formatting in the world, it is not clear… how is somebody supposed to look at The Report and actually… do something?

Because, at the end of the day (and yes, with a handful of exceptions like keeping the SEC happy), it is our hope that somebody looks at our reports and uses it to make a decision.  To do something.  To Verb.

Read the rest of this entry »


Please Review Alchemy :)

June 23, 2014

Click to leave a review of Power Pivot Alchemy on Amazon

Click to leave a review of Alchemy on Amazon

If you’ve read it, maybe give it a quick review on Amazon?

My original book DAX Formulas for PowerPivot has 62 reviews on Amazon and I have read every one of them.  So I have a great idea of what people think of that book, and that is VERY helpful, thank you everyone who took the time.

So far though, Alchemy only has two reviews – one was written before it was even released, and the other erroneously assumed something about our color choices, so neither is particularly “informative” to us, feedback-wise.

If you’ve read Alchemy and can spare a few minutes, please drop in over at Amazon and let us know what you think of it ok?  Thanks everyone!


Why I am a “late adopter” of virtually everything

June 20, 2014

Rant On.  100% ON.

I’m writing this post on a Lenovo Yoga 2 laptop that will be returned to Best Buy by the time you have read this.

Nothing but a nightmare.  Starts off with one of those improvements that we all love so much:

image

3200 Lines of Resolution, WOW!  So Why do I have it set at 1600???

Read the rest of this entry »


Experiments in Linear Regression, Part 2

June 19, 2014

 
<< Back to Part 1

Download this workbook Here

Slope of a Linear Regression Line - Now We Just Need to Translate That into Power Pivot

The Formula for the Slope of a Linear Regression Line.  It’s Greek to Me.
(Get it?  Greek?  Sigh.  Anyway, click the image to view the article on StatisticsHowTo.com)

In Case of Emergency, Call JT Statmaster!

imageI struggle mightily to understand formulas expressed as Greek symbols.  I don’t know why really.  Probably because it seems so abstract – that notation sacrifices “humanity” in order to achieve precision and uniformity.  I get that, but it doesn’t make it easy for me.

Fortunately, we have people like JT Joyner.  JT was a student in one of my classes late last year.  And I dare say he was one of those “star pupils.”  He took to Power Pivot like a natural.  So natural, in fact, that a couple days after the class, he emailed me a Linear Regression workbook example, implemented in Power Pivot measures.  He translated Greek into, you know, formulas.

Six months later, yeah, I am just getting around to doing something with it.  But this is exciting stuff for sure.  I’m pumped.  Let’s dig in.

Read the rest of this entry »