This was an issue that occurred when working specifically with Excel 2016 under the following conditions:
- Create a connection to a datasource using Excel's typicall method of connecting to a datasource.
- Create a Pivot Table or Pivot Chart
- Drag a date field to one of the Area's section (i.e. Rows, Columns, Values, Fiter, Axis) to add it to the Pivot.
- When the date field is dropped into the area section, Excel will break it into groups (Year, Quarter, Month) automatically.
- Add additional fields as needed
- Save the Workbook by uploading it to Office 365
- In Office365, locate the workbook you uploaded and open it using the browser. This will cause the new workbook to open in Excel Services.
- In the top menu, click Data and refresh the data connection.
- The workbook will fail to refresh with an error "We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh: This workbookDataModel.
What was happening is when Excel 2016 was breaking the date field into Year, Quarter, Month groups automatically, it was naming them something like TimeByDay (Month), etc. See below
What we had to do was rename the fields to TimeByDayMonth, etc. (see below) Once we did and re-uploaded the reports they would then render in Excel Services with no error.
Thanks! if you have any questions or comments feel free to leave them below.
-Dan
