PowerPivot for SharePoint 2013 – Data Refresh with Power Query error (UPDATED)


You may see a “Call to Excel Services failed” error when trying to perform a scheduled data refresh using PowerPivot for SharePoint on a workbook in SharePoint 2013 that has a Power Query connection to a data source. At this time, Power Query is not supported by SharePoint 2013 on-premise. Both data refresh in the browser and scheduled data refresh will give an error if the workbook has a data connection created using Power Query. Data refresh for Power Query connections is supported by Power BI for Office 365 (SharePoint Online).

http://blogs.msdn.com/b/powerbi/archive/2014/05/02/scheduled-data-refresh-for-power-query.aspx

——–

UPDATE (Dec. 2016)

Interactive browser refresh and scheduled data refresh for Power Query connections is now supported on-premise with PowerPivot for SharePoint 2016. It has the following minimum product requirements:

  • SQL Server 2016 PowerPivot Analysis Services with Cumulative Update 1
  • PowerPivot For SharePoint 2016 add-on
  • Office Online Server November 2016 release
  • SharePoint Server 2016 RTM

Note that the interactive data refresh will not work with Windows authentication. It requires a Secure Store ID to be configured to use for the refresh.

Also, data that is loaded from a Power Query connection needs to be added to the data model. Otherwise, the refresh will fail as PowerPivot is required in this refresh process, and only workbooks with a data model are processed by PowerPivot.

——–

As a workaround, you can connect to the data source without using Power Query by creating a data connection using PowerPivot. To determine whether or not the workbook currently has a Power Query data connection, look at the connection string in Excel by going to Data > Connections > Properties >  Definition. If the Provider is Microsoft.Mashup.OleDb.1, this indicates that the data connection was made using Power Query.

 


Comments (10)

  1. sunny says:

    Thanks for the details

  2. THAT SUCKS! When will on-prem support be available?

  3. ugh says:

    this Microsoft BI story really really sucks

  4. Mindy says:

    Any ETA when will be available to do this?

  5. At the moment we have no confirmation on a timeframe of when this will be available. Technically PowerQuery is not supported with SharePoint on-premise in any way, not just scheduled data refresh.

  6. peteM says:

    Thanks for the comment RickA. It would be nice to find this info on the Power Query web site. The only place I could find that mentioned this is not even a blog post.. but a reply to a blog post. Crossing my fingers this will be supported at some point.
    PowerQuery should be supported for SharePoint on-premise.

  7. Omar Sultan says:

    Does this apply to a Manual Data Refresh as well ?

  8. anton says:

    Any updates on this? This would solve so many problems for me..

  9. Choulho Shin says:

    yeah, desperately wonder if any upcoming 2013 CU will address this limitation.

  10. Inn VNix Ginner says:

    I personally did not like this article but thanks you for your time

Skip to main content