Post by Rob Collie

SalesForce Data Into Power Pivot / Power BI? YES!

This is what it looks like when Microsoft Does Something Epically Awesome…
…But Dark Clouds Loom – Read on to Learn Why

We LOVE This!

Seriously, we do.  It’s AMAZING.  Multiple of our clients are going to jump all over this.  It’s going to change their culture – AGAIN.

If Power Query can connect to it, that lets you pull SalesForce data directly into Power Pivot.  No more export, save, import/paste/etc.

SalesForce Data Into Power Pivot / Power View? YES!

Power Query Can Import SalesForce Data Directly Into Power Pivot, Which Means You Can Then Visualize in Power View, For Instance

So, the primary point of today’s post is to make you aware of this new capability, and tell you how to get it.

How to Get It

  1. Make sure you have the latest version of Power Query downloaded and installed.
  2. Download and install the additional “connector” for SalesForce:  32-bit here or alternatively 64-bit here.

Optional:  Read the Official MS Blog Post, Watch Their Video


Click to Visit the Power BI Team’s Blog

The Problem:  No Auto Refresh Support (Except Power BI Online)

Seriously, Power Query is our favorite thing from Microsoft other than Power Pivot (and Excel) itself.  It’s a game changer.  And this new connectivity to SalesForce solves a massively widespread problem.

But Power Query has an Achilles Heel:  it only supports scheduled autorefresh in Power BI Online.  Microsoft has not released a server version of Power Query that you can install in your “on premises” (or even private cloud) environment.

That’s right:  if you are running your own Power Pivot for SharePoint (or Tabular SSAS) server, workbooks/models that utilize Power Query are a DEAD END for Auto Refresh.

That’s a pretty powerful DIS-incentive against using Power Query.  Most of our clients are either already utilizing AutoRefresh on the server, or planning to do so in the short term, and as a result, we caution them against using Power Query, period.

Which is a TERRIBLE, TERRIBLE SHAME.  Power Query is stinking amazing.  It’s heartbreaking every time we have to recommend against using it.

So, A Poison Pill for Microsoft Revenues?

Power Query Won't Refresh on the Server, Except in Power BI Online

We wonder whether Microsoft has adequately considered another likely consequence of all this.  This might turn out to be an expensive mistake that costs Microsoft a lot of licensing revenue.

Consider that this is how a Power Pivot “infection’’ typically proceeds:

  1. An Excel Pro gets ahold of Power Pivot and builds something amazing.
  2. They show that around the office, and everyone is blown away.
  3. They are then asked to produce MORE amazing stuff.
  4. A few others get in on the act and also start producing amazing insights.
  5. But now there is a sharing problem.  How do I get these awesome insights onto everyone’s desktops and devices?  It’s a very difficult problem – lots of software installs, file size issues, etc.
  6. The organization then realizes that it’s time for a server.  A YouTube for workbooks, if you will.

See where this is headed?

Interested in Learning How to Do this Kind of Thing?

power query

Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.

CLICK HERE to claim your spot.

SalesForce Drives PQ Adoption.  PQ Adoption Drives You into a Wall.

This new SalesForce capability is brilliant – it will probably drive a massive uptick in usage of Power Query.

As a result, thousands of organizations will develop portfolios of models/workbooks that depend on Power Query.  They will become ENTRENCHED in Power Query, in other words.

That entrenchment will happen, in most cases, PRIOR to the realization of “a server would be nice.”

And one of the big selling points of that server will be effectively eliminated.  No autorefresh will translate into “no sale.”

You could argue that this will therefore drive Power BI Online adoption, but I don’t buy it.  Power BI Online is either a good fit for your org or it isn’t, and so far we are finding few orgs where it’s a good fit.  (Over time, Power BI Online will hopefully evolve and become a good fit for an ever-increasing percentage of the population).

Blessing in Disguise?

I’m actually hoping that I’m right about this being a big problem for Microsoft.  Runaway Power Query adoption would force MS to actually fill this ridiculous gap, and release a PQ server for on-prem and private cloud deployments.

PQ is just too damn awesome for it to remain pigeonholed like it is today.  The MS beast has many competing priorities, and it’s often hard for those priorities to get the attention they need until there’s a crisis.

I think they may now, in fact, be sowing the seeds of precisely that crisis.

So please, everyone, go install and start using the SalesForce import capability today Smile

For more on Power BI Online and the Power BI Family of products, see What is Power BI?