Power Query: It’s Good to Share(Point) Part 7
6 November 2024
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1730715162.png/fdd8aff572a7bf5c338aaef8b8373935.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1730715214.png/dd57be3afd0fcf1f4feceafa15305b4a.jpg)
In this series on SharePoint data, it’s no surprise to see that we need to enter the URL of the SharePoint site:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1730715237.png/c194247488253d2359a320042f7d8f68.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1730715258.png/6d171677ac970d87078b3e7c3788654e.jpg)
I could select multiple lists if I wish, but I only need ‘Power Query List’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1730715275.png/ddf88725a7c4cc1a98979098e76a65b0.jpg)
There are numerous columns I don’t need. I choose to ‘Transform Data’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1730715297.png/e932093714a47c084652f1297823aa86.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1730715325.png/58e8ccd3454054e4d1adb1145af7ceb5.jpg)
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’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1730715343.png/506e20f139825b4b29ff37285f55bd63.jpg)
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.
= Table.RenameColumns(#"bb59a983-0cde-417a-91a0-d806d1a8d020",{{"ID", "ID.1"}})
I use ‘Choose Columns’ from the Home tab to decide which columns I need:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1730715378.png/09d8ce3df57cebcc29f02dc018c75203.jpg)
I can see I need to keep the Title column, as that is the salesperson responsible for the expenses. I also need AttachmentFiles:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1730715402.png/d222cba658e52ae25560230527bdb124.jpg)
I click OK, and the other columns are removed:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1730715429.png/676851ec68607810ae1face78448c1dd.jpg)
Next time, I will continue transforming this data.
Come back next time for more ways to use Power Query!