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

Welcome to our Power Query blog.  This week, I look at another way to extract data from a folder on SharePoint.

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 will be starting with a single file and then move on to consider combining data in a SharePoint folder. 

The data for this series will come from the following SharePoint folder:

Regular readers will be familiar with the expense data in the form of CSV files, I’m sure!

In Part 3, I completed the task of extracting a single CSV file from SharePoint into Power Query.

In Part 4, I used the ‘From SharePoint Folder’ option to combine all the CSVs in the SharePoint folder.

Last time, I completed extracting the data from the folder, ready for it to be transformed in the usual way:

However, depending upon the arrangement of the data and the time taken to extract it, there are other options that may way speed up the process.  Let’s look at the M code generated by the initial Source step to extract the files from the SharePoint folder:

If I enter the function SharePoint.Files(), I can see the help for it in the IntelliSense:

This function receives the URL pointing at the top level of the site, and from my M code, I can see the options are specifying the ‘ApiVersion’.  This is the version of the Application Programming Interface for SharePoint.  This interface is being used by Power Query to access the information via a web address.  This should be set to version 15, the latest version, so this is correct. 

Since this process is getting all the files from the top level of my SharePoint site, it’s quite slow.  I may also use the IntelliSense to see the other M functions that access SharePoint:

I will look at these in turn:

SharePoint.Contents()

As I see from the IntelliSense, this is similar to SharePoint.Files():

The second (API) parameter may be entered for this function too, but in this case, it is optional.  Let’s look at what I would see if I used this as my Source step:

I may click in the space next to a ‘Table’ in the Content column to see the data in that table:

I may click on the ‘Table’ for the value ‘General’, which then navigates to that folder.  I have also filtered on ‘Kathryn’ to avoid showing sensitive data:

I click again on the word ‘Table’ to access the folders under my folder, and filter to see the expenses folder:

Clicking on the resulting ‘Table’ gives me the folder I need and I may follow the usual method to combine the data.

Is this method of accessing the folder quicker?  It is of course more steps, and it will depend upon your setup, but with my data, when I loaded the same result to a worksheet, I found it to be considerably quicker (a few seconds for SharePoint.Contents(), versus a couple of minutes using SharePoint.Files()).

SharePoint.Tables()

This function has a slightly different description in the IntelliSense:

Since this is referring to a SharePoint List, we will look at this in more detail next time.

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