No “From Sharepoint Folder” option in Power Query? No problem!

How do you connect to a Sharepoint folder in Power Query if you don’t have “from Sharepoint folder” as an option?

There are several excellent guides online for how to connect Power Query to a Sharepoint folder. For example, see Wyn Hopkins, Mark Proctor (Excel off the Grid) and Mynda Treacy (My Online Training Hub).

However, all of the videos or blogs I could find assumed that you have a “From Sharepoint folder” as an option.

I didn’t have this option on any the licences I have access to.

This was where I found out that you need Apps for Enterprise to get this option to appear. This is unlikely to be an option for all but the biggest businesses, which is a pity.

A workaround for solo users

I have previously used a workaround for this.

A few years ago I wrote a post about combining multiple forecasts with Power Query. In that post I explain my method for accessing Sharepoint folders, which was to create a local synchronised folder and point the query at that.

This works fine (at the moment) but is only really suitable if only one person is going to be editing or updating the query. If more than one of you are doing it, you’ll need to keep changing the file path, which isn’t ideal.

So how can we connect directly to Sharepoint?

I had the breakthrough moment when I was watching Wyn’s video and saw the code line that his magic “Connect to Sharepoint Folder” button generated. Once I had got over my sulk and frustration that this button wasn’t available to me, I wondered: What happens if I type that code into a blank query, making adjustments for my Sharepoint Library path. And it just… worked!

So here’s what you need to do.

1. First, identify your Sharepoint Library filepath. This is not the filepath to the folder that you want but rather to the entire Sharepoint library. In my example in the picture I’ve cut off the file path to protect company information but it would look something like https://mycompany.sharepoint.com/sites/finance/Shared%20Documents/hdfhehu6364768364

You want the bit that corresponds to the library, which is everything in bold, before /Shared%20Documents. (If you’re not sure, click around in your library a bit. It’s the bit that doesn’t change when you go between the Documents and the Site Page)

2. Open a Blank Query in Excel (Data = > Get Data From => Other Sources => Blank Query). This will take you into the Power Query window with no data showing.

3. Enter this code, adjusted to match your sharepoint Library. In my example the code is:

= SharePoint.Files(“https://mycompany.sharepoint.com/sites/finance/”, [ApiVersion = 15])

4. If this is your first time connecting to the site, you will be prompted to set credentials. You’ll need to select “Organisational account” and enter whatever your password / two factor authentication set up requires.

5. If it works, you will then need to filter down to the relevant folders. Or follow Wyn’s suggested trick for isolating one folder.

Incidentally, it’s well worth watching / reading the other resources that I linked to before trying this because connecting Power Query to Sharepoint is a bit of a pain even if you do have the magic “From Sharepoint Folder” option in your version of Excel.

I learned something from all three of these videos. And when I looked again, I noticed that Mynda does link to a comment on her site that gives this code. However, I didn’t notice it first time, hence why I’m writing this blog in case it helps others.

Sharepoint fun

I should point out that although the code was dead simple, making sure I had all the relevant Sharepoint access was not.

As well as being a user with a licence on that tenancy, I also had to have full access the relevant sharepoint library. It’s very possible to have access to the files and folders without having this step. So if there are issues with credentials not being accepted, have a look at that.

I hope this post is useful to someone.