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).
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.