How to Get It

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?

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:

An Excel Pro gets ahold of Power Pivot and builds something amazing.

They show that around the office, and everyone is blown away.

They are then asked to produce MORE amazing stuff.

A few others get in on the act and also start producing amazing insights.

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.

Interested in Learning How to Do this Kind of Thing?

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.

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

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

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

Not ideal but you may be able to refresh the Excel with Power Query via SSIS. The process would be the business copy the Excel with PQ in a network shared folder. The SSIS scheduled will pick up all the Excel files inside the folder and do the refresh and save. The SSIS can upload the Excel file to SharePoint if necessary. Also refer to http://southbaydba.com/2013/09/10/part-5-power-query-api-refreshing-data-indeed/, there is some other ways. But yes, none of them are ideal and we should have a easy to use server to do the refresh jobs.

Rob, I agree that Power Query is powerful, and that there should be an easy way to use it as a data source for SSAS (SQL Server Analysis Services) Tabular. I have moved several Power Pivots to Tabular, but don’t see my organization paying the Power BI tax for many users.

I feel your pain Rob, and this is something i realised a very long time ago. My feeling is that is a deliberate move from Microsoft to force *cough* guide users to the cloud. Im guessing there is some resistance to baking this functionality into SharePoint internally as it means updates to both SQL Server and SharePoint components and new functionality is generally not released as part of a CU or Service Pack. There have been some exceptions but its generally around version compatibility e.g. SharePoint 2010 supporting the latest Power View or PowerView supporting SSAS mutlidimensional models

I am encountering a limit of 2,000 rows utilizing ‘From Salesforce Reports’. The report I am connecting to in SF has 6,331 rows. I know there is a display limit in SF of 2,000 rows, but is there a workaround to connect Powerquery to the full report of 6,331 rows?

Thanks for reporting the issue. Unfortunately this is a known limitation of the Salesforce API. Their website has the same limitation like you pointed out. Salesforce need to update their API for us achieve what you want.

One co-worker suggested you can work around this by rebuilding the report using the Salesforce Objects connector, which would allow Power Query to access all the rows.

Not sure how our IT is doing it in detail but we have a daily scheduled load of saleforece data into our datawarehouse. PowerPivot takes the data from DWH using the Sharepoint scheduled refresh function. So far this works great for us.

I’ve done this previously. There are quite a few commercial connectors to be able to connect to SF. You can easily use any data integration tool such as SSIS to schedule to pull the data from SF to the data warehouse. However, this is IT solution and business users have very limited control.

Power Query gives the user full control but you cannot auto refresh without Power BI cloud subscription. I guess a scheduled “ETL Refresh as a Services” as mentioned in my first reply would be able to solve the issue in a ugly way. But it is free if your company already has SSIS invested.

I installed the latest Power Query add in and wanted to also install the Salesforce Extension, but link no longer works. Did they take away this preview installation? Did they move it into Power Bi instead?

Thanks Tim. There was another update ( PowerQuery_2.23.4035.242 (64-bit) [en-us] ) – but still no SFDC extension.
Initially there was a separate install file for it, but they took it down. ( Salesforce_Extension_Preview_For_PowerQuery_2_16_3822_242 (64-bit) [en-us]_msi (1).47dz4p6 ).
Do you have Office ProPlus? My Excel Version is V15.0.4719.1002

Is there a way to use this amazing tool if my company has a SSO (single sign-on) in place? We have a private domain but it doesn’t work for this tool. It just logs in in a separate webpage and that is useless.

I get “Power Update” and “Power Query” mixed up all the time. Too many “Power” names to keep track of 🙂

My comment on “Trouble Looms Ahead” was specific to the fact that you couldn’t auto-refresh Power Query files at the time this article was written. You now can with Power Update.

As for you SSO problem, I personally haven’t encountered it, I’m curious and have some questions:
1. What data source are you trying to connect to?
2. What option are you choosing from the Power Query menu?
3. Are you using Active Directory SSO?

1. Well, I am trying to use Salesforce connection.
2. Since my company uses SSO the only way I see it to connect is to use the private domain we have. However, this is where either I or Power Query fails because eventually, it’s just opening my Salesforce in a separate webpage. Without any option to actually validate my credentials.
3. I am not sure about this. Sorry, I am new at this. 🙂

Check the last comment which I think might be what you’re looking for. If not, just reply to that discussion, letting them know that you have the same issue. Then you’ll get a notification when there’s an update, just like when you comment on this blog.

Now that there is Salesforce connector is available in Power BI desktop to get data, I am struggling to figure out a correct link to pass in order to fetch the objects. What link it needs? I do not have production link I have test link of Object Manager and also of the actual website. When I try to use any link in “Custom URL” it says invalid arguments provided.