How to deal with SharePoint (or MS Access) choice column, multi-value field (lookup field, person field) in Pivot or even PowerPivot?
How to split, expand, explode multi-value fields and use them in Excel reports?
Analyze SharePoint multiple values field in an Excel pivot table.
The solution must be automated, not manual execution of a script or macro.
The solution described in this post is meant to help users in a Personal BI, Self-Service BI environment.
In such environment they don’t have access to advanced tools like SQL server nor its components like SSIS or SSAS.
Multi-value field, Choice field, Person field with multi values, lookup field with multi-values once exposed in Excel, PowerPivot or any text editor look like this:
or even worst if it’s a lookup:
Person 1 ;#125;#Person 3;#68;#Person 4;#122
Unfortunately this kind of value can not be filtered, grouped and of course not used in Pivots.
Audience / Prerequisites
To understand what I’m talking about in this post you should know
- SharePoint List with choice column (or similar multivalues fields)
- Excel Pivot
- How to connect a SharePoint List in MS Access
- How to consume data from an MS Access Database in Excel
SharePoint List -> MS Access Linked Table -> Views -> Excel Pivot | PowerPivot
The concept is to not consume data directly from a SharePoint List in Excel or PowerPivot but to do it through an MS Access database.
In MS Access you will have a Linked Table to the SharePoint List you are trying to analyse.
You will need to set up some Views to expose properly your data.
The MS Access database file is stored in the same SharePoint space.
Once set up you don’t need to reopen the MS Access file, Data are automatically refreshed.
- Case 1: Excel Pivot (no PowerPivot Data Model)
- Case 2: PowerPivot Data Model
How a multi-value field look like in Excel?
How the same field looks like in MS Access?
Case 1: Excel Pivot (no PowerPivot Data Model)
MS Access is doing the split of multi-values automatically.
To be able to use your data you just need to create 2 Views per multi-value field.
- One to list the values
- One to add the extra info you may need in your Pivot (we are in Case 1).
Your View will look like this
The MS Access View to be used in Excel Pivot is using a relation like in this example (this extra step is only required in case you can not use the PowerPivot Data Model in Excel).
You have to add all the columns you may need in your Pivot.
Case 2: PowerPivot Data Model
If you can use the PowerPivot Data Model then it’s even more straight forward.
You only need to create a MS Access View for each Multi-value field you want to expose in your Pivots (see Case 1).
You will set the relation inside PowerPivot Data Model, like in this example:
Pros / Cons
- + MS Access file is hosted in SharePoint
- + Data refresh work (in Excel)
- + No coding
- + Fully automated
- + Works fine
- + No need of an SQL database
- + Concept is simple
- – Need MS Access
- – May need PowerPivot Data Model
- – if exposed in Excel Webpart auto refresh is not functional
- using an SQL database
- using macro to explode data record in Excel
- complex DAX statements that could do half of the work
Never miss an update by following us and subscribing to our monthly newsletter!