Results 1 to 7 of 7

Thread: Power Query DataSource.Error: SharePoint: Request failed Help

  1. #1
    Neophyte JJoorisity's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016 Office 365 64bit

    Power Query DataSource.Error: SharePoint: Request failed Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:

    Click image for larger version. 

Name:	PQ Problem.PNG 
Views:	69 
Size:	18.7 KB 
ID:	8456

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	PQ Problem.PNG 
Views:	5 
Size:	23.7 KB 
ID:	8455  

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,261
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte JJoorisity's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016 Office 365 64bit
    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...

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,261
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Neophyte JJoorisity's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016 Office 365 64bit
    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.PNG 
Views:	3 
Size:	1.7 KB 
ID:	8457   Click image for larger version. 

Name:	Formula.PNG 
Views:	3 
Size:	2.5 KB 
ID:	8458   Click image for larger version. 

Name:	Navigation.PNG 
Views:	3 
Size:	7.7 KB 
ID:	8459  

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,261
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:

    Code:
    = 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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Neophyte JJoorisity's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016 Office 365 64bit
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •