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 (12)

  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

  11. Greg says:

    So, can we assume that this feature will never be supported in SharePoint 2013?

    1. At the moment there are no (known) plans to include PowerQuery supported data refresh in any aspect of SharePoint 2013.

Skip to main content