Power Query: It’s Good to Share(Point) Part 7

Welcome to our Power Query blog.  This week, I look at extracting data from a SharePoint list.

In this series, I am considering how to access data on SharePoint.  SharePoint is used by many companies to store organised data which may then be accessed and shared throughout the organisation.  I started with a single file and then moved on to consider combining data in a SharePoint folder. 

This time, instead of a folder, I am looking at a SharePoint list:

Along with the flattering pictures, the salespeople are responsible for creating expense files and these are linked as attachments.  I would like to extract a table with the attached expenses and the responsible salesperson.  Regular readers will not be surprised to discover that the expense files are CSV files, I’m sure!

I start in my Excel workbook, where I access the option I need from the ‘From Other Sources’ tab of the ‘Get Data’ dropdown:

In this series on SharePoint data, it’s no surprise to see that we need to enter the URL of the SharePoint site:

As we discovered in Part 4, this is:

https://sumproduct0.sharepoint.com/sites/SumProductTeam

I enter this and click ‘OK’.  If necessary, I follow the same process to log in, and choose to Connect:

I could select multiple lists if I wish, but I only need ‘Power Query List’:

There are numerous columns I don’t need.  I choose to ‘Transform Data’:

I have the Source step and the Navigation step that I would expect, but the third generated step is ‘Renamed Columns’.  This behaviour appears to be prompted by this particular source type.  Let’s start by looking at the Source step:

I use the URL and the Sharepoint.Tables() function that I discovered last week.  This has produced a table containing an identifier Id, the Title, and a table of data.

The Navigation step then expands the table for ‘Power Query List’.

This gives me two [2] columns with the same name, Id and ID.

The ‘Renamed Columns’ step renames ID to ID.1 and is generated to avoid any confusion.

I use ‘Choose Columns’ from the Home tab to decide which columns I need:

I can see I need to keep the Title column, as that is the salesperson responsible for the expenses.  I also need AttachmentFiles:

I click OK, and the other columns are removed:

Next time, I will continue transforming this data.

Come back next time for more ways to use Power Query!