I received an email recently with details of a customer that had a concern moving from SharePoint 2010 to SharePoint Online. In their SharePoint on-premises they had a number of SharePoint lists and they were creating Excel-based reports off these. The concern was they would not be able to do the same in SharePoint Online.
Fortunately the answer is that this can easily be achieved as lists expose themselves as OData feeds which we can connect to using Excel. I’ll walk you through how I achieved this using Excel 2013 and PowerPivot. We’re going to be working with two lists. The first list is a projects list:
And the second list contains project documents:
Eagle-eyed readers will notice that the data is related through the Project Code column. This is a primary/foreign key one-to-many relationship. One project has many project documents.
The requirement is to provide a report showing the “Average Days Between Approved and Returned Date” and the “Average Days Between Approved and Submitted Date” for each project. We’re going to have to join up the list data and create some calculated columns.
Create the Excel document and PowerView
We’ll start by opening Excel and loading the list data using the OData Data feed. This can be found on the Data tab under “From Other Sources”
If you are familiar with PowerQuery and PowerPivot you might be thinking “why isnt’ he using the PowerQuery OData Data feed or the OData Data Feed source in PowerPivot?”. Firstly, the PowerPivot OData Data feed won’t connect to a SharePoint Online OData feed, so that is easy to answer. Secondly, if we could use the PowerQuery OData Data feed it would be super as we could join the data in our two lists as part of the PowerQuery mash up. In fact, I would recommend you do this for simplicity if you don’t want to refresh your report (without using the Data Management Gateway in Power BI for Office 365). At the time of writing the only permitted data sources for PowerQuery are accessed through the Data Management Gateway.
In the data feed dialog you’ll need to enter the location/URL of the data feed. This will be in the following format
https://<path to your SharePoint>/_vti_bin/ListData.svc
You may need to enter credentials at this point after which you’ll need to select tables. I’m going to select my Projects and ProjectDocuments tables:
I accept the defaults in the Save Data Connection File step and click “Finish”.
In the Import Data dialog I’m going to view the data in a table and add the data to the Data Model. Normally I’d select “Only Create Connection” to avoid duplicating data by having it in my worksheet and in the PowerPivot model – I want to show you something later on so I’ll add to both.
When I click OK two worksheets and tables are created; one for Projects and one for Project Documents.
Great, so now I have my data in Excel. I need to create a relationship between the tables so I open up PowerPivot. You should see a PowerPivot tab in Excel – if not you need to enable PowerPivot (if you’re using Excel 2013 or download it if you’re using Excel 2010 –search Bing for how to do this or leave a comment below and I’ll write a blog on how to do it).
Click “Manage” then open the diagram view:
I create a relationship between the Project and Project Documents tables by dragging the Project Code from one table to the other:
PowerPivot knows which way round to add the relationship based on the underlying data.
Next we need to add some calculated columns in order to calculate some of the metrics for the report. Switching back to the data view and in the ProjectDocuments table I’m going to add the “Days Between Approved and Submitted” as =1*[ApprovedDate]-[SubmittedDate] and the “Days Between Approved and Returned” as =IF(ISBLANK([ApprovedDate]) || ISBLANK([ReturnedDate]), BLANK(), 1*[ApprovedDate]-[ReturnedDate])
PowerPivot uses the DAX (Data Analysis Expressions) language to build formulae and expressions in case you’re not familiar with it.
Save the PowerPivot model and then you’ll return to Excel.
Finally we insert a PowerView sheet into our Excel document and create a table with the Project, “Days Between Approved and Submitted” and “Days Between Approved and Returned”. Since we added data in both tables and PowerPivot you’ll see the worksheet tables and PowerPivot tables appear in the fields picker:
We need to use the PowerPivot fields which are the ones not prefixed by Table_. When the columns are added you should see a table looking like the following:
As you can see, PowerView has summed the Days Between… columns. We need the average so let’s change this to average in the fields:
Now we have exactly what we need:
Publishing to SharePoint Online and Refreshing Data
The next step is to upload the document to a SharePoint Online document library. Here’s my document and data appearing in Excel Online:
At this stage you might try adding a new project to your list add seeing if you can refresh the data in Excel Online.
When you do this you’ll notice that the worksheet-based tables update and show the new list content but the PowerView table doesn’t update! Surely the worksheet tables feed PowerPivot that feeds the PowerView right? It seems that when you first open an Excel document in Excel Online the PowerPivot model is refreshed but there is no mechanism to refresh it after the document has been opened.
We can work around this though. Opening the document again in Excel you need to go to the Data tab, click Connections and look at the properties of the connection to SharePoint Online.
Check the “Refresh data when opening the file option”, save and upload the document to SharePoint, replacing the original one. When you open the document in Excel Online it will automatically refresh and show the latest list data.