Archive for the ‘SharePoint’ Category


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. Read the rest of this entry »