Creating reports from SharePoint lists

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

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”

image

Enter the name of the SharePoint site you want to get your lists from.

image

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.

image

Click on “Load” and Power Query will run off and get your data.

image

image

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).

image

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”.

image

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.

References

https://support.office.com/en-us/article/Merge-queries-Power-Query-fd157620-5470-4c0f-b132-7ca2616d17f9

http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2014/10/08/excel-services-and-powerpivot-for-sharepoint-2013-power-query-connection-errors.aspx