Power Query DataSource.Error: SharePoint: Request failed Help

[JJoorisity]

I am trying to access a group of Excel files (some of them quite large) from a company SharePoint, by using a blank Query to acces the SharePoint subfolder content. When I get to the folder with the required Excel files, I can see the list of files and they all show as a Binary content but I get the following error for each:



It is a company SharePoint and goes through a few authorizations to access, using my login information. I have tried to edit the credentials and the message I get is that I do not have access to the file, if I try the other options. So I'm wondering if there's something on the SharePoint side that won't allow me to see the file content?

The SharePoint setup is less than ideal, there are a number of folders I have to go through to get to the files, as it is a common storage area for a group of people. As such, I haven't been able to access the files and their content using the normal SharePoint List/Folder source in Power Query. I can add all the files individually but I get a time out error after awhile. So I was hoping to just access the folder.

Any help would be appreciated, I'm fairly new to Power Query.

Thanks,
J

 

[Ken Puls]

Are you providing a path that goes straight to the folder you want to use?

In order to connect to Sharepoint, you must connect to the root of the Sharepoint site, then add a step to filter out the stuff you don't want, and consolidate what's left. (It's kind of a pain.)

I'm not 100% sure that's the issue here, but it's the first thing I would be looking at.

 

[JJoorisity]

I am only using the root of the SharePoint site and then using the Power Query Navigation to move to the folder with the files I need. But I can't seem to actually get any data from these files...

 

[Ken Puls]

Out of curiosity, if you filter to a single file (just for testing), then try to do the combine, does it give the same error? (Trying to figure out if this is a size thing, or a permissions issue...)

Can you post your M code (just replace the full https:// link with http://mysharepointurl so that we can see the code you're trying?

 

[JJoorisity]

I tested out the filter, I was given the same error message when I went over the file's content after I had filtered to just one.

I've attached my Formula for the blank Query that I used to access the SharePoint's content and then the code for subsequent steps. I basically just navigated to the folder shown, I'm not sure if this is part of the problem.

Thanks again,

J

 

[Ken Puls]

Ahhh... I think you're using the wrong connector. (I can't even find a UI entry point to that one in my build any more.) Here's what my M code looks like when I use the connector under From File --> From Sharepoint Folder:

= SharePoint.Files("https://<domain>/sites/<sitename>", [ApiVersion = 15])

From there I get a table of all the files in the entire site. My next step is to filter the "Folder Path" colum to get to the specific folder I need. Then I run the combine.

If you don't see the Sharepoint Folder connector under From File, don't worry. Just take your existing query and update the formula to use what I have above. (You may also need to strip the , ApiVersion bit, but try it with that first)

Let me know if that gets you closer.

 

[JJoorisity]

I tried both methods and get the same error when I try entering my credentials: "Access to the resource is forbidden". I know my login works because the sign in doesn't fail, the process fails after I select the connect button. So it's obviously something on my company's side that's preventing this.

Any chance you would know if this is a setting on SharePoint that I could have our IT adjust or is it just linked to my company's network security?

Thanks again for all the help.