Protiviti / SharePoint Blog

SharePoint Blog

February 20
How to Analyze SharePoint 2013 Data Using PowerBI

This is Part 1 of a three part series on How to Analyze SharePoint 2013 Data Using PowerBI. This series is aimed towards Business Users with a working knowledge of PowerPivot.​

In Part 1 I will show you how to connect your SharePoint lists to Excel. Connecting a list to Excel can be used for anything from ad-hoc reporting to creating dashboards using the PowerPivot Gallery. In subsequent posts, I will show you how to connect multiple lists together using the Data Model as well as some of the advanced features of PowerPivot.

Creating the Connection

Exporting SharePoint data is easy, after navigating to the SharePoint list you would like to export you are just a few button clicks away.

1. Select or create the view you would like to export. Only the columns in the current view will be exported to Excel, so be sure you have all the columns you need.

2. Navigate to ‘List’ in the Ribbon.

3. Select ‘Export to Excel’


4. Select ‘Open’ in the dialog box.

At this point Excel should open and you may see a security notice. As you know the source of this data, you can safely select ‘Enable’

The list will now appear in Excel.
Note: If you already have Excel open, you may see an ‘Import Data’ wizard. For simplicity, select ‘Table’ and ‘New Workbook’ and the list will appear in Excel.

The list will be inserted as a Table with a non-descriptive name; you can change this the same way you would change any other table name under the ‘Design’ tab in Excel.

Now, you may be thinking ‘this is handy but my SharePoint list is constantly adding new items, do I have to export it every time?’ Excel offers two ways to refresh connected SharePoint data.

Refreshing the Connection

Option 1: On Demand Refresh

Simply press ‘Refresh’ in the ‘Design’ tab.


That is it - your table will now have the most up-to-date information from SharePoint

Option 2: Refresh on File Open

Excel also gives you the option to Refresh data when you open the Workbook. To enable this option select ‘Connection Properties’ in the extended menu under ‘Refresh’ in the ‘Design Tab’


From there select ‘Refresh data when opening the file’ and you will get your up-to-date SharePoint data every time you open the file.


You have now connected your SharePoint list to excel and can analyze your data using the all the familiar Excel and PowerPivot tools. The next post will discuss how to connect multiple SharePoint lists together using the PowerPivot Data Model thus allowing for analytics across multiple lists.


Quick Launch

© Protiviti 2020. All rights reserved.   |   Privacy Policy