Summary
This post covers creating custom reports from SharePoint lists in Excel by joining SharePoint lists together. It specifically addresses the issues with People Picker and Lookup columns returning the row ID rather than the value.
Pros
- All point and click, no SQL query knowledge required.
- Easy to define which columns you want and amend them when the list changes rather than be constrained by the columns in the view as with the Export to Excel function from SharePoint
- Easy to join multiple SharePoint lists together, especially useful for when you have lookup and people picker columns which only return a row ID under most circumstances.
- Rename and reorder columns which persists between data refreshes.
Cons
- Since the data connection is provided by Power Query you can’t to automatic refreshes in Excel Web App, you have to launch full Excel.
- An additional piece of software that a workstation needs to update the spread sheet.
Prerequisites
- Excel 2013
- Power Query (download here)
Pulling your lists into Excel
Ensure that you have enabled the Power Query add-on in Excel.
Launch Excel and click on the “Power Query” tab.
Under the “Get External Data” tab, click on “From Other Sources” > “From SharePoint List”
Enter the name of the SharePoint site you want to get your lists from.
Check the box next to your list and the UserInformationList.
The UserInformationList is important so that you can resolve your site usernames from the Row ID’s which you’ll see if you use the people picker.
You should also check any other lists that you want to pull data from if you’re lookup columns from other lists.
Click on “Load” and Power Query will run off and get your data.
Under the “Combine” section of Power Query tab, click on “Merge”.
In your first table, click on column with the People Picker values (you’ll just see a Row ID number).
In the second section, select the “UserInformationList” and the ID Column then click on OK.
In the main Power Query window, click on the Expand column for the new column you’ve just created from the merged data and in the drop down list of fields, select “Name”.
Repeat this process for any other lists you have that might have dropdown or lookup values that you need to include.
You can then load your data into an Excel sheet and create pivot charts.