Protiviti / SharePoint Blog

SharePoint Blog

March 06
How to Analyze Your SharePoint Data Using Power BI: Part 2

This is Part 2 of a three part series on How to Analyze your SharePoint Data Using Power BI. This series is aimed towards Business Users with a working knowledge of PowerPivot. If you missed Part 1 you can view it here: Exporting SharePoint Data to Excel.

The previous lesson showed you how to connect one SharePoint List to Excel, but SharePoint is full of lists, which usually have lookups to other lists, creating a web of interconnected data. The question then arises: ‘How can I analyze this data?’ You have exported the data to excel yet there is no pre-set relationship allowing you analyze the data with the same connections that exist across different lists. This is where PowerPivot and the Data Model comes in handy.

The Data Model within PowerPivot for Excel allows you to connect columns with a ‘one to one’ or a ‘one to many’ relationship, much like in a database. These connections can then be utilized when creating Pivot Tables just as you normally would on a single table.

If you do not have PowerPivot activated in your copy of Excel, you can follow the links below for information on how to activate the Add-In:

Office 2010 Users

Office 2013 Users

Adding a Table to the Data Model

A quick note before we begin. In the previous post I had you import your SharePoint list into a new workbook. However, when working with the Data Model it is easier to have all the tables in the same workbook. The process will be the same but when you are adding subsequent Lists to Excel select ‘New worksheet’ so all the data is in the same Workbook.

1. Click Anywhere within the Table you would like to add to the Data Model

2. Navigate to PowerPivot in the Ribbon and selected ‘Add to Data Model’​


Upon clicking ‘Add to Data Model’, the table is added to the data model and a new window will appear that looks like this: 


Repeat these steps for the other tables you would like to add to the data model. To get back to the Excel view you can click the Excel Icon next to the save button or just minimize the window. To navigate back to Data Model view click on ‘Manage’ in the PowerPivot tab.

Creating Relationships

Now that you have added all your tables to the Data Model, it is time to create relationships between the columns. This can be done through the Design tab on the Ribbon or the Diagram View.

Option 1: Design Tab

1. Navigate to ‘Create Relationship’ on the Design Tab’


2. Using the ‘Create Connection’ wizard select the Column from each table you would like to connect and press create.

To view active relationships navigate to ‘Manage Relationships’ in the Design tab on the Ribbon

Mattl36155.PNGOption 2: Diagram View

Personally, I find the Diagram View to be an easier way to visualize the relationships between the tables. To get to the Diagram View:

From the Home tab on the Ribbon click on ‘Diagram View’

Diagram View.PNG
The Diagram View will display each table and its associated columns, as well as any relationships that have been created. As you can see below, I have a connection between the two employee columns that I created through the ‘Create Relationship’ wizard earlier.mattl36156.PNGTo create a relationship in this view you simply have to drag and drop the columns you would like to create a relationship for, as illustrated below. 


If you would like to delete a connection simply highly the arrow and press delete.

Creating PivotTable from the Data Model

Back in the Excel Workbook navigate to the ‘Insert’ tab and select PivotTable

1. Select ‘Use an External Data Source’

2. Click the ‘Choose Connection…’ button

3. Navigate to the ‘Tables’ tab of the ‘Existing Connections’ window

4. Select ‘Tables in Workbook Data Model’ and press open.

5. Back in the ‘Create PivotTable’ wizard press ok.

You are now able to create PivotTables and charts as you would normally with data from a single table.


You now know how to set the groundwork to bring powerful analytics to your SharePoint data. In the final part of this three part series, I will show you some of the advanced features of PowerPivot to bring your analytics to the next level. 

Quick Launch

© Protiviti 2020. All rights reserved.   |   Privacy Policy